ASrequest_logical_readsMSSQL2008配置性能监控脚本PerfStats

--TODO: schedulerstats

--------------------

--

IF'%runmode%' !='REALTIME'USEtempdb

GO

SET NOCOUNTON

SETQUOTED_IDENTIFIER ON

GO

IF(CHARINDEX('10.00',@@VERSION)=0)BEGIN

PRINT' '

PRINT'****NOTE ****'

PRINT '**** This script is for SQL Server 2008. Errors are expected when run on earl ierversions. '

PRINT'**************'

PRINT' '

END

GO

PRINT'Starting SQL Server 2008 Perf Stats Script. . . '

SET LANGUAGE us_engl ish

PRINT'--Script Source--'

SELECT 'SQL 2008 Perf Stats Script' AS script_name, '$Revision: 12 $ ($Change: 3355 $)' ASrevision

PRINT' '

PRINT'--Scriptand Environment Detai ls--'

PRINT'Name Value'

PRINT'------------------------ ---------------------------------------------------'

PRINT'SQLServer Name '+@@SERVERNAME

PRINT'Machine Name '+CONVERT(varchar,SERVERPROPERTY('MachineName'))PRINT'SQLVersion (SP) '+CONVERT(varchar,SERVERPROPERTY('ProductVersion' ))+'('+CONVERT(varchar,SERVERPROPERTY('ProductLevel '))+') '

PRINT'Edition '+CONVERT(varchar,SERVERPROPERTY('Edition'))PRINT'ScriptName SQL2008 PerfStatsScript'

PRINT'Script Fi le Name $Fi le:SQL_2005_Perf_Stats.sql $'

PRINT'Revision $Revision: 12$($Change:3355$)'

PRINT'Last Modified $Date:2007/09/25 10:03:24$'

PRINT'Script BeginTime '+CONVERT(varchar(30),GETDATE(), 126)

PRINT'Current Database '+DB_NAME()

PRINT' '

GO

IFOBJECT_ID('sp_perf_stats10', 'P') IS NOT NULL

DROP PROCEDURE sp_perf_stats10

GO

CREATE PROCEDURE sp_perf_stats10@appname sysname='PSSDIAG',@runtime datetimeAS

SETNOCOUNT 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 FROMsys.dm_exec_requests WHERE session_id=@@SPID

IFOBJECT_ID('tempdb.dbo.#tmp_requests') ISNOTNULLDROPTABLE#tmp_requests

IFOBJECT_ID('tempdb.dbo.#tmp_requests2' ) IS NOT NULL DROPTABLE#tmp_requests2

IF@runtimeISNULL

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()

SELECTsess.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)ASwait_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(SQLBU D#487091)*/

CAS E

WHEN req.open_transaction_count IS NOT NULLTHEN req.open_transaction_count

ELSE (SELECT open_tran FROM master.dbo.sysprocesses sysproc WHERE sess.session_id=sysproc.spid)

END ASopen_trans,

LEFT(CASE COALESCE(req.transaction_isolation_level, sess.transaction_isolation_level )

WHEN 0THEN'0-ReadCommitted'

WHEN 1THEN'1-Read Uncommitted(NOLOCK)'

WHEN 2THEN'2-ReadCommitted'

WHEN3THEN'3-RepeatableRead'

WHEN4THEN'4-Serial izable'

WHEN 5THEN'5-Snapshot'

ELSE CONVERT(varchar(30), req.transaction_isolation_level )+'-UNKNOWN'

END,30)AStransaction_isolation_level,sess. is_user_process, req.cpu_time AS request_cpu_time,

/*CASE stmts necessarytoworkaround SQLBUD#438189(fixed in SP2)*/

CASE WHEN (@servermajorversion > 9) OR (@servermajorversion = 9 ANDSERVERPROPERTY('ProductLevel ')>='SP2'COLLATE Latin1_General_BIN)

THEN req. logical_reads ELSE req. logical_reads - sess. logical_reads END ASrequest_logical_reads,

CASE WHEN (@servermajorversion > 9) OR (@servermajorversion = 9 ANDSERVERPROPERTY('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 ANDSERVERPROPERTY('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.writesAS session_writes, sess. logical_readsASsession_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 ASrequest_total_elapsed_time,

CONVERT (decimal(5,2), req.percent_complete) AS percent_complete,req.estimated_completion_timeASest_completion_time, req.transaction_id,req.start_time AS request_start_time, LEFT(req.status, 15)AS request_status, req.command,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 (sess.program_name, ' '), 50)ASprogram_name, ISNULL(sess.host_process_id,0)AS host_process_id,

ISNULL (sess.cl ient_version,0)AS cl ient_version, LEFT(ISNULL (sess.cl ient_interface_name,' '),30)AS cl ient_interface_name,

LEFT(ISNULL(sess. login_name, ' '),30)ASlogin_name, LEFT(ISNULL(sess.nt_domain, ' ' ),30)AS nt_domain, LEFT(ISNULL(sess.nt_user_name, ' '),20)AS nt_user_name,

ISNULL (conn.net_packet_size, 0) AS net_packet_size, LEFT (ISNULL(conn.cl ient_net_address, ' '),20)AScl ient_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 predicatepushdown(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)) ASlast_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 (ultimatelyby-design, caused bythe lackof accurate DMVcard estimates)*/

LEFTOUTER MERGEJOIN 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 ANDtasks.request_id=req.request_id

/* The fol lowing 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

LEFTOUTER MERGEJOIN sys.dm_exec_connectionsconn on conn.session_id=sess.session_id

WHERE

/*Get execution stateforal l active queries. . . */

(req.session_id IS NOT NULL AND (sess. is_user_process = 1 OR req.status COLLATELatin1_General_BIN NOTIN('background', 'sleeping')) )

/* . . . and also any head blockers, even though they may not be running a query at themoment. */

OR (sess.session_id IN (SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requestsWHERE blocking_session_id!=0))

/* redundantduetothe use of join hints,butadded hereto suppresswarning message*/

OPTION(FORCEORDER)

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'Noactivequeries'

END

ELSEBEGIN

--Thereareactivequeries (otherthan this one).

--This query could be col lapsed into the query above. It is broken out here to avoid an

excessively

-- large memorygrantdue topoor cardinal ityestimates(see previousbugs --ultimatecauseisthe

-- lack of good statsfor 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 (reqtrans.name, sesstrans.name, ' '),24)AStran_name,

COALESCE (reqtrans.transaction_begin_time, sesstrans.transaction_begin_time) AStransaction_begin_time,

LEFT(CASE COALESCE(reqtrans.transaction_type, sesstrans.transaction_type)

WHEN 1THEN'1-Read/write'

WHEN2THEN'2-Readonly'

WHEN 3 THEN'3-System'

WHEN 4THEN'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)

WHEN0THEN'0-Initial izing'

WHEN 1THEN'1-Initial ized'

WHEN 2 THEN'2-Active'

WHEN3THEN'3-Ended'

WHEN4THEN'4-Preparing'

WHEN 5THEN'5-Prepared'

WHEN 6THEN'6-Committed'

WHEN7THEN'7-Rol l ingback'

WHEN8THEN'8-Rol led 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, r.command, r.plan_handle, r.sql_handle,r.state ment_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], r.program_name, r.host_process_id, r.cl ient_version,r.cl ient_interface_name, r. login_name, r.nt_domain,r.nt_user_name, r.net_packet_size, r.cl ient_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_idr.group_id

INTO#tmp_requests2

FROM#tmp_requests r

/* Join hintsare required here toworkaround bad QOjoin order/type decisions (ultimatelyby-design, caused bythe lackof accurate DMVcard 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 onsessions_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 ONsesstrans.transaction_id=sessions_transactions.transaction_id

/*Suboptimal perf: seeSQLBUD#449144.Butwe haveto handlethis in qry3 instead of heretoavoidSQLBUD#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

/* redundantduetothe use of join hints,butadded hereto suppresswarning message*/

OPTION(FORCEORDER)

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 typical ly takes<10ms to create, and drops the head blocker summary querycost from~250ms CPU down to~20ms. */

CREATE NONCLUSTERED INDEX idx1 ON#tmp_requests2 (blocking_session_id, session_id,wait_type,wait_duration_ms)

RAISERROR('-- requests--',0, 1)WITH NOWAIT

/*Output Resultset#1: summary of al l active requests(and head blockers)*/

/* Dynamic (but expl icitly parameterized) SQL used here to al low for (optional)direct-to-database data col lection

**without unnecessarycode dupl ication. */

SET@sql='

SELECTTOP 10000CONVERT(varchar(30),@runtime, 126)ASruntime,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, CONVERT (varchar, last_request_start_time, 126) ASla st_req uest_sta rt_t i me,

CONVERT (varchar, last_request_end_time, 126) AS last_request_end_time,session_row_count,prev_error,open_resultsets, request_total_elapsed_time,percent_complete,est_completion_time, tran_name,

CONVERT(varchar, transaction_begin_time, 126)AStransaction_begin_time, tran_type,tran_state, CONVERT (varchar, request_start_time, 126) AS request_start_time,request_status,comma nd, statement_sta rt_offset, statement_end_offset,data base_id, [user_i d],executing_managed_code, pending_io_count, CONVERT (varchar, login_time, 126) ASlogin_time,

[host_name], program_name, host_process_id, cl ient_version, cl ient_interface_name,login_name,nt_domain, nt_user_name, net_packet_size, cl ient_net_address, session_status,scheduler_id,

-- is_preemptive, is_sick, last_worker_exception, last_exception_address

--os_thread_idgroup_id

FROM#tmp_requests2 r

WHERE ISNULL([host_name], ' ' ' ' ) !=@appnameAND r.session_id!=@@SPID

/*One EC can have multiplewaits in sys.dm_os_waiting_tasks (e.g.parentthreadwaitingonmultiplechi ldren, forexample

**for paral lel create index;or memgrantwaitsfor RES_SEM_FOR_QRY_COMPILE). Thiswi l l result inthe same EC being l isted

** multiple times in the request table, which is counterintuitive for most people.Instead of showing al l wait relationships,

** for each EC we wi l l report the wait relationship that has the longest wait time. (Iftherearemultiplerelationshipswith

** the same wait time, blocker spid/ecid is used to choose one of them.) If it were notforSQLBUD#489109,we would dothis

**exclusion in the previousquerytoavoidstoringdata thatwi l l ultimatelybe fi lteredout.

*/

AND NOT EXISTS

(SELECT* FROM#tmp_requests2 r2

WHERE r.session_id = r2.session_id AND r.request_id = r2.request_id AND r.ecid =r2.ecid AND r.wait_type=r2.wait_type

AND (r2.wait_duration_ms > r.wait_duration_ms OR (r2.wait_duration_ms =r.wait_duration_msAND 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, comma nd, statement_sta rt_offset,statement_end_offset, database_id, [user_id], executing_managed_code,pending_io_count, login_time, [host_name],program_name, host_process_id,cl ient_version, cl ient_interface_name, login_name, nt_domain, nt_user_name,net_packet_size, cl ient_net_address, session_status,most_recent_sql_handle, scheduler_id) '+@sql

SET@querystarttime=GETDATE()

EXEC sp_executesql @sql, N'@runtime 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(13)+CHAR(10)

/*Resultset#2:Head blocker summary*/

/* Intra-query blocking relationships (paral lel query waits) aren't "true" blocking problemsthatweshould reporton here. */

IF NOT EXISTS (SELECT * FROM#tmp_requests2 WHERE blocking_session_id != 0 ANDwait_type NOT IN('WAITFOR', 'EXCHANGE', 'CXPACKET')AND wait_duration_ms>0)

BEGIN

PRINT' '

PRINT'--Noblocking detected--'

PRINT' '

END

ELSEBEGIN

PRINT' '

PRINT'-----------------------'

PRINT'--BLOCKING DETECTED--'

PRINT' '

RAISERROR('--headblockersummary--',0, 1)WITH NOWAIT;

/*We need stats l ike the number of spids blocked,max waittime, etc, for each headblocker. Use a recursive CTE to

**walkthe blocking hierarchy.Again,expl icitly parameterized dynamicSQL used to al lowoptional col lection direct

**toa database. */

SET@cte='

WITH BlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id,wait_type,wait_duration_ms,wait_resource, statement_sta rt_offset, statement_end_offset,pla n_ha ndle, sql_ha n dle,most_recent_sql_handle, [Level] )

AS(

SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id,head.blocking_session_id,head.wait_type, head.wait_duration_ms, head.wait_resource,hea d.statement_sta rt_offset, hea d.statement_e nd_offset,head.plan_handle, head.sql_handle, head.most_recent_sql_handle,0AS[Level]

FROM#tmp_requests2 head

WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id=0)

AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM#tmp_requests2 WHERE blocking_session_id!=0)

UNIONALL

SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id,blocked.wait_type,blocked.wait_duration_ms, blocked.wait_resource, h.statement_start_offset,h.statement_end_offset,h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level]+1

FROM#tmp_requests2 blocked

INNER JOIN BlockingHierarchy AS h ON h.session_id = blocked.blocking_session_idand h.session_id!=blocked.session_id--avoid infinite recursionfor latchtype of blocknig

WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN(' 'EXCHANGE' ', ' 'CXPACKET' ')

)'

SET@sql='

SELECT CONVERT(varchar(30),@runtime, 126)AS runtime,head_blocker_session_id, COUNT(*) AS blocked_task_count, SUM (ISNULL