Can You Dig It? – Researching Index Scans

It’s been a while since the last plan cache series post.   In the last few posts, I was focusing on specific attributes.  Instead of continuing on that vein, I want to cover a use case that fit to a specific performance need.

Access Methods:Range Full/sec

Suppose for a moment you were monitoring your SQL Server environment and you noticed that Access Methods:Range Full/sec. had jumped up some.  Maybe it jumped up, like it did below, and instead of averaging 0 per second the rate is around 550 per second.  This might sound some alarms and your manager might hit the klaxon and sirens.

The trouble here is that, while the performance counter is high, it isn’t telling you anything that you can do anything about.

It’s just too vague to know if there is an issue…

DMV sys.dm_db_index_usage_stats

You are able to get more information on this issue in DMVs.  More specifically, you can look at the user_scans column of sys.dm_db_index_usage_stats.  So while the sirens are wailing, maybe you execute the query below:

SELECT TOP 10
OBJECT_NAME(ius.object_id) AS TableName
,i.name AS IndexName
,ius.user_scans
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.index_id = i.index_id AND ius.object_id = i.object_id
ORDER BY ius.user_scans DESC

This query will let you know that there were a heck of a lot of scans on the index PK_Product_ProductID on Production.Product.  You can see this in the output in the image to the right.

But can you bring this information to your boss and do anything with it?  Is the information actionable?  Are the Full Scans reduced or mitigated?

Plan Cache Approach

The answer to all of the questions above are No.  There still isn’t enough information that you can reasonably consider the situation resolved.  You only know that there is a possible problem and what it might be related to.  There is not enough information to show exactly what the problem is.

Many issues don’t just lay around on the surface saying, “Fix me! Fix me!”  The solution to this is to take the information you already have and look deeper.  If we search the plan cache for both Clustered Index Scans and Index Scans and then the index PK_Product_ProductID, you’ll get the execution plans that likely match to the performance issue.

The query to investigate this is such:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

DECLARE @op sysname = 'Index Scan';
DECLARE @IndexName sysname = 'PK_Product_ProductID';

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
cp.plan_handle
,operators.value('(IndexScan/Object/@Schema)[1]','sysname') AS SchemaName
,operators.value('(IndexScan/Object/@Table)[1]','sysname') AS TableName
,operators.value('(IndexScan/Object/@Index)[1]','sysname') AS IndexName
,operators.value('@PhysicalOp','nvarchar(50)') AS PhysicalOperator
,cp.usecounts
,qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY query_plan.nodes('//RelOp') rel(operators)
WHERE operators.value('@PhysicalOp','nvarchar(50)') IN ('Clustered Index Scan','Index Scan')
AND operators.value('(IndexScan/Object/@Index)[1]','sysname') = QUOTENAME(@IndexName,'[');

In the results below, there is an obvious execution plan that is likely the culprit of the large volume of full scans.  Hint: It’s the one with 54K uses.  Now instead of browsing around and trying to figure out where to start, you can move from alert to action in just a few minutes.  Using DMVs and the Plan Cache to resolve the issue.

What’s Next?

Now the next steps is going to be one that relies entirely on the plan that you find and the environment that the query is running in.  When it comes to my customers, some of the common things I look for are:

• Is the query filtering early enough?
• Is it returning too many rows?
• Is the right filtering in place? Server vs. Client side filtering.
• Is the query appropriate for the environment? OLTP vs. Reporting.

While these are vague questions, they are really the tip of the iceberg.  But I am sure they will start you on the path to reducing full scans in your environment and breathing easier every day.  Please leave a comment below if you have any successes with this technique.  It’ll help others when they try this for themselves.