MSSQL2008配置性能监控脚本PerfStats.docx

-- TODO: scheduler stats IF %runmode% != REALTIME USE tempdbGOSET NOCOUNT ONSET QUOTED_IDENTIFIER ONGOIF (CHARINDEX (10.00, @@VERSION) = 0) BEGIN PRINT PRINT **** NOTE **** PRINT **** This script is for SQL Server 2008. Errors are expected when run on earlier versions. PRINT ************** PRINT ENDGOPRINT Starting SQL Server 2008 Perf Stats Script...SET LANGUAGE us_englishPRINT -- Script Source --SELECT SQL 2008 Perf Stats Script AS script_name, $Revision: 12 $ ($Change: 3355 $) AS revisionPRINT PRINT -- Script and Environment Details --PRINT Name ValuePRINT PRINT SQL Server Name + @@SERVERNAMEPRINT Machine Name + CONVERT (varchar, SERVERPROPERTY (MachineName))PRINT SQL Version (SP) + CONVERT (varchar, SERVERPROPERTY (ProductVersion)) + ( + CONVERT (varchar, SERVERPROPERTY (ProductLevel)) + )PRINT Edition + CONVERT (varchar, SERVERPROPERTY (Edition))PRINT Script Name SQL 2008 Perf Stats ScriptPRINT Script File Name $File: SQL_2005_Perf_Stats.sql $PRINT Revision $Revision: 12 $ ($Change: 3355 $)PRINT Last Modified $Date: 2007/09/25 10:03:24 $PRINT Script Begin Time + CONVERT (varchar(30), GETDATE(), 126) PRINT Current Database + DB_NAME()PRINT GOIF OBJECT_ID (sp_perf_stats10,P) IS NOT NULL DROP PROCEDURE sp_perf_stats10GOCREATE PROCEDURE sp_perf_stats10 @appname sysname=PSSDIAG, @runtime datetime AS SET NOCOUNT ON DECLARE @msg varchar(100) DECLARE @querystarttime datetime DECLARE @queryduration int DECLARE @qrydurationwarnthreshold int DECLARE @servermajorversion int DECLARE @cpu_time_start bigint, @elapsed_time_start bigint DECLARE @sql nvarchar(max) DECLARE @cte nvarchar(max) DECLARE @rowcount bigint SELECT @cpu_time_start = cpu_time, @elapsed_time_start = total_elapsed_time FROM sys.dm_exec_requests WHERE session_id = @@SPID IF OBJECT_ID (tempdb.dbo.#tmp_requests) IS NOT NULL DROP TABLE #tmp_requests IF OB