Welcome to Sign in | Join | Help
in Search

Get query text and query plans for cached query plans

Last post 10-17-2006, 11:15 AM by xmldeveloper. 0 replies.
Sort Posts: Previous Next
  •  10-17-2006, 11:15 AM 412

    Get query text and query plans for cached query plans

    Source: Forced and Simple Parameterization in SQL Server 2005 (TSQLNet Weblog - Cihangir Biyikoglu)

    SELECT

    c.[text],

    a.refcounts,

    a.usecounts,

    d.query_plan

    FROM sys.dm_exec_cached_plans a

    OUTER APPLY sys.dm_exec_plan_attributes(a.plan_handle) b

    OUTER APPLY sys.dm_exec_sql_text(CAST(b.VALUE AS VARBINARY)) c

    OUTER APPLY sys.dm_exec_query_plan(a.plan_handle) d

    WHERE b.attribute='sql_handle' AND a.cacheobjtype = 'Compiled Plan'

    ORDER BY a.usecounts DESC

     


    Cristian Andrei Lefter, SQL Server MVP
    MCT, MCSA, MCDBA, MCAD, MCSD .NET,
    MCTS, MCITP - Database Administrator SQL Server 2005
    http://sqlserver.ro
    Filed under:
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems