SQL Server NonClustered Index – Not Being Used

index-tuningnonclustered-indexsql serversql-server-2008-r2

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'

Best Answer

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';

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';

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';

dbfiddle here, plan here

Related Solutions

Sql-server – Index fragmentation increase as more row being deleted

This isn't fragmentation.

Fragmentation is generated of course, but deletes will simply create "islands" of remaining pages, which is less evil then GUID/clustered key INSERT fragmentation.

If you're PK is an IDENTITY, then CreationDate should roughly track this so you're actually deleting chunks of contiguous rows anyway.

Do you have an index on CreationDate
Do you have delete cascades?
Is the TOP 1000 in a single transaction?

For point 3, doing a loop inside a transaction is pointless: is this it?

At some point, a statistics update may be needed if you delete enough rows but I don't think it's that.

Other options:

why not use TRUNCATE TABLE, wrapped in a stored procedure with EXECUTE AS OWNER
use SYNONYMs for poor man's partitioning

SQL Server – Reasons for Clustered Index Scan

This table is very small!

It has 20 rows of which 2 match the search condition. The table definition contains three columns and two indexes (which both support uniqueness constraints).

CREATE TABLE Person.ContactType(
    ContactTypeID int IDENTITY(1,1) NOT NULL,
    Name dbo.Name NOT NULL,
    ModifiedDate datetime NOT NULL,
    CONSTRAINT PK_ContactType_ContactTypeID PRIMARY KEY CLUSTERED(ContactTypeID),
    CONSTRAINT AK_ContactType_Name UNIQUE NONCLUSTERED(Name)
)

Running

SELECT index_type_desc,
       index_depth,
       page_count,
       avg_page_space_used_in_percent,
       avg_record_size_in_bytes
FROM   sys.dm_db_index_physical_stats(db_id(), 
                                      object_id('Person.ContactType'), 
                                      NULL, 
                                      NULL, 
                                      'DETAILED')

Shows both indexes only consist of a single leaf page with no upper level pages.

+--------------------+-------------+------------+--------------------------------+--------------------------+
|  index_type_desc   | index_depth | page_count | avg_page_space_used_in_percent | avg_record_size_in_bytes |
+--------------------+-------------+------------+--------------------------------+--------------------------+
| CLUSTERED INDEX    |           1 |          1 | 15.9130219915987               | 62.5                     |
| NONCLUSTERED INDEX |           1 |          1 | 13.1949592290586               | 51.5                     |
+--------------------+-------------+------------+--------------------------------+--------------------------+

Rows on each index page aren't necessarily in index key order but each page has a slot array with the offset of each row on the page. This is maintained in index order.

The nonclustered index covers two out of the three columns (Name as a key column and ContactTypeID as a row locator back to the base table) but is missing ModifiedDate.

You can use index hints to force the NCI seek as below

SELECT ct.*
FROM   Person.ContactType AS ct WITH (INDEX = AK_ContactType_Name)
WHERE  ct.Name LIKE 'Own%';

But you can see that under SQL Server's cost model this plan is given a higher estimated cost than the competing CI scan (roughly double).

enter image description here

The single page clustered index scan would just need to read all the 20 rows on the page, evaluate the predicate against them and return them.

The single page nonclustered index range seek might potentially be able to perform a binary search on the slot array to reduce the number of rows evaluated however the index does not cover the query so it would also need a potential IO to retrieve the CI page and then it would still need to locate the row with the missing column values on there (for each row returned by the NCI seek).

On my machine running 1 million iterations of the non clustered index plan took 15.245 seconds compared to 11.113 seconds for the clustered index plan. Whilst this is far from double the plan without the hint was measurably faster.

Even if the table was orders of magnitude larger however you may well still not get your expected plan with lookups.

SQL Server's costing model prefers sequential scans to random IO lookups and the "tipping point" between it choosing a scan of a covering index or a seek and lookups of a non covering one is often surprisingly low as discussed in Kimberley Tripp's blog post here.

It is certainly not out of the question that it would choose such a plan for a 10% selective predicate but the clustered index would likely need to be quite a lot wider than the NCI for it to do so.

Best Answer

Related Solutions

Sql-server – Index fragmentation increase as more row being deleted

SQL Server – Reasons for Clustered Index Scan

Related Question