none
Execution plan from query cache its coming up with just SELECT only and nothing else RRS feed

  • Question

  • Hi Gurus

    I am trying to capture an execution plan form the query cache using the T-SQL

    SELECT cp.objtype AS ObjectType,
    
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    
    cp.usecounts AS ExecutionCount,
    
    st.TEXT AS QueryText,
    
    qp.query_plan AS QueryPlan
    
    FROM sys.dm_exec_cached_plans AS cp
    
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
    
    WHERE st.TEXT like '%TtestTable%

    I get the results like the below  but when I click on the execution plan it just comes up with SELECT block 0%.. .

    

    I tried to change the setting in SSMS or XML form 2 MB to unlimited etc..still doesn't resolve my question.

    Why I need execution plan form cache?

    Its because I just want to see the query execution plan inside the sql engine rather me capturing the actual execution plan as it may be different due parameter sniffing etc..

    Kind Regards,

    Chinna.

    Friday, December 13, 2019 2:34 PM

All replies

  • So it shows you that there are SELECT statements on that table currently in cache. What do you expect to see?

    SELECT * FROM tblname WHERE

    UPDATE tblname SET...

    I see both plans....

    SELECT st.text, cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, qp.query_plan --, *
    FROM sys.dm_exec_cached_plans AS cp
            CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
            CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
    WHERE cp.objtype IN ('Adhoc', 'Prepared')
            AND st.text LIKE '%tblname%'
    ORDER BY cp.objtype

    Also see this SP


    CREATE PROC [dbo].[dba_SearchCachedPlans] 
    @StringToSearchFor VARCHAR(255) 
    AS 
    /*---------------------------------------------------------------------- 

    Example Usage: 
    1. exec dbo.dba_SearchCachedPlans '%<MissingIndexes>%' 
    2. exec dbo.dba_SearchCachedPlans '%<ColumnsWithNoStatistics>%' 
    3. exec dbo.dba_SearchCachedPlans '%<TableScan%' 
    4. exec dbo.dba_SearchCachedPlans '%CREATE PROC%MessageWrite%' 

    -----------------------------------------------------------------------*/ 
    BEGIN 
    -- Do not lock anything, and do not get held up by any locks. 
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
    SELECT TOP 20 
    st.text AS [SQL] 
    , cp.cacheobjtype 
    , cp.objtype 
    , DB_NAME(st.dbid)AS [DatabaseName] 
    , cp.usecounts AS [Plan usage] 
    , 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 
    WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor 
    ORDER BY cp.usecounts DESC 
    END 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, December 16, 2019 4:51 AM
    Moderator
  • Hi Chinna,

    Please refer to the following articles which might help:

    How to Analyze SQL Execution Plan Graphical Components

    SQL Server Query Execution Plans in SQL Server Management Studio

    Graphical Execution Plans for Simple SQL Queries

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 16, 2019 5:52 AM
  • Hi URI, When I click on the execution plan I see just the ‘Select 0 %’ and nothing else. I expect to see the graphical plan about costs etc. Kind Regards, Chinna
    Monday, December 16, 2019 9:01 AM
  • Please run the query I posted above  and click on quey_plan column

    SELECT st.text, cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, qp.query_plan --, *
    FROM sys.dm_exec_cached_plans AS cp
            CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
            CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
    WHERE cp.objtype IN ('Adhoc', 'Prepared')
            AND st.text LIKE '%tblname%'
    ORDER BY cp.objtype


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 16, 2019 9:07 AM
    Moderator
  • Hi Uri,

    Thanks for the reply.I have tried that but when I click on the Plan and I get just the select 0% and nothing else.

    Kind Regards,

    Chinna

    

    Monday, December 16, 2019 1:48 PM
  • What SSMS version do you use? If you click on Show Actual Execution plan icon and run the query still see same output? I see this one 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 17, 2019 4:54 AM
    Moderator
  • Hi Uri Dimant,

    I want the execution plan form cache.If I run the query I can see the execution plan but I want the execution plan form cache as that can be different.

    Its SQLserver 2012 enterprise and it is standard ssms.

    Kind Regards,

    Chinna.

    Tuesday, December 17, 2019 12:09 PM
  • Run this script , still see only SELECT operator?

    DBCC FREEPROCCACHE

    GO
    USE AdventureWorks
    GO
    SELECT *
    FROM Sales.SalesOrderHeader
    WHERE SalesOrderID = 56000
    GO


    declare @i int
    set @i = 56004
    SELECT *
    FROM Sales.SalesOrderHeader
    WHERE SalesOrderID = @i
    GO


    SELECT st.text, cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, qp.query_plan --, *
    FROM sys.dm_exec_cached_plans AS cp
            CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
            CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
    WHERE cp.objtype IN ('Adhoc', 'Prepared')
            AND st.text LIKE '%Sales.SalesOrderHeader%'
    ORDER BY cp.objtype


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, December 18, 2019 4:59 AM
    Moderator
  • Hi Uri

    I cannot run DBCC FREEPROCCACHE on a live production server 

    I need the plan form cache. 

    When I run the query on the SSMS I do get the execution plan but I want to verify if the plan in the cache is identical or not?

    Kind Regards,

    Chinna

    Thursday, December 19, 2019 4:39 PM
  • What version you are using? Can you enable Query Store?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, December 22, 2019 5:44 AM
    Moderator
  • Hi Uri Dimant,

    Its sql server 2012 enterprise.

    Kind Regards,

    Keerthi.

    Monday, December 23, 2019 10:53 AM
  • And you have latest service pack?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 24, 2019 4:55 AM
    Moderator