SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Wrong Index Being Used


Wrong Index Being Used

Author
Message
Roger Sabin
Roger Sabin
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 995
I was wondering if anyone has seen this before and has any idea why it is happening. This is occuring in our production. (All column, table and index names have been changed to protect the guilty.) Problem involves a single select statement on a single table.


First the background information:

We are using SQL Server 2005.
Table has 40,257,935 rows.
Table has 390 columns.
Table has several indexes of which only 2 are pertinent to this discussion.


The table stores a monthy snapshot of account information. The two indexes are as follows:

Name: IDX_MonthlyAccount_YearNum_MonthNum_AltKey_AccountNumber
Indexed Columns: YearNum, MonthNum, AltKey and AccountNumber

Name: IDX_MonthlyAccount_YearNum_MonthNum_AccountNumber
Indexed Columns: YearNum, MonthNum, AccountNumber


If we use the following select statement, the second index (IDX_MonthlyAccount_YearNum_MonthNum_AccountNumber) is correctly used:

select YearNum, MonthNum, AccountNumber from MonthlyAccount where YearNum = 2013 and MonthNum = 1 and AccountNumber = 'ABCDEF'


But, if we use the following select statement, the first index (IDX_MonthlyAccount_YearNum_MonthNum_AltKey_AccountNumber) is incorrectly used:

select YearNum, MonthNum, AccountNumber, Name from MonthlyAccount where YearNum = 2013 and MonthNum = 1 and AccountNumber = 'ABCDEF'


The only change between the two selects is the addition of a column (Name) in the select list that is not part of the second index.

Why is the wrong index being used in the second select?
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40316 Visits: 38567
Not enough information. If you could post the actual execution plan (saved as a .sqlplan file) for both queries we could probably give you a better answer.

As it is, we no little about the table or the actual definition of the indexes, how much data is being returned, etc.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Roger Sabin
Roger Sabin
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 995
Execution plans from our production for both types of select statements
Attachments
CorectIndex.sqlplan (17 views, 5.00 KB)
WrongIndex.sqlplan (43 views, 4.00 KB)
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40316 Visits: 38567
Your "wrong index" is the clustered index. Since SQL Server needed to go to the table for the additional information not included in the nonclustered index (the "correct index"), SQL Server decided it was cheaper to do an INDEX SEEK on the clustered index rather than an INDEX SEEK on the nonclustered index with a bookmark lookup to the data for the additional information.

Looks to me as if SQL Server used the "correct index" where you believe it used the "wrong index" for the second query.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14916 Visits: 9006
Roger Sabin (2/7/2013)
The two indexes are as follows:

Name: IDX_MonthlyAccount_YearNum_MonthNum_AltKey_AccountNumber
Indexed Columns: YearNum, MonthNum, AltKey and AccountNumber

Name: IDX_MonthlyAccount_YearNum_MonthNum_AccountNumber
Indexed Columns: YearNum, MonthNum, AccountNumber


If we use the following select statement, the second index (IDX_MonthlyAccount_YearNum_MonthNum_AccountNumber) is correctly used:

select YearNum, MonthNum, AccountNumber from MonthlyAccount where YearNum = 2013 and MonthNum = 1 and AccountNumber = 'ABCDEF'


But, if we use the following select statement, the first index (IDX_MonthlyAccount_YearNum_MonthNum_AltKey_AccountNumber) is incorrectly used:

select YearNum, MonthNum, AccountNumber, Name from MonthlyAccount where YearNum = 2013 and MonthNum = 1 and AccountNumber = 'ABCDEF'


Aside from what Lynn is saying, I recently learned that indexes are also chosen based on column order. I do not know why the first index (that has AltKey) is being chosen for the first query, but SQL Server is correctly choosing the second index on the second query because the order of your SELECT follows the order of columns defined in the second index. That's part of how it knows which indexes it can use.

Food for thought. EDIT: Lynn, feel free to correct me if I'm wrong. I'm still figuring out the innards of indexing.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4868 Visits: 72519
Lynn's spot on with this.

Because the Name field is missing from the non-clustered index it would require a seek on the non-clustered followd by a bookmark lookup to the clustered index for the fields missing from the index. That's more expensive than using the Clustered index once to perform the entire query.

If you want to use the non-clustered index, INCLUDE the name field in the index.

i.e.


DROP INDEX UN_MonthlyCMS.[IDX_MonthlyCMS_ProcessYear_ProcessMonth_VisionAccountNumber_CreditPlanNumber]

CREATE INDEX [IDX_MonthlyCMS_ProcessYear_ProcessMonth_VisionAccountNumber_CreditPlanNumber] ON UN_MonthlyCMS(ProcessYear, ProcessMonth, VisionAccountNumber) INCLUDE (CustomerName)





--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Roger Sabin
Roger Sabin
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 995
I understand what's been said.

But I still don't see why the first index is more "correct" when it requires trolling through thousands of records to find just the few for an account when the second index goes directly to the few records for the account.

Doesn't the equality comparison on the AccountNumber column make the second index a better choice than the first index?
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14916 Visits: 9006
It might help us a little to see the queries in question.

I'm looking at the pictures generated by the execution plans and I see a difference in the predicate searches that might point to why the clustered index is being chosen over the non-clustered index. The indexes themselves seem to have the same relative cost, but I'm still learning how to interpret the data on these, so I could be wrong.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Attachments
Correct Index Pic.png (9 views, 11.00 KB)
Wrong Clustered Index Pic.png (7 views, 10.00 KB)
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40316 Visits: 38567
Roger Sabin (2/8/2013)
I understand what's been said.

But I still don't see why the first index is more "correct" when it requires trolling through thousands of records to find just the few for an account when the second index goes directly to the few records for the account.

Doesn't the equality comparison on the AccountNumber column make the second index a better choice than the first index?


You are doing an equality comparision on AccountNumber on both indexes. SQL Server is doing a seek on both indexes. For your second query, if SQL Server used the nonclustered index, it would then have to do a bookmark lookup to the table (in this case the clustered index) to get the rest of the data to satisfy the query. Instead it chose to do an index seek on the clustered index as this satisfies the query.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14916 Visits: 9006
Lynn Pettis (2/8/2013)
Roger Sabin (2/8/2013)
I understand what's been said.

But I still don't see why the first index is more "correct" when it requires trolling through thousands of records to find just the few for an account when the second index goes directly to the few records for the account.

Doesn't the equality comparison on the AccountNumber column make the second index a better choice than the first index?


You are doing an equality comparision on AccountNumber on both indexes. SQL Server is doing a seek on both indexes. For your second query, if SQL Server used the nonclustered index, it would then have to do a bookmark lookup to the table (in this case the clustered index) to get the rest of the data to satisfy the query. Instead it chose to do an index seek on the clustered index as this satisfies the query.


Lynn, for my own edification... This is because Name isn't included in either index, right? So the Clustered Index is easier to use to find name as associated with the rest of the data that actually is in the index?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search