Timeline for NonClustered Index not being Used
Current License: CC BY-SA 3.0
17 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 7, 2018 at 17:30 | vote | accept | Kevin | ||
| Mar 7, 2018 at 16:00 | comment | added | Hannah Vernon♦ | @Kevin there are no particular rules around specific numbers of rows needed to cause a particular query to use a particular index. The query optimizer makes these decisions on a case-by-case basis by determining the cost of a large number of possible plans then using the lowest cost plan. | |
| Mar 6, 2018 at 20:13 | answer | added | McNets | timeline score: 9 | |
| Mar 6, 2018 at 15:03 | comment | added | Kevin | What is considered a large enough number of rows for an Index to be used? | |
| Mar 6, 2018 at 14:59 | comment | added | Jonathan Fite | As an aside, I would recommend using AdventureWorks for your sample database. It will have large amounts of data. You can then remove indexes from it to show your tuning samples. Unless you are going to use your actual production data. | |
| Mar 6, 2018 at 14:56 | answer | added | sepupic | timeline score: 5 | |
| Mar 6, 2018 at 14:47 | comment | added | Kevin | Ah OK. Version 2008R2 | |
| Mar 6, 2018 at 14:46 | history | edited | Kevin |
Updated Tag to include version
|
|
| Mar 6, 2018 at 14:17 | comment | added | Jonathan Fite | Your version matters as well, I put together a quick test and at least in 2016, it uses the nonclustered index. | |
| Mar 6, 2018 at 14:05 | comment | added | Kevin | OK. Yeah I was looking to build an example out to demonstrate how Indexes are used | |
| Mar 6, 2018 at 14:03 | comment | added | Paul | Try 'UPDATE STATISTICS TextIndexSample with FULLSCAN' and see if that changes it. As it stands SQL is determining that a small table scan is cheaper than an index scan plus lookup. | |
| Mar 6, 2018 at 14:03 | comment | added | DimUser | For such a small table, does it matter at the moment? Essentially, if you need ModifiedDate (SELECT *) your NCI is not a covering index, so it is easier/quicker for SQL Server just to scan the whole table. Whereas, if you exclude ModifiedDate, then the NCI is covering, as SQL Server keeps a pointer to the clustering key (Code) at the leaf level, hence the index seek. | |
| Mar 6, 2018 at 14:01 | comment | added | Jonathan Fite | It's probably because the table is so small. You could just INCLUDE the ModifiedDate in the non-clustered index. | |
| Mar 6, 2018 at 13:57 | comment | added | Kevin | If I leave out "ModifiedDate" from the SELECT query it uses the NonClustered Index seek... SELECT Code, Name FROM dbo.TestIndexSample where Name = 'User A' | |
| Mar 6, 2018 at 13:56 | comment | added | Kevin | Yes, values in Code and Name are unique | |
| Mar 6, 2018 at 13:54 | comment | added | Jonathan Fite | How unique is the value "User A"? But your table is so short and narrow, the engine may have decided that the index is more work than it's worth. Have you tried clearing plan cache and buffers between queries to see if the engine changes it's mind based on if the table is in memory or not? | |
| Mar 6, 2018 at 13:49 | history | asked | Kevin | CC BY-SA 3.0 |