3

I have a table (created below) that contains 321 rows.

I expect the last query below to use the NonClustered Index and then a Key Lookup. However, instead it uses a Clustered Index scan. Only a single row is returned as expected.

Why does it do the scan rather than use the NonClustered Index? Is it because the table contains only 321 rows?

CREATE TABLE dbo.TestIndexSample
(
    Code char(4) NOT NULL,
    Name nvarchar(200) NOT NULL,
    ModifiedDate datetime NOT NULL CONSTRAINT [DF_TestIndexSample_ModifiedDate] DEFAULT GETDATE(),
    CONSTRAINT [PK_TestIndexSample_Code] PRIMARY KEY CLUSTERED(Code)
);
GO
CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name  
    ON dbo.TestIndexSample(Name);   
GO

INSERT INTO dbo.TestIndexSample(Code, Name)
select CodeName, FullName 
from dbo.SourceTest 
GO

SELECT * FROM dbo.TestIndexSample

SELECT * FROM dbo.TestIndexSample where Code = 'X132EY'

SELECT * FROM dbo.TestIndexSample where Name = 'User A'
  • 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? – Jonathan Fite Mar 6 '18 at 13:54
  • 1
    It's probably because the table is so small. You could just INCLUDE the ModifiedDate in the non-clustered index. – Jonathan Fite Mar 6 '18 at 14:01
  • 1
    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. – DimUser Mar 6 '18 at 14:03
  • 1
    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. – Jonathan Fite Mar 6 '18 at 14:59
  • 1
    @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. – Max Vernon Mar 7 '18 at 16:00
7

You can force SQL Server to use the nonclustered index:

SELECT Code, Name, ModifiedDate 
FROM   dbo.TestIndexSample WITH(INDEX (IX_TestIndexSample_Name))
WHERE  Name = 'NAME10';

enter image description here

dbfiddle here, plan here

Without the hint the query optimizer considers the plan with the index more costly for this small amount of data, but you can get this plan by increasing the number of rows in your table. I managed to achieve it with 600 rows:

SELECT Code, Name, ModifiedDate 
FROM   dbo.TestIndexSample 
WHERE  Name = 'NAME10';

dbfiddle here

If you want to get just the Index Seek, your query should return only Name, so that data is pulled only from the index.

SELECT Name 
FROM   dbo.TestIndexSample  
WHERE  Name = 'NAME10';

enter image description here

dbfiddle here, plan here

But if other columns need to be returned too, you could use a covering index:

CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name  
    ON dbo.TestIndexSample(Name) INCLUDE (Code, ModifiedDate);

Obviously the index size will increase, but nothing important for this number of rows.

Now your current query will use an Index Seek:

SELECT Code, Name, ModifiedDate 
FROM   dbo.TestIndexSample 
WHERE  Name = 'NAME10';

enter image description here

dbfiddle here, plan here

4

Is it because the table contains only 321 rows?

Yes it should be the cause.

How many pages does your table have? It is very probable it has only one data page, so clustered index scan means that server can read only 2 pages to make this work while doing lookup means 2 pages of nonclustered index + 2 pages of clustered index.

You can see page count using this code:

select index_id, total_pages
from sys.allocation_units au join sys.partitions p
        on au.container_id = p.hobt_id
where p.object_id = object_id('dbo.TestIndexSample');

Here index_id = 1 corresponds to clustered and index_id = 2 to nonclustered index.

  • I get 6 total pages for Index_id 1; 5 total pages for Index_id 3. Don't see Index_id 2 – Kevin Mar 6 '18 at 15:01
  • index_id = 3 is your nonclustered index. id=3 just means that there was another nonclustered with id=2 but it was dropped – sepupic Mar 6 '18 at 15:09
  • You can set statistics io on and add a hint with(index(IX_TestIndexSample_Name)) to your selects and see how many pages are red – sepupic Mar 6 '18 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.