How to see if index is being read “ordered” from DMVs

All we need is an easy explanation of the problem, so here it is.

I have a table that is being used in many reporting queries. I would like to know how many times the table is read in an "ordered" way. In the execution plan for clustered index scan one can see if the read is "ordered". Is this information available other places or is the only solution to search the plan cache?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

That data in the query plan only indicates whether the query plan requires on an ordered scan, or whether an allocation-order scan would work too. Even if the query plan does not require an ordered scan, you normally get one anyway, as allocation-order scans are only allowed under specific circumstances, as Paul White explains all here.

So since this data is about the query plan, not the scan method, it’s only available in the query plan.

Method 2

Assuming you have a query plan in a variable @plan you can use this:

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT IndexScan.value('concat(@Database,".",@Schema,".",@Table,".",@Index)', 'nvarchar(516)')
FROM @x.nodes('//IndexScan[@Ordered = "false"]/Object') AS n(IndexScan)

So to get this from every query plan, you could do something like this:

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT x2.IndexName, SUM(stat.execution_count) TotalExecutionCount
FROM sys.dm_exec_query_stats stat
CROSS APPLY sys.dm_exec_query_plan(stat.plan_handle) pln
CROSS APPLY pln.query_plan.nodes('//IndexScan[@Ordered = "true" or @Ordered = "1"]/Object') AS x(IndexScan)
CROSS APPLY (VALUES (IndexScan.value('concat(@Database,".",@Schema,".",@Table,".",@Index)', 'nvarchar(516)'))) AS x2(IndexName)
GROUP BY x2.IndexName

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply