MSSQL配置性能监控脚本PerfStats.docx
- 文档编号:10557907
- 上传时间:2023-05-26
- 格式:DOCX
- 页数:38
- 大小:26.21KB
MSSQL配置性能监控脚本PerfStats.docx
《MSSQL配置性能监控脚本PerfStats.docx》由会员分享,可在线阅读,更多相关《MSSQL配置性能监控脚本PerfStats.docx(38页珍藏版)》请在冰点文库上搜索。
MSSQL配置性能监控脚本PerfStats
--TODO:
schedulerstats
--------------------
--
IF'%runmode%'!
='REALTIME'USEtempdb
GO
SETNOCOUNTON
SETQUOTED_IDENTIFIERON
GO
IF(CHARINDEX('10.00',@@VERSION)=0)BEGIN
PRINT''
PRINT'****NOTE****'
PRINT'****ThisscriptisforSQLServer2008.Errorsareexpectedwhenrunonearlierversions.'
PRINT'**************'
PRINT''
END
GO
PRINT'StartingSQLServer2008PerfStatsScript...'
SETLANGUAGEus_english
PRINT'--ScriptSource--'
SELECT'SQL2008PerfStatsScript'ASscript_name,'$Revision:
12$($Change:
3355$)'ASrevision
PRINT''
PRINT'--ScriptandEnvironmentDetails--'
PRINT'NameValue'
PRINT'---------------------------------------------------------------------------'
PRINT'SQLServerName'+@@SERVERNAME
PRINT'MachineName'+CONVERT(varchar,SERVERPROPERTY('MachineName'))
PRINT'SQLVersion(SP)'+CONVERT(varchar,SERVERPROPERTY('ProductVersion'))+'('+CONVERT(varchar,SERVERPROPERTY('ProductLevel'))+')'
PRINT'Edition'+CONVERT(varchar,SERVERPROPERTY('Edition'))
PRINT'ScriptNameSQL2008PerfStatsScript'
PRINT'ScriptFileName$File:
SQL_2005_Perf_Stats.sql$'
PRINT'Revision$Revision:
12$($Change:
3355$)'
PRINT'LastModified$Date:
2007/09/2510:
03:
24$'
PRINT'ScriptBeginTime'+CONVERT(varchar(30),GETDATE(),126)
PRINT'CurrentDatabase'+DB_NAME()
PRINT''
GO
IFOBJECT_ID('sp_perf_stats10','P')ISNOTNULL
DROPPROCEDUREsp_perf_stats10
GO
CREATEPROCEDUREsp_perf_stats10@appnamesysname='PSSDIAG',@runtimedatetimeAS
SETNOCOUNTON
DECLARE@msgvarchar(100)
DECLARE@querystarttimedatetime
DECLARE@querydurationint
DECLARE@qrydurationwarnthresholdint
DECLARE@servermajorversionint
DECLARE@cpu_time_startbigint,@elapsed_time_startbigint
DECLARE@sqlnvarchar(max)
DECLARE@ctenvarchar(max)
DECLARE@rowcountbigint
SELECT@cpu_time_start=cpu_time,@elapsed_time_start=total_elapsed_timeFROMsys.dm_exec_requestsWHEREsession_id=@@SPID
IFOBJECT_ID('tempdb.dbo.#tmp_requests')ISNOTNULLDROPTABLE#tmp_requests
IFOBJECT_ID('tempdb.dbo.#tmp_requests2')ISNOTNULLDROPTABLE#tmp_requests2
IF@runtimeISNULL
BEGIN
SET@runtime=GETDATE()
SET@msg='Starttime:
'+CONVERT(varchar(30),@runtime,126)
RAISERROR(@msg,0,1)WITHNOWAIT
END
SET@qrydurationwarnthreshold=500
--SERVERPROPERTY('ProductVersion')returnse.g."9.00.2198.00"-->9
SET@servermajorversion=REPLACE(LEFT(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),2),'.','')
RAISERROR(@msg,0,1)WITHNOWAIT
SET@querystarttime=GETDATE()
SELECT
sess.session_id,req.request_id,tasks.exec_context_idASecid,tasks.task_address,req.blocking_session_id,LEFT(tasks.task_state,15)AStask_state,
tasks.scheduler_id,LEFT(ISNULL(req.wait_type,''),50)ASwait_type,LEFT(ISNULL(req.wait_resource,''),40)ASwait_resource,
LEFT(req.last_wait_type,50)ASlast_wait_type,
/*sysprocessesistheonlywaytogetopen_trancountforsessionsw/oanactiverequest(SQLBUD#487091)*/
CASE
WHENreq.open_transaction_countISNOTNULLTHENreq.open_transaction_count
ELSE(SELECTopen_tranFROMmaster.dbo.sysprocessessysprocWHEREsess.session_id=sysproc.spid)
ENDASopen_trans,
LEFT(CASECOALESCE(req.transaction_isolation_level,sess.transaction_isolation_level)
WHEN0THEN'0-ReadCommitted'
WHEN1THEN'1-ReadUncommitted(NOLOCK)'
WHEN2THEN'2-ReadCommitted'
WHEN3THEN'3-RepeatableRead'
WHEN4THEN'4-Serializable'
WHEN5THEN'5-Snapshot'
ELSECONVERT(varchar(30),req.transaction_isolation_level)+'-UNKNOWN'
END,30)AStransaction_isolation_level,
sess.is_user_process,req.cpu_timeASrequest_cpu_time,
/*CASEstmtsnecessarytoworkaroundSQLBUD#438189(fixedinSP2)*/
CASEWHEN(@servermajorversion>9)OR(@servermajorversion=9ANDSERVERPROPERTY('ProductLevel')>='SP2'COLLATELatin1_General_BIN)
THENreq.logical_readsELSEreq.logical_reads-sess.logical_readsENDASrequest_logical_reads,
CASEWHEN(@servermajorversion>9)OR(@servermajorversion=9ANDSERVERPROPERTY('ProductLevel')>='SP2'COLLATELatin1_General_BIN)
THENreq.readsELSEreq.reads-sess.readsENDASrequest_reads,
CASEWHEN(@servermajorversion>9)OR(@servermajorversion=9ANDSERVERPROPERTY('ProductLevel')>='SP2'COLLATELatin1_General_BIN)
THENreq.writesELSEreq.writes-sess.writesENDASrequest_writes,
sess.memory_usage,sess.cpu_timeASsession_cpu_time,sess.readsASsession_reads,sess.writesASsession_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_countASsession_row_count,
sess.prev_error,req.open_resultset_countASopen_resultsets,req.total_elapsed_timeASrequest_total_elapsed_time,
CONVERT(decimal(5,2),req.percent_complete)ASpercent_complete,req.estimated_completion_timeASest_completion_time,req.transaction_id,
req.start_timeASrequest_start_time,LEFT(req.status,15)ASrequest_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(sess.program_name,''),50)ASprogram_name,ISNULL(sess.host_process_id,0)AShost_process_id,
ISNULL(sess.client_version,0)ASclient_version,LEFT(ISNULL(sess.client_interface_name,''),30)ASclient_interface_name,
LEFT(ISNULL(sess.login_name,''),30)ASlogin_name,LEFT(ISNULL(sess.nt_domain,''),30)ASnt_domain,LEFT(ISNULL(sess.nt_user_name,''),20)ASnt_user_name,
ISNULL(_packet_size,0)ASnet_packet_size,LEFT(ISNULL(conn.client_net_address,''),20)ASclient_net_address,conn.most_recent_sql_handle,
LEFT(sess.status,15)ASsession_status,
/*sys.dm_os_workersandsys.dm_os_threadsremovedduetoperfimpact,nopredicatepushdown(SQLBU#488971)*/
--workers.is_preemptive,
--workers.is_sick,
--workers.exception_numASlast_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
FROMsys.dm_exec_sessionssess
/*JoinhintsarerequiredheretoworkaroundbadQOjoinorder/typedecisions(ultimatelyby-design,causedbythelackofaccurateDMVcardestimates)*/
LEFTOUTERMERGEJOINsys.dm_exec_requestsreqONsess.session_id=req.session_id
LEFTOUTERMERGEJOINsys.dm_os_taskstasksONtasks.session_id=sess.session_idANDtasks.request_id=req.request_id
/*ThefollowingtwoDMVsremovedduetoperfimpact,nopredicatepushdown(SQLBU#488971)*/
--LEFTOUTERMERGEJOINsys.dm_os_workersworkersONtasks.worker_address=workers.worker_address
--LEFTOUTERMERGEJOINsys.dm_os_threadsthreadsONworkers.thread_address=threads.thread_address
LEFTOUTERMERGEJOINsys.dm_exec_connectionsconnonconn.session_id=sess.session_id
WHERE
/*Getexecutionstateforallactivequeries...*/
(req.session_idISNOTNULLAND(sess.is_user_process=1ORreq.statusCOLLATELatin1_General_BINNOTIN('background','sleeping')))
/*...andalsoanyheadblockers,eventhoughtheymaynotberunningaqueryatthemoment.*/
OR(sess.session_idIN(SELECTDISTINCTblocking_session_idFROMsys.dm_exec_requestsWHEREblocking_session_id!
=0))
/*redundantduetotheuseofjoinhints,butaddedheretosuppresswarningmessage*/
OPTION(FORCEORDER)
SET@rowcount=@@ROWCOUNT
SET@queryduration=DATEDIFF(ms,@querystarttime,GETDATE())
IF@queryduration>@qrydurationwarnthreshold
PRINT'DebugPrint:
perfstatsqry1-'+CONVERT(varchar,@queryduration)+'ms,rowcount='+CONVERT(varchar,@rowcount)+CHAR(13)+CHAR(10)
IFNOTEXISTS(SELECT*FROM#tmp_requestsWHEREsession_id<>@@SPIDANDISNULL(host_name,'')!
=@appname)BEGIN
PRINT'Noactivequeries'
END
ELSEBEGIN
--Thereareactivequeries(otherthanthisone).
--Thisquerycouldbecollapsedintothequeryabove.Itisbrokenoutheretoavoidanexcessively
--largememorygrantduetopoorcardinalityestimates(seepreviousbugs--ultimatecauseisthe
--lackofgoodstatsformanyDMVs).
SET@querystarttime=GETDATE()
SELECT
IDENTITY(int,1,1)AStmprownum,
r.session_id,r.request_id,r.ecid,r.blocking_session_id,ISNULL(waits.blocking_exec_context_id,0)ASblocking_ecid,
r.task_state,r.wait_type,ISNULL(waits.wait_duration_ms,0)ASwait_duration_ms,r.wait_resource,
LEFT(ISNULL(waits.resource_description,''),140)ASresource_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(CASECOALESCE(reqtrans.transaction_type,sesstrans.transaction_type)
WHEN1THEN'1-Read/write'
WHEN2THEN'2-Readonly'
WHEN3THEN'3-System'
WHEN4THEN'4-Distributed'
ELSECONVERT(varchar(30),COALESCE(reqtrans.transaction_type,sesstrans.transaction_type))+'-UNKNOW
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MSSQL 配置 性能 监控 脚本 PerfStats
![提示](https://static.bingdoc.com/images/bang_tan.gif)