Performance tuning scripts
--Long Running Queries
SELECT r.session_id,
st.TEXT AS batch_text,
qp.query_plan AS 'XML Plan',
r.start_time,
r.status,
r.total_elapsed_time
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE DB_NAME(r.database_id) = '{db_name}'
ORDER BY cpu_time DESC;
st.TEXT AS batch_text,
qp.query_plan AS 'XML Plan',
r.start_time,
r.status,
r.total_elapsed_time
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE DB_NAME(r.database_id) = '{db_name}'
ORDER BY cpu_time DESC;
Tvenkat
I have 16+ years of experience in IT and 11+ years of experience in training. I am currently working in Bangalore and occasionally in Hyderabad as well. I have worked in various domains including Telecom, Healthcare, Pharma, Insurance, Retail, and Storage. Currently, I am working as a Cloud Principal Consultant in an MNC Product based company.
