Troubles finding query plan in dm_exec_cached_plans

  • Mr. Holio

    Hall of Fame

    Points: 3309

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

  • Grant Fritchey

    SSC Guru

    Points: 395417

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Mr. Holio

    Hall of Fame

    Points: 3309

    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: 395417

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Mr. Holio

    Hall of Fame

    Points: 3309

    It does, thank you!

    __________________________
    Allzu viel ist ungesund...

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

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