By: Aaron Bertrand | Comments (1) | Related: > Dynamic Management Views and Functions
Problem
Sometimes the database administrator wants a quick inventory of all queries in use on the system that meet some criteria. One that I've come across a few times is to get a feel for all of the queries being used that involve hints, whether they are table hints (like NOEXPAND), index hints (such as FORCESEEK), or query hints (for example, MAXDOP).
The first instinct by many is to manually parse all of the code in sys.sql_modules. This is problematic because searching for terms using a wildcard can yield false positives (since they can be included in table names or they can be mentioned in comments), and not all queries are run from stored procedures and views - some are sent in an ad hoc manner through applications. Even code stored in applications is not always searchable; for example, queries can be dynamically generated in application code or by ORMs. Now, that code shouldn't contain hints, but it does. The next instinct is to use a server-side trace (or worse yet, profiler). This is casting a pretty wide net, can place a significant load on your server, and still relies on very loose parsing rules.
Solution
For *some* hints, there is - at least I believe - a better answer, and that is parsing the query plans that have been cached on the system. It has some caveats, and there are some hints it doesn't cover, but for a few options it can be quite effective. It uses the output from the DMV sys.dm_exec_cached_plans and the functions sys.dm_exec_sql_text and sys.dm_exec_text_query_plan (which outputs the plan XML in plain text, making it simpler to grep). In its simplest form, you can derive the query along with the text version of the plan for all cached plans in the following way:
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;If you inspect a few plans for queries that use hints, you will see that the plan itself does yield some clues about when these options are used. Some common hints you can derive from the XML (this is not meant to be an exhaustive list):
<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 = ...) ---^
XML-heads are probably already looking at that and visualizing how they can write a quick XQuery to derive that information, but since you can get a non-XML version of the plan, you don't necessarily have to become an XML guru to perform this parsing. For example, the following will capture, with a high degree of accuracy, all query plans in the cache that used any of the above hints:
SELECT [Query] = t.[text], [Database] = DB_NAME(t.dbid), qp.query_plan,
[ForceSeek] = CASE WHEN qp.query_plan LIKE '%ForceSeek="1"%' THEN 1 ELSE 0 END,
[ForceScan] = CASE WHEN qp.query_plan LIKE '%ForceScan="1"%' THEN 1 ELSE 0 END,
[NoExpand] = CASE WHEN qp.query_plan LIKE '%NoExpandHint="1"%' THEN 1 ELSE 0 END,
[ForceIndex] = CASE WHEN qp.query_plan LIKE '%ForcedIndex="1" ForceSeek="1"%' THEN 1 ELSE 0 END,
[NoLock] = CASE WHEN UPPER(t.[text]) LIKE '%NOLOCK%' THEN 1 ELSE 0 END,
[MaxDop] = CASE WHEN qp.query_plan LIKE '%<QueryPlan%[^<]%"MaxDopSet%'
AND UPPER(t.[text]) LIKE '%MAXDOP%' THEN 1 ELSE 0 END
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_cached_plans%' -- 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" ForceSeek="1"%'
OR UPPER(t.[text]) LIKE '%NOLOCK%'
OR (qp.query_plan LIKE '%<QueryPlan%[^<]%"MaxDopSet%' AND UPPER(t.[text]) LIKE '%MAXDOP%')
)
--AND t.[dbid] = DB_ID() -- to limit results, but may be too exclusionary
;I say high degree of accuracy because this isn't perfect. It could produce false positives for queries like this:
SELECT * FROM dbo.table /* WITH (NOLOCK) */
SELECT * FROM dbo.RenoLocksmiths;But it will *not* return false positives for queries like this, which would come out of a brute force parsing match:
SELECT * FROM dbo.table /* Forceseek="1" */
SELECT * FROM dbo.[WITH (FORCESEEK)];There are some other disclaimers and caveats you should keep in mind:
- There are several hints that aren't exposed in a deterministic way in the query plan, such as OPTION (FORCE ORDER), OPTION (FAST n), and join hints like INNER LOOP JOIN, so - like NOLOCK - these may also need to rely on parsing the query text as opposed to relying on the plan XML.
- The DMV only exposes plans that are currently in the cache - the pool disappears when you restart or run
DBCC FREEPROCCACHE(or other things that can cause this indirectly), and individual plans can get aged out. To avoid this, you can take snapshots of the above query periodically using methodology similar to this tip. - Certain hints are actually self-defeating for this purpose, e.g. OPTION (RECOMPILE) prevents the plan from being cached at all.
- If you are using the
optimize for ad hoc workloadssetting, you may not be able to discover queries associated with single-use plans if they have, in fact, only been run once.
Conclusion
I hope that this shows you a way that you can use the DMVs to find queries that are being used in your system that meet certain criteria, without using brute force methods like trace and without having to wade through large volumes of false positives.
Next Steps
- Identify patterns in query plans that highlight criteria for queries you'd like to isolate.
- Adapt the above query to find those plans (and perhaps automate the process).
- Review the following tips and other resources:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
