MSSQL_2008配置性能監(jiān)控腳本_Perf_Stats.doc

- 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 OBJECT_ID (tempdb.dbo.#tmp_requests2) IS NOT NULL DROP TABLE #tmp_requests2 IF runtime IS NULL BEGIN SET runtime = GETDATE() SET msg = Start time: + CONVERT (varchar(30), runtime, 126) RAISERROR (msg, 0, 1) WITH NOWAIT END SET qrydurationwarnthreshold = 500 - SERVERPROPERTY (ProductVersion) returns e.g. 9.00.2198.00 - 9 SET servermajorversion = REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY (ProductVersion), 2), ., ) RAISERROR (msg, 0, 1) WITH NOWAIT SET querystarttime = GETDATE() SELECT sess.session_id, req.request_id, tasks.exec_context_id AS ecid, tasks.task_address, req.blocking_session_id, LEFT (tasks.task_state, 15) AS task_state, tasks.scheduler_id, LEFT (ISNULL (req.wait_type, ), 50) AS wait_type, LEFT (ISNULL (req.wait_resource, ), 40) AS wait_resource, LEFT (req.last_wait_type, 50) AS last_wait_type, /* sysprocesses is the only way to get open_tran count for sessions w/o an active request (SQLBUD #487091) */ CASE WHEN req.open_transaction_count IS NOT NULL THEN req.open_transaction_count ELSE (SELECT open_tran FROM master.dbo.sysprocesses sysproc WHERE sess.session_id = sysproc.spid) END AS open_trans, LEFT (CASE COALESCE(req.transaction_isolation_level, sess.transaction_isolation_level) WHEN 0 THEN 0-Read Committed WHEN 1 THEN 1-Read Uncommitted (NOLOCK) WHEN 2 THEN 2-Read Committed WHEN 3 THEN 3-Repeatable Read WHEN 4 THEN 4-Serializable WHEN 5 THEN 5-Snapshot ELSE CONVERT (varchar(30), req.transaction_isolation_level) + -UNKNOWN END, 30) AS transaction_isolation_level, sess.is_user_process, req.cpu_time AS request_cpu_time, /* CASE stmts necessary to workaround SQLBUD #438189 (fixed in SP2) */ CASE WHEN (servermajorversion 9) OR (servermajorversion = 9 AND SERVERPROPERTY (ProductLevel) = SP2 COLLATE Latin1_General_BIN) THEN req.logical_reads ELSE req.logical_reads - sess.logical_reads END AS request_logical_reads, CASE WHEN (servermajorversion 9) OR (servermajorversion = 9 AND SERVERPROPERTY (ProductLevel) = SP2 COLLATE Latin1_General_BIN) THEN req.reads ELSE req.reads - sess.reads END AS request_reads, CASE WHEN (servermajorversion 9) OR (servermajorversion = 9 AND SERVERPROPERTY (ProductLevel) = SP2 COLLATE Latin1_General_BIN) THEN req.writes ELSE req.writes - sess.writes END AS request_writes, sess.memory_usage, sess.cpu_time AS session_cpu_time, sess.reads AS session_reads, sess.writes AS session_writes, sess.logical_reads AS session_logical_reads, sess.total_scheduled_time, sess.total_elapsed_time, sess.last_request_start_time, sess.last_request_end_time, sess.row_count AS session_row_count, sess.prev_error, req.open_resultset_count AS open_resultsets, req.total_elapsed_time AS request_total_elapsed_time, CONVERT (decimal(5,2), req.percent_complete) AS percent_complete, req.estimated_completion_time AS est_completion_time, req.transaction_id, req.start_time AS request_start_time, LEFT (req.status, 15) AS request_status, mand, req.plan_handle, req.sql_handle, req.statement_start_offset, req.statement_end_offset, req.database_id, req.user_id, req.executing_managed_code, tasks.pending_io_count, sess.login_time, LEFT (sess.host_name, 20) AS host_name, LEFT (ISNULL (gram_name, ), 50) AS program_name, ISNULL (sess.host_process_id, 0) AS host_process_id, ISNULL (sess.client_version, 0) AS client_version, LEFT (ISNULL (sess.client_interface_name, ), 30) AS client_interface_name, LEFT (ISNULL (sess.login_name, ), 30) AS login_name, LEFT (ISNULL (sess.nt_domain, ), 30) AS nt_domain, LEFT (ISNULL (sess.nt_user_name, ), 20) AS nt_user_name, ISNULL (_packet_size, 0) AS net_packet_size, LEFT (ISNULL (conn.client_net_address, ), 20) AS client_net_address, conn.most_recent_sql_handle, LEFT (sess.status, 15) AS session_status, /* sys.dm_os_workers and sys.dm_os_threads removed due to perf impact, no predicate pushdown (SQLBU #488971) */ - workers.is_preemptive, - workers.is_sick, - workers.exception_num AS last_worker_exception, - convert (varchar (20), master.dbo.fn_varbintohexstr (workers.exception_address) AS last_exception_address - threads.os_thread_id sess.group_id INTO #tmp_requests FROM sys.dm_exec_sessions sess /* Join hints are required here to work around bad QO join order/type decisions (ultimately by-design, caused by the lack of accurate DMV card estimates) */ LEFT OUTER MERGE JOIN sys.dm_exec_requests req ON sess.session_id = req.session_id LEFT OUTER MERGE JOIN sys.dm_os_tasks tasks ON tasks.session_id = sess.session_id AND tasks.request_id = req.request_id /* The following two DMVs removed due to perf impact, no predicate pushdown (SQLBU #488971) */ - LEFT OUTER MERGE JOIN sys.dm_os_workers workers ON tasks.worker_address = workers.worker_address - LEFT OUTER MERGE JOIN sys.dm_os_threads threads ON workers.thread_address = threads.thread_address LEFT OUTER MERGE JOIN sys.dm_exec_connections conn on conn.session_id = sess.session_id WHERE /* Get execution state for all active queries. */ (req.session_id IS NOT NULL AND (sess.is_user_process = 1 OR req.status COLLATE Latin1_General_BIN NOT IN (background, sleeping) /* . and also any head blockers, even though they may not be running a query at the moment. */ OR (sess.session_id IN (SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0) /* redundant due to the use of join hints, but added here to suppress warning message */ OPTION (FORCE ORDER) SET rowcount = ROWCOUNT SET queryduration = DATEDIFF (ms, querystarttime, GETDATE() IF queryduration qrydurationwarnthreshold PRINT DebugPrint: perfstats qry1 - + CONVERT (varchar, queryduration) + ms, rowcount= + CONVERT(varchar, rowcount) + CHAR(13) + CHAR(10) IF NOT EXISTS (SELECT * FROM #tmp_requests WHERE session_id SPID AND ISNULL (host_name, ) != appname) BEGIN PRINT No active queries END ELSE BEGIN - There are active queries (other than this one). - This query could be collapsed into the query above. It is broken out here to avoid an excessively - large memory grant due to poor cardinality estimates (see previous bugs - ultimate cause is the - lack of good stats for many DMVs). SET querystarttime = GETDATE() SELECT IDENTITY (int,1,1) AS tmprownum, r.session_id, r.request_id, r.ecid, r.blocking_session_id, ISNULL (waits.blocking_exec_context_id, 0) AS blocking_ecid, r.task_state, r.wait_type, ISNULL (waits.wait_duration_ms, 0) AS wait_duration_ms, r.wait_resource, LEFT (ISNULL (waits.resource_description, ), 140) AS resource_description, r.last_wait_type, r.open_trans, r.transaction_isolation_level, r.is_user_process, r.request_cpu_time, r.request_logical_reads, r.request_reads, r.request_writes, r.memory_usage, r.session_cpu_time, r.session_reads, r.session_writes, r.session_logical_reads, r.total_scheduled_time, r.total_elapsed_time, r.last_request_start_time, r.last_request_end_time, r.session_row_count, r.prev_error, r.open_resultsets, r.request_total_elapsed_time, r.percent_complete, r.est_completion_time, - r.tran_name, r.transaction_begin_time, r.tran_type, r.tran_state, LEFT (COALESCE (, , ), 24) AS tran_name, COALESCE (reqtrans.transaction_begin_time, sesstrans.transaction_begin_time) AS transaction_begin_time, LEFT (CASE COALESCE (reqtrans.transaction_type, sesstrans.transaction_type) WHEN 1 THEN 1-Read/write WHEN 2 THEN 2-Read only WHEN 3 THEN 3-System WHEN 4 THEN 4-Distributed ELSE CONVERT (varchar(30), COALESCE (reqtrans.transaction_type, sesstrans.transaction_type) + -UNKNOWN END, 15) AS tran_type, LEFT (CASE COALESCE (reqtrans.transaction_state, sesstrans.transaction_state) WHEN 0 THEN 0-Initializing WHEN 1 THEN 1-Initialized WHEN 2 THEN 2-Active WHEN 3 THEN 3-Ended WHEN 4 THEN 4-Preparing WHEN 5 THEN 5-Prepared WHEN 6 THEN 6-Committed WHEN 7 THEN 7-Rolling back WHEN 8 THEN 8-Rolled back ELSE CONVERT (varchar(30), COALESCE (reqtrans.transaction_state, sesstrans.transaction_state) + -UNKNOWN END, 15) AS tran_state, r.request_start_time, r.request_status, mand, r.plan_handle, r.sql_handle, r.statement_start_offset, r.statement_end_offset, r.database_id, r.user_id, r.executing_managed_code, r.pending_io_count, r.login_time, r.host_name, gram_name, r.host_process_id, r.client_version, r.client_interface_name, r.login_name, r.nt_domain, r.nt_user_name, _packet_size, r.client_net_address, r.most_recent_sql_handle, r.session_status, r.scheduler_id, - r.is_preemptive, r.is_sick, r.last_worker_exception, r.last_exception_address, - r.os_thread_id r.group_id INTO #tmp_requests2 FROM #tmp_requests r /* Join hints are required here to work around bad QO join order/type decisions (ultimately by-design, caused by the lack of accurate DMV card estimates) */ /* Perf: no predicate pushdown on sys.dm_tran_active_transactions (SQLBU #489000) */ LEFT OUTER MERGE JOIN sys.dm_tran_active_transactions reqtrans ON r.transaction_id = reqtrans.transaction_id /* No predicate pushdown on sys.dm_tran_session_transactions (SQLBU #489000) */ LEFT OUTER MERGE JOIN sys.dm_tran_session_transactions sessions_transactions on sessions_transactions.session_id = r.session_id /* No predicate pushdown on sys.dm_tran_active_transactions (SQLBU #489000) */ LEFT OUTER MERGE JOIN sys.dm_tran_active_transactions sesstrans ON sesstrans.transaction_id = sessions_transactions.transaction_id /* Suboptimal perf: see SQLBUD #449144. But we have to handle this in qry3 instead of here to avoid SQLBUD #489109. */ LEFT OUTER MERGE JOIN sys.dm_os_waiting_tasks waits ON waits.waiting_task_address = r.task_address ORDER BY r.session_id, blocking_ecid /* redundant due to the use of join hints, but added here to suppress warning message */ OPTION (FORCE ORDER) SET rowcount = ROWCOUNT SET queryduration = DATEDIFF (ms, querystarttime, GETDATE() IF queryduration qrydurationwarnthreshold PRINT DebugPrint: perfstats qry2 - + CONVERT (varchar, queryduration) + ms, rowcount= + CONVERT(varchar, rowcount) + CHAR(13) + CHAR(10) /* This index typically takes r.wait_duration_ms OR (r2.wait_duration_ms = r.wait_duration_ms AND r2.tmprownum r.tmprownum) IF %runmode% = REALTIME SET sql = INSERT INTO tbl_REQUESTS (runtime, session_id, request_id, ecid, blocking_session_id, blocking_ecid, task_state, wait_type, wait_duration_ms, wait_resource, resource_description, last_wait_type, open_trans, transaction_isolation_level, is_user_process, request_cpu_time, request_logical_reads, request_reads, request_writes, memory_usage, session_cpu_time, session_reads, session_writes, session_logical_reads, total_scheduled_time, total_elapsed_time, last_request_start_time, last_request_end_time, session_row_count, prev_error, open_resultsets, request_total_elapsed_time, percent_complete, estimated_completion_time, tran_name, transaction_begin_time, tran_type, tran_state, request_start_time, request_status, command, statement_start_offset, statement_end_offset, database_id, user_id, executing_managed_code, pending_io_count, login_time, host_name, program_name, host_process_id, client_version, client_interface_name, login_name, nt_domain, nt_user_name, net_packet_size, client_net_address, session_status, most_recent_sql_handle, scheduler_id) + sql SET querystarttime = GETDATE() EXEC sp_executesql sql, Nruntime datetime, appname sysname, runtime = runtime, appname = appname SET rowcount = ROWCOUNT SET queryduration = DATEDIFF (ms, querystarttime, GETDATE() RAISERROR (, 0, 1) WITH NOWAIT IF queryduration qrydurationwarnthreshold PRINT DebugPrint: perfstats qry3 - + CONVERT (varchar, queryduration) + ms, rowcount= + CONVERT(varchar, rowcount) + CHAR(