Index Precedence
- I'm looking to tune up some queries that are taking forever to run for a report and the thing that I'm noticing is that in my execution plan the incorrect index for a table is being used. I'm joining on the PK to another table but am getting an 'Index Scan' versus 'Seek'. The PK is non-clustered (don't ask why) so by default the SQL engine is picking the clustered index. Here's my query along w/ the execution plan an indexes...
Am I correct in assuming SQL chooses the clustered index by default or would it be some sort of 'precedence order' that can be specified somewhere?
SELECT
'x'
FROM
MyDB1..table1 a
INNER JOIN MyDB2..table2 b ON
a.SubsidiaryNo = b.SubsidiaryNo
Table1
Index_Name Description Keys IX_Table1_AccountNo_RepId_AcctClass_PrimaryAlphaKey1 nonclustered located on PRIMARY AccountNo, RepId, AccountClass, PrimaryAlphaKey1 IX_Table1_AccountNo_StateCode nonclustered located on PRIMARY AccountNo, StateCode IX_Table1_AsOfDate nonclustered located on PRIMARY AsOfDate IX_Table1_MMFSecurityNo nonclustered located on PRIMARY MMFSecurityNo IX_Table1_OldAccountNo nonclustered located on PRIMARY OldAccountNo IX_Table1_RepId_AcctClass clustered located on PRIMARY RepId, AccountClass IX_Table1_RepIdBeforeACATs nonclustered located on PRIMARY RepIdBeforeACATS IX_Table1_SSNTIN nonclustered located on PRIMARY SSNTIN PK_Table1_OFFLINE nonclustered, unique, primary key located on PRIMARY AccountNo Table 2
Index_Name Description Keys PK__SubsidiaryCorrBD__56757D0D clustered, unique, primary key located on PRIMARY SubsidiaryNo
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
So I guess the real question is...how does SQL Server choose which index to use?
Anyone...?
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
SELECT
'x'
FROM
MyDB1..table1 a
INNER JOIN MyDB2..table2 b ON
a.SubsidiaryNo = b.SubsidiaryNo
is your query, but I don't see a.SubsidiaryNo in any of the indexes for table 1. This may be why it is doing a scan of the clustered index (it also happens to be where the data resides and it has to look at the data to compare a.SubsidiaryNo to b.SubsidiaryNo. I'd build an index on a.SubsidiaryNo.
-
Theoretically, under the assumption that the statistics are recently updated (and indexes are not fragmented), SQL server chooses one index over another because choosing that one would result in a better execution plan and thus better performance.
If, however, for whatever reason, you do not agree with the choice, you can force(hint) SQL Server to use a specific index of your choice.
Check this out: http://www.sqlservercentral.com/columnists/rmarda/performanceaddinghints_printversion.asp
-
Ah okay so it's based on table statistics and index fragmentation...yeah I'm including table hints in my queries here because for some reason the clustered index on my table1 is on a field that is not referenced often (to the best of my knowledge) I think I will work w/ our DBA's to find out whats up w/ that and see if it can be changed...
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Also, presuming 'x', is the only field you're returning, add that field to an index on SubsidairyID, non-clustered or clustered. SQL will prefer that index as it will be able to find and return the data within the same index. It doesn't need to do any bookmark lookups to return additional information.
--------------------
Colt 45 - the original point and click interface
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply