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.
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