Sql-server – Clustered index scan in query with non clustered index filter

performancequery-performancesql server

I have an Operations table with a nonclustered index on my foreign key to a Organizations table; however, when trying to run the following query, SQL Server does a clustered index scan instead of using my non clustered index:

SELECT OpType, UserId, OpTime 
FROM Operations 
WHERE OrganizationId = 'some uniqueindetifier'

I tried doing a join or using in instead and the result was the same. If I change the query to SELECT OrganizationId FROM... then I actually get my intended nonclustered index seek, but naturally returning only this column would be pointless and adding any other column goes back to a clustered scan.

There's dozens of millions of rows in the table but there should be around only 50 000 rows with the OrganizationId I'm looking for, so I'm sure that a query plan that uses the non clustered index would be faster.

I executed the same query for a different OrganizationId (that returned about 30 000 rows) and got the result in less than 2 minutes, while doing this query for the OrganizationId that does a clustered scan didn't finish in 8 minutes even though it should return around 50 000.

I don't have any columns included in the Organization index but I don't want to double the size of this fairly large table by adding all columns to the index as there's a lot of rows getting inserted and I only want to extract all columns from it once a month for a report.

So aside from including columns to the index, what else can I do to make SQL Server use my non clustered index?

Best Answer

One way to coax the optimizer to use narrow nonclustered indexes is to use a CTE:

WITH fix_me AS (
SELECT OrganizationId 
FROM Operations 
WHERE OrganizationId = 'some uniqueindetifier'
)
SELECT *
FROM Operations AS o
JOIN fix_me AS f
ON f.OrganizationId = o.OrganizationId

I'm guessing that your nonclustered index isn't covering, meaning the optimizer has a choice between doing a Key Lookup, or scanning the Clustered Index.

Using a CTE in this way can help guide the optimizer to use a narrow index to get only the necessary rows, but may also be ignored.

Since you gave an incomplete picture of your query and table design, feel free to adjust that to fit your needs.

I talk about this technique more in this presentation:

Improving SELECT * Query Performance

Related Solutions

Sql-server – Where to place non clustered index

In there interest of showing a generalized example until we see your particular DDL and query, take the below as a basic example:

create table SomeTable
(
    id int identity(1, 1) not null
        primary key clustered,
    AnotherInt int null,
    SomeData nvarchar(1024) null
)
go

insert into SomeTable(AnotherInt, SomeData)
values(null, null)
go 1000

update SomeTable
set 
    AnotherInt = id * 3 + 152,
    SomeData = 'My ID value is ' + cast(id as nvarchar(16))

So we have a test table, SomeTable, with a clustered index. Execute the below query:

select SomeData
from SomeTable
where AnotherInt < 200

This causes a Clustered Index Scan, like you are seeing:

enter image description here

So analyzing the query, we have the column AnotherInt that is in the WHERE clause and being searched on. We are also retrieving the SomeData column, so to prevent a key lookup in the clustered index (or the optimizer may even just use a Clustered Index Scan again), we'll have SomeData as an INCLUDE column:

create nonclustered index IX_SomeData_AnotherInt_SomeData
on SomeTable(AnotherInt) 
include (SomeData)
go

Now, executing the same query above:

select SomeData
from SomeTable
where AnotherInt < 200

SQL Server will utilize that non-clustered index to return the data. It is a covering index, because it won't need to do a lookup on the clustered index for the remaining data:

enter image description here

Through creating a prudent NCI, we have now eliminated the CI Scan in lieu of an Index Seek.

Sql-server – Is a clustered index locked while a (clustered index) scan is in progress

There are many many many more factors at play.

isolation level. locking behavior differs wildly between isolation levels. Some don't lock at all (read uncommitted, snapshot, rcsi). The default read committed transiently locks rows it reads as long as is necessary. Repeatable read and Serializable hold on to locks and end up locking everything, and many developers deploy serializable without ever realizing they do so.
scan purpose. Scans for read are compatible with each other so they don't block, no matter what they lock. If you have scans blocking other scans they must be scans for update, which are incompatible with each other. Again, snapshot isolation level (including rcsi) do not block even when doing a scan for update.
lock granularity, based on cardinality estimates. Scans may choose row, page or rowset level granularity. A table scan will likely choose page locks.
index page locks/row locks configuration, which you so inadvertently changed. You should revert the change, since is not based on any measurement and root cause analysis. Guts feeling has no role in investigation.
lock escalation
row stability requirements when off-row LOB data is present
other

I suggets you follow the procedure described in Capturing wait stats for a single operation to capture the wait stats of the scans you observed as blocking. Se if indeed they block on locks held by the table scan operation. IF the scenario is truly as you described (read scan vs. other read operations) then there is no reason for blocking so something else will be at play. You can also give sp_whoisactive a shot.

Best Answer

Related Solutions

Sql-server – Where to place non clustered index

Sql-server – Is a clustered index locked while a (clustered index) scan is in progress

Related Question