How can I capture or list all queries that use hints and have executed since the last instance restart?
I am using SQL Server 2005 Standard Edition.
migrated from stackoverflow.com Jun 19 '12 at 23:19
This question came from our site for professional and enthusiast programmers.
At my prodding @buckley already provided a query from which you can derive information. I'll add that you can capture snapshots of this DMV periodically so that you capture things even if they have been pushed out of the cache since their last use. I won't get into how to maintain an archive of DMV data here; maybe a good blog post in the making.
Once you have the data (or just looking at the current plans that are cached), some common hints you can derive from the XML returned by
I am NOT an XML guy so I have no clue how to tell you to extract that information dynamically. (More on this below.)
I can't see any way to determine whether isolation has been set (at the batch level or using things like
I also can't see any difference in the plan for query hints like
Since you can get a non-XML version of the plan from an alternate DMF,
As identified earlier, this could produce false positives for queries like this:
But outside of parsing the query text for
That's not possible in retrospect.
You need to actively log the queries with SQL Profiler where you can export and filter on the queries with hints. Or use server-side trace for long running traces.
You can come close by inspecting the cached execution plans.
This will show most queries but remember that some aren't cached and that sql server can remove them from the cache if it's under pressure. Use this query to list them:
You can use a where clause with a regex (clr function) so that false positives are minimized close to 0.