Troubles finding query plan in dm_exec_cached_plans

Question

Post reply

Troubles finding query plan in dm_exec_cached_plans

Mr. Holio

Hall of Fame

Points: 3314

May 14, 2019 at 8:40 pm

#3642105

Hello,

I'm running the following query but not getting any results even tough sys.dm_db_index_usage_stats returns 600 user seeks for index IX1. How can I find what queries performed those 600 seeks on the index? Thank you

DECLARE @index_name AS NVARCHAR(128) = '[IX1]';
 
;WITH XMLNAMESPACES
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')   
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)') AS sql_text,
obj.value('(@Database)[1]', 'varchar(128)') AS database_name,
obj.value('(@Schema)[1]', 'varchar(128)') AS schema_name,
obj.value('(@Table)[1]', 'varchar(128)') AS table_name,
obj.value('(@Index)[1]', 'varchar(128)') AS index_name,
obj.value('(@IndexKind)[1]', 'varchar(128)') AS index_type,
dm_exec_query_plan.query_plan,
dm_exec_cached_plans.usecounts AS execution_count
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS nodes(stmt)
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@index_name")]') AS index_object(obj)

__________________________
Allzu viel ist ungesund...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 1

Your query is working. I tested it running this:

SELECT *
FROM Sales.SalesOrderDetail AS sod
WHERE sod.SalesOrderDetailID = 432;

And modifying the index to [IX_SalesOrderDetail_ProductID]. It worked fine.

So, the problem must be that the plan isn't in cache. Plans age out of cache, or, may not be stored there if there's a RECOMPILE hint on the query, or, you may have a VERY complex plan which is exceeding the nesting limits on XML. In the last case, you can try querying sys.dm_exec_text_query_plan. It has all the plans, even the ones that exceed the XML nesting limit.

If the plan isn't in cache, then you'd have to have Query Store enabled or be capturing plans some other way to try to identify how a particular index was accessed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Mr. Holio Hall of Fame Points: 3314 More actions · Answer 2

Thanks!!

Is there a way to query sys.dm_exec_text_query_plan so that it only returns plans that performed at least one seek on index IX_SalesOrderDetail_ProductID? For example under <OptimizerStatsUsage> I can see all my indexes listed however the query itself doesn't even touch that index.

I got another related question with regards to single use plans / plan aging hope it's ok to post it in the same topic.

Q1.

So, I can see in sys.dm_exec_cached_plans that I got over 90,000 single use plans in the "Prepared" category (out of 100,000) and 30,000 single use plans in the "Ad-hoc" group (out of 31,000). Let's say I move some prepared queries to stored procedures accountable for all those single use plans - when will those plan get cleared from the cache (since there should only be 1 for the SP) or is this something I need to clean up manually?

What is the drawback of having so many single use plans (other than the cache being large taking lots of memory)? What performance improvement can I expect if I decrease the number of single use plans?

Q2.

Will the ALTER INDEX (or DROP/CREATE) statement automatically recompile any plans that used a given index? If so what happens with the old plans? Would they get wiped immediately?

Thank you!!!

__________________________
Allzu viel ist ungesund...

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 3

So, Q0: No. The issue is that all plans are estimated plans, so they won't show how many reads they did, just how many they think they might do. You can get that from the estimated row counts.

Q1: This is what the "optimize for Ad Hoc" setting is for. I enable it on pretty much every machine. It deals with exactly this issue. Instead of an entire plan, you get a plan stub and this reduces memory overhead.

Q2: Every plan is marked as invalid. That doesn't mean they get recompiled immediately or dropped from cache. They will age out faster and, any plan that gets reused will immediately get recompiled first.

Hope that helps a little.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Mr. Holio Hall of Fame Points: 3314 More actions · Answer 4

It does, thank you!

__________________________
Allzu viel ist ungesund...