Capture all queries that use hints

Question

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.

Answer 1

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 sys.dm_exec_query_plan.query_plan:

    <QueryPlan      NonParallelPlanReason="MaxDOPSetToOne"
    ---- this query used OPTION (MAXDOP 1) ^^^^^^^^^^^^^^
    ---- or perhaps was forced using server maxdop settings or resource governor

    <IndexScan Ordered="0" ForcedIndex="0" ForceScan="1" NoExpandHint="0">
    ---- this query used WITH (FORCESCAN) ------------^

    <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="1">
    ---- this query used WITH (NOEXPAND) ------------------------------^

    <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="1" ...>
    ---- this query used WITH (FORCESEEK) ------------------------------------^

    <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="1"
    ---- this query uses WITH (FORCESEEK(index(col))) ----------^-------------^

    <IndexScan      Ordered="0" ForcedIndex="1" ForceScan="0" NoExpandHint="0">
    ---- this query used WITH (INDEX = ...) -^

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 WITH (NOLOCK)) - you might have to:

• capture those in the act by polling sys.dm_exec_requests (though transaction_isolation_level really only tells you about things like SET TRANSACTION ISOLATION LEVEL, not table-level hints, since you can apply NOLOCK only to some tables in the query), or
• rely on parsing of the query text itself from sys.dm_exec_sql_text (noting that this can yield false positives if, say, NOLOCK is commented out, or you have a table name like dbo.SubmitNoLocks).

I also can't see any difference in the plan for query hints like OPTION (FORCE ORDER) or OPTION (FAST n), so likewise these may also need to rely on parsing the query text as opposed to relying on the plan XML.

EDIT

Since you can get a non-XML version of the plan from an alternate DMF, sys.dm_exec_text_query_plan, you don't necessarily have to become an XML guru to perform this parsing. For example, the following will capture all query plans in the cache that used WITH (INDEX...), WITH FORCESEEK[(...)], WITH FORCESCAN, WITH (NOEXPAND) or WITH (NOLOCK):

SELECT t.[text]--, qp.query_plan 
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp
WHERE 
 t.[text] NOT LIKE '%dm_exec%' -- to keep this query out of result
 AND
 (
  qp.query_plan LIKE '%ForceSeek="1"%'
  OR qp.query_plan LIKE '%Forcescan="1"%'
  OR qp.query_plan LIKE '%NoExpandHint="1"%'
  OR qp.query_plan LIKE '%ForcedIndex="1"%'
  OR t.[text] LIKE '%NOLOCK%'
 )
 -- t.[dbid] = DB_ID() -- to limit results, but may be too exclusionary
;

As identified earlier, this could produce false positives for queries like this:

SELECT * FROM dbo.table /* WITH (NOLOCK) */
SELECT * FROM dbo.RenoLocksmiths;

But outside of parsing the query text for NOLOCK, for all the plan attributes - since the XML gets entitized and characters like quotes get escaped - it will not return false positives for queries like this:

SELECT * FROM dbo.table /* Forceseek="1" */
SELECT * FROM dbo.[WITH (FORCESEEK)];

Answer 2

Using the sys.dm_exec_query_plan statement you can get the information you are looking for.

Answer 3

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:

SELECT [cp].[refcounts] 
, [cp].[usecounts] 
, [cp].[objtype] 
, [st].[dbid] 
, [st].[objectid] 
, [st].[text] 
, [qp].[query_plan] 
FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st 
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;

You can use a where clause with a regex (clr function) so that false positives are minimized close to 0.