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?


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.

  • Thanks, what I really want to figure out is how many times the table is used in a hash join or hash match aggregate, so my reasoning was that if ordered is false then I can deduce this from that... – xhr489 Apr 1 at 19:20

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

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.