Useful queries when troubleshooting performance issues in SQL Server

Here are the some of the queries which are very useful when troubleshooting performance issues in SQL Server.

  1. This query will return actual execution plan of a given stored procedure.

    select o.object_id, s.plan_handle, h.query_planfrom sys.objects oinner join sys.dm_exec_procedure_stats s on o.object_id = s.object_idcross apply sys.dm_exec_query_plan(s.plan_handle) hwhere o.object_id = object_id({name of stored procedure})

  2. This query will return various attributes of a given stored procedure for example, what are the SET options when stored procedure is executed, how many times that stored procedure is hit by a user etc.. . You would nee to get plan_handle from above query then use it in this query.

    select * from sys.dm_exec_plan_attributes ({plan_handle from #1 query})

  3. This query will return query which are currently running in current database.

    SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time FROM sys.dm_exec_requests reqCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

  4. This query will return details of a given session for example, login_name, program and machine who is running the query, all SET OPTIONS values when query was executed, query start and end date.

    SELECT * FROM sys.dm_exec_sessions WHERE session_id in( {session Id which you can get it from #3 query})

  5. This query will return actual execution plansof all stored procedures.

    SELECT [cp].[refcounts] , [cp].[usecounts] , [cp].[objtype] , [st].[dbid] , [st].[objectid] , [st]. , [qp].[query_plan] ,cp.plan_handle 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 ;

  6. This query will clear query execution plan cache.



Hope this helps!!!

Posted in RDMS Tagged with: