SqlServer性能优化用SQL(四)【执行次数和性能监控】

概述SQL脚本 --查询CPU最高的10条SQLSELECT TOP 10 TEXT AS 'SQL Statement' ,last_execution_time AS 'Last Execution Time' ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Av sql脚本

--查询cpu最高的10条sql@H_301_13@SELECT top 10 TEXT @H_301_13@AS 'sql Statement',last_execution_time @H_301_13@AS 'Last Execution Time',(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count @H_301_13@AS [Average IO],(total_worker_time / execution_count) / 1000000.0 @H_301_13@AS [Average cpu @H_301_13@Time (sec)],(total_elapsed_time / execution_count) / 1000000.0 @H_301_13@AS [Average Elapsed @H_301_13@Time (sec)],execution_count @H_301_13@AS "Execution Count",qs.total_physical_reads,qs.total_logical_writes,qp.query_plan @H_301_13@AS "query Plan" @H_301_13@FROM sys.dm_exec_query_stats qs @H_301_13@CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st @H_301_13@CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp @H_301_13@ORDER @H_301_13@BY total_elapsed_time / execution_count @H_301_13@DESC --找出执行频繁的语句的@H_301_13@sql语句 @H_301_13@with aa @H_301_13@as ( @H_301_13@SELECT --执行次数 QS.execution_count,--查询语句 SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,((@H_301_13@CASE QS.statement_end_offset @H_301_13@WHEN -1 @H_301_13@THEN DATALENGTH(st.text) @H_301_13@ELSE QS.statement_end_offset @H_301_13@END - QS.statement_start_offset)/2) + 1 ) @H_301_13@AS statement_text,--执行文本 ST.text,--执行计划 qs.last_elapsed_time,qs.min_elapsed_time,qs.max_elapsed_time,QS.total_worker_time,QS.last_worker_time,QS.max_worker_time,QS.min_worker_time @H_301_13@FROM sys.dm_exec_query_stats QS --关键字 @H_301_13@CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST @H_301_13@WHERE QS.last_execution_time > '2016-02-14 00:00:00' @H_301_13@and execution_count > 500 -- @H_301_13@AND ST.text @H_301_13@liKE '%%' --@H_301_13@ORDER @H_301_13@BY --QS.execution_count @H_301_13@DESC ) @H_301_13@select text,max(execution_count) execution_count --,last_elapsed_time,min_elapsed_time,max_elapsed_time @H_301_13@from aa @H_301_13@where [text] @H_301_13@not @H_301_13@like '%sp_MSupd_%' @H_301_13@and [text] @H_301_13@not @H_301_13@like '%sp_MSins_%' @H_301_13@and [text] @H_301_13@not @H_301_13@like '%sp_MSdel_%' @H_301_13@group @H_301_13@by text @H_301_13@order @H_301_13@by 2 @H_301_13@desc -- 查找逻辑读取最高的查询(存储过程) @H_301_13@SELECT top ( 25 ) P.name @H_301_13@AS [SP name],Deps.total_logical_reads @H_301_13@AS [TotalLogicalReads],deps.total_logical_reads / deps.execution_count @H_301_13@AS [AvgLogicalReads],deps.execution_count,ISNulL(deps.execution_count / DATEDIFF(@H_301_13@Second,deps.cached_time,GETDATE()),0) @H_301_13@AS [Calls/@H_301_13@Second],deps.total_elapsed_time,deps.total_elapsed_time / deps.execution_count @H_301_13@AS [avg_elapsed_time],deps.cached_time @H_301_13@FROM sys.procedures @H_301_13@AS p @H_301_13@INNER @H_301_13@JOIN sys.dm_exec_procedure_stats @H_301_13@AS deps @H_301_13@ON p.[Object_ID] = deps.[Object_ID] @H_301_13@WHERE deps.Database_ID = DB_ID() @H_301_13@ORDER @H_301_13@BY deps.total_logical_reads @H_301_13@DESC;

总结

以上是内存溢出为你收集整理的SqlServer性能优化用SQL(四)【执行次数和性能监控】全部内容，希望文章能够帮你解决SqlServer性能优化用SQL(四)【执行次数和性能监控】所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。