I have a table with 50+ columns and 1M rows The PK is a clustered Index (integer) Two other Columns are Indexed (colA,ColB) Query1 = SELECT * FROM Table Where ColA = 0 AND ColB = 29 Query2 = SELECT ColA, ColB FROM Table Where ColA = 0 AND ColB = 29 Query3 = SELECT ColA, ColB,ColC FROM Table Where ColA = 0 AND ColB = 29 Query1 uses the clustered index (kind of expected because of the select *) Query2 uses the index on ColA, ColB Query3 uses the clustered index Query 2 performs significantly faster + fewer logical reads 1 & 3 are 5-10x slower running 1 or 3 with WITH (INDEX()) results in good performance. Also Just for fun I removed the PK Clustered index and the plan chooses to use a Table Scan rather than the ColA,ColB indexes. why does the optimizer prefer to use the clustered index (even though it clearly performs poorly)? Is it something in the table/index design that I need to look at? Thanks MS
The cluster index on witch column was created? Luis Martin Moderator SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason. Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte All postings are provided “AS IS†with no warranties for accuracy.
What I would like to know is: On colA, colB, colC or other column? Luis Martin Moderator SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason. Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte All postings are provided “AS IS†with no warranties for accuracy.
Execution plan sometimes use scan table some times use cluster when no one index sastify the plan. In the 3rd query I suppose the reason is not to many rows in the table. How about if you take a look of ITW for each case? Luis Martin Moderator SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason. Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte All postings are provided “AS IS†with no warranties for accuracy.
ITW makes no new recommendations. I'm really just trying to determine what logic the plan is using to determine why the clustered index may be faster? Is it possibly due to a large row size (with so many columns). I would have thought it looked at the where clause to determine what indexes to use. (where clause results in selecting of less than 1% of the total data) It seems to be looking at the select and if an index doesn't cover all columns it reverts to the clustered index or table scan. If there were more rows (there are nearly 1 Million right now) would it be likely to use the indexes on ColA and ColB or would it still choose the same plan? Any thoughts or can you direct me to any articles that may explain what is going on here? Thanks MS
the optimizer uses a set formula for deciding between 1) a table scan, and 2) an index seek with a bookmark lookup to get values not in the index. hence Q1 and Q3 require the bookmark lookup the SQL optimizer assumes that 1 table scan page costs 0.00741sec, and a bookmark lookup for 1 row costs either 0.006325 or 0.00312 sec, depending on whether your table in memory or on disk, this formula is seriously in either direction, for modern server system, it might have been right at one point in time, but actually i have a suspicion that MS let an intern do the CBO calibration, instead of having a more experienced person do it. all of this are in my articles and powerpoint some where on this sight
I suppose I'm stuck with the With(Index()), doesn't give you much faith in the plan optimizer <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />I'll do some reading.<br /><br />Thanks
HI ya, How selective is COlA in your example? It may be that SQL decides that since colA is not that selective it would need to go through x% of the index and therefore might as well scan rather than seek if there are fewer unique values for colA in the table than colB then switch the order of the columns in the index to see if that makes a difference Cheers Twan
ColA and ColB are indexed seperately (having a compound index did not seem to change this plan) ColA is 1 of 30 possible values the value in my current select would return 10-20% of the table all others are even distributed ColB could be 1 or 10 values and the value 0 is about 25% of the table. Thoughts?
