CODEWITHSUNDEEP
sql-serverperformanceindexing
Abed
Abed

Reputation: 21

SQL Server index on group of columns doesn't perform well on individual column seek

I have a non-clustered index on a group of columns as (a, b, c, d), and I already use this index using a common query we have which search for those four columns in the where clause.

On the other side, when I try to search for column (a) by simply using: 

select count(*) 
from table 
where a = value

here the performance is fine and execution plan shows it used my index.

But when I try to search for column (d) by simply using: 

select count(*) 
from table 
where d = value

here the performance is bad, and execution plan already used same index but it shows hint that index is missing and impact is 98% and it suggest creating new index for the column (d).

Just for testing, I tried to create new index on this column and the performance become very good.

I don't want to stuck in redundant indices as the table is very huge (30GB) and it has about 100 million rows.

Any idea why my main index didn't perform well with all columns?

Column a data type is INT

Column d data type is TINYINT

SQL Server version is 2014 Enterprise.

Thanks.

Abed

Upvotes: 0

Views: 120

Answers (1)

Andrei Amarfii
Andrei Amarfii

Reputation: 725

If you have complex index on 4 columns (A,B,C,D )then you could use queries which filter:

1) WHERE A=...
2) WHERE A=... AND B =...
3) WHERE A=... AND B =... AND C=....
3) WHERE A=... AND B =... AND C=.... AND D=...

You CAN'T skip lead portion of the index, if you will filter like this : WHERE B= ... AND C= ... AND D=... (thus, skipping A) performance will be BAD.

TRY creating separate indexes on each column, they are more flexible.

Upvotes: 2

Related Questions