Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 19 '12 at 23:19

This question came from our site for professional and enthusiast programmers.

1  
Query hints, table hints, join hints, index hints, plan guides, all of the above...? –  Aaron Bertrand Jun 19 '12 at 20:56

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

share|improve this answer
1  
Well there should be information you can extract from cached execution plans, since I believe that information is stored in the XML. I just don't have a query handy that can do it. –  Aaron Bertrand Jun 19 '12 at 20:53
    
@AaronBertrand I added a query to show the cached plans as you suggest. 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. –  buckley Jun 19 '12 at 20:56
1  
I also would recommend a server-side trace, not SQL Profiler. Especially if you're going to track this over time, Profiler is the last tool you want to use. –  Aaron Bertrand Jun 19 '12 at 21:00
    
Thank you guys, I think this would help I just need to figure out how to break the execution plan so I can know for sure that this query is using a hint. –  mvillegascuellar Jun 19 '12 at 21:45
    
@AaronBertrand Good idea, cleaned up and comments summarized to the answer –  buckley Jun 19 '12 at 23:09

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

share|improve this answer
    
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. –  Max Vernon Oct 16 '12 at 13:40

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)];
share|improve this answer
    
Great thank you very much –  mvillegascuellar Jun 19 '12 at 22:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.