Plan cache

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.