Clustered Index created but SQL complains

Question

Clustered Index created but SQL complains

thenewbee

SSC Eights!

Points: 861
More actions
April 5, 2018 at 5:02 am

#328136

Hi Guys,
I'm very new in query optimizing.
I have created a non clustered Index on tbl_A as below also note that all tables have primary key created.
CREATE NONCLUSTERED INDEX idx_tbl_A ON tbl_A ([col1],[Col2] )

My Query:
Select * from tbl_c
Inner Join....
Inner Join...tbl_A
on tbl_A.Col2 =tbl_C.Col2
where tbl_c.id=123
Running the above query , SQL gives me a suggestion to add a missing index. I didn't understand why SQL is complaining about the missing index ; as I have already included that column in the NONCLUSTERED index
If I create a NONCLUSTERED index only on that column (col2), the query cost comes down to 4%.
Ideally its
CREATE NONCLUSTERED INDEX idx_tbl_A ON tbl_A ([col1],[Col2] ) vs CREATE NONCLUSTERED INDEX idx_tbl_A ON tbl_A ([Col2] )
can someone explain what makes the difference here
Thanks.
bee

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

thenewbee - Thursday, April 5, 2018 5:02 AM
Hi Guys,
I'm very new in query optimizing.
I have created a non clustered Index on tbl_A as below also note that all tables have primary key created.
CREATE NONCLUSTERED INDEX idx_tbl_A ON tbl_A ([col1],[Col2] )

My Query:
Select * from tbl_c
Inner Join....
Inner Join...tbl_A
on tbl_A.Col2 =tbl_C.Col2
where tbl_c.id=123
Running the above query , SQL gives me a suggestion to add a missing index. I didn't understand why SQL is complaining about the missing index ; as I have already included that column in the NONCLUSTERED index
If I create a NONCLUSTERED index only on that column (col2), the query cost comes down to 4%.
Ideally its
CREATE NONCLUSTERED INDEX idx_tbl_A ON tbl_A ([col1],[Col2] ) vs CREATE NONCLUSTERED INDEX idx_tbl_A ON tbl_A ([Col2] )
can someone explain what makes the difference here
Thanks.
bee

The order of the columns within the key section is critical. Your join is on Col2, hence col2 first in the key column list is likely to be beneficial.

http://www.sqlservercentral.com/stairway/72399/

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

thenewbee SSC Eights! Points: 861 More actions · Answer 2

Thanks Chris,
, but some other query utilizes the other column. If thats the case we have to create multiple index for each query right

Regards
bee

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

thenewbee - Thursday, April 5, 2018 5:19 AM
Thanks Chris,
, but some other query utilizes the other column. If thats the case we have to create multiple index for each query right
Regards
bee

"we have to create multiple index for each query" Not really. You may well end up with multiple indexes, each index matched to a family of queries which access the table the same way. You may end up with a query accessing a table via two ordinary indexes. If you were to observe multiple queries each accessing the same table via two or more indexes, then you'd probably be better off creating an index to match those queries.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

thenewbee - Thursday, April 5, 2018 5:02 AM
If I create a NONCLUSTERED index only on that column (col2), the query cost comes down to 4%.

Really important concept here, query cost is not query cost. It's ESTIMATED cost. You can't really compare cost to cost in two different execution plans. There is some degree of correlation to reality, but it is fairly loose. Instead, always measure the reads/writes and duration of the query to ensure you're seeing improvement or degradation. Using the cost estimate values for tuning can lead you into dangerous territory.

Since you're on 2014, the best way to measure query metrics is through Extended Events. They have the least observer impact on your queries.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning