Top 50 CPU And Disk IO Consuming Queries in SQL Server

本文主要是介绍Top 50 CPU And Disk IO Consuming Queries in SQL Server，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

Top 50 CPU Consuming Queries in SQL Server

Select 
     st.[text] AS [Query Text],          
     wt.last_execution_time AS [Last Execution Time],
     wt.execution_count AS [Execution Count],
     wt.total_worker_time/1000000 AS [Total CPU Time(second)],
     wt.total_worker_time/wt.execution_count/1000 AS [Average CPU Time(milisecond)],
     qp.query_plan,
     DB_NAME(st.dbid) AS [Database Name]
from 
    (select top 50 
          qs.last_execution_time,
          qs.execution_count,
   qs.plan_handle, 
          qs.total_worker_time
    from sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) wt
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
order by wt.total_worker_time desc

Top 50 Disk IO Consuming Queries in SQL Server

select 
     st.[text] AS [Query Text],      
     qs.last_execution_time AS [Last Execution Time],
     qs.execution_count AS [Execution Count],
     qs.total_logical_reads AS [Total Logical Read],
     qs.total_logical_reads/execution_count AS [Average Logical Read],
     qs.total_worker_time/1000000 AS [Total CPU Time(second)],
     qs.total_worker_time/qs.execution_count/1000 AS [Average CPU Time(milisecond)],
     qp.query_plan AS [Execution Plan],
     DB_NAME(st.dbid) AS [Database Name]
from 
    (select top 50 
          qs.last_execution_time,
          qs.execution_count,
      qs.plan_handle, 
          qs.total_worker_time,
          qs.total_logical_reads
    from sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) qs
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
order by qs.total_logical_reads desc

refer:

https://dbtut.com/index.php/2019/04/19/how-to-find-most-expensive-queries-in-sql-server/

https://www.tutorialspoint.com/ms_sql_server/ms_sql_server_architecture.htm

这篇关于Top 50 CPU And Disk IO Consuming Queries in SQL Server的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！