Plan cache

S_NO 21 Reputation points
2022-03-21T14:42:21.027+00:00

Team,

I need an query to get- SQL plan handle,sql text,plan handle,Login name,programname,database name,runningstatment and its associated batch in place, during Run time in sql server

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,852 questions
0 comments No comments
{count} votes

Accepted answer
  1. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2022-03-22T03:27:52.243+00:00

    Hi SNO-0014,
    You can also try to use sys.dm_exec_text_query_plan, sys.dm_exec_sql_text , sys.dm_exec_requests and sys.dm_exec_sessions. Please check the query which might be helpful.

    SELECT  
       S.login_name, S.program_name, r.session_id, t.text, q.query_plan, CONVERT(XML, q.query_plan) xml_query_plan,r.sql_handle  
    FROM sys.dm_exec_requests r  
    JOIN sys.dm_exec_sessions s  
    ON r.session_id = s.session_id  
    CROSS APPLY sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) q  
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t  
    WHERE  
       s.is_user_process = 1  
       AND r.session_id <> @@SPID  
    

    Best Regards,
    Amelia


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


3 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,096 Reputation points
    2022-03-21T15:02:17.86+00:00

    You can use sys.dm_exec_cached_plans to get a row for each query plan that is cached by SQL Server. You can use some JOINING to get more information related

    https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-cached-plans-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699


  2. S_NO 21 Reputation points
    2022-03-21T15:11:24.88+00:00

    Thank you,but I needed to get all the details as I have asked that becomes easy to get during that time..whats happening from where and who.


  3. Erland Sommarskog 102.1K Reputation points MVP
    2022-03-21T23:17:09.293+00:00

    It is not very clear what you are asking for, but take a look at my beta_lockinfo. It may either give you what you want, or you can work from the code to get the query you want.