Friday 17 February 2017

How to check expensive queries in SQL server

select top 50 qs.creation_time,
              qs.execution_count,
              qs.total_worker_time as total_cpu_time,
              qs.max_worker_time   as max_cpu_time,
              qs.total_elapsed_time,
              qs.max_elapsed_time,
              qs.total_logical_reads,
              qs.max_logical_reads,
              qs.total_physical_reads,
              qs.max_physical_reads,
              t.[text],
              qp.query_plan,
              t.dbid,
              t.objectid,
              t.encrypted,
              qs.plan_handle,
              qs.plan_generation_num
from   sys.dm_exec_query_stats qs
       cross apply sys.Dm_exec_sql_text(plan_handle) as t
       cross apply sys.Dm_exec_query_plan(plan_handle) as qp
--order  by qs.total_worker_time desc
--ORDER BY qs.total_logical_reads DESC -- logical reads
 ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

No comments:

Post a Comment

Check if column exists or not in the SQL server

--method 1 IF EXISTS(SELECT 1 FROM sys.columns with (nolock)           WHERE Name = N'LoginName'           AND Object_ID = Objec...