性能分析-常用性能分析查询脚本大全

WITH [Waits] 

AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],

          (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],

           signal_wait_time_ms / 1000.0 AS [SignalS],

           waiting_tasks_count AS [WaitCount],

           100.0 *  wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],

           ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]

    FROM sys.dm_os_wait_stats WITH (NOLOCK)

    WHERE [wait_type] NOT IN (

        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',

        N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',

        N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',

        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',

        N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',

        N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',

        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', 

        N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',

        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', 

        N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',

        N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',

        N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',

        N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',

        N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS',

        N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',

        N'QDS_ASYNC_QUEUE',

        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',

        N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',

        N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',

        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',

        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',

        N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',

        N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',

        N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',

        N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')

    AND waiting_tasks_count > 0)

SELECT

    MAX (W1.wait_type) AS [WaitType],

    CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],

    CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],

    CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],

    MAX (W1.WaitCount) AS [Wait Count],

    CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],

    CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],

    CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],

    CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.RowNum <= W1.RowNum

GROUP BY W1.RowNum

HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold

OPTION (RECOMPILE);

--查IO速度

SELECT DB_NAME(fs.database_id) AS [Database Name], CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],

CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],

CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms],

CONVERT(DECIMAL(18,2), mf.size/128.0) AS [File Size (MB)], mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads, 

fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls], fs.num_of_reads + fs.num_of_writes AS [total_io],

io_stall_queued_read_ms AS [Resource Governor Total Read IO Latency (ms)], io_stall_queued_write_ms AS [Resource Governor Total Write IO Latency (ms)] 

FROM sys.dm_io_virtual_file_stats(null,null) AS fs

INNER JOIN sys.master_files AS mf WITH (NOLOCK)

ON fs.database_id = mf.database_id

AND fs.[file_id] = mf.[file_id]

ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);

--查阻塞

SELECT t1.resource_type AS [锁类型], DB_NAME(resource_database_id) AS [数据库名],

t1.resource_associated_entity_id AS [阻塞资源对象],t1.resource_description as [资源描述信息], t1.request_mode AS [请求的锁], 

t1.request_session_id AS [等待会话], t2.wait_duration_ms AS [等待时间],       

(SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK)                      

CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) 

    WHERE r.session_id = t1.request_session_id

) AS [等待会话执行的批SQL],

(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2, 

(CASE WHEN r.statement_end_offset = -1 

THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 

ELSE r.statement_end_offset END )/2) 

FROM sys.dm_exec_requests AS r WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt

WHERE r.session_id = t1.request_session_id

) AS [等待会话执行的SQL],                    

t2.blocking_session_id AS [阻塞会话],                                        

(SELECT [text] FROM sys.sysprocesses AS p                                       

CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) 

WHERE p.spid = t2.blocking_session_id

) AS [阻塞会话执行的批SQL]

FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)

INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)

ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);

--查IO

SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text], 50) AS [Short Query Text],

 qs.total_logical_reads AS [Total Logical Reads],

 qs.min_logical_reads AS [Min Logical Reads],

 qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],

 qs.max_logical_reads AS [Max Logical Reads],   

 qs.min_worker_time AS [Min Worker Time],

 qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 

 qs.max_worker_time AS [Max Worker Time], 

 qs.min_elapsed_time AS [Min Elapsed Time], 

 qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], 

 qs.max_elapsed_time AS [Max Elapsed Time],

 qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]

 ,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel

 FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 

 CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 

 ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);

--查CPU

SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text], 50) AS [Short Query Text],  

qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],

qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 

qs.max_worker_time AS [Max Worker Time], 

qs.min_elapsed_time AS [Min Elapsed Time], 

qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], 

qs.max_elapsed_time AS [Max Elapsed Time],

qs.min_logical_reads AS [Min Logical Reads],

qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],

qs.max_logical_reads AS [Max Logical Reads], 

qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]

 ,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

DECLARE @sessionsCPU TABLE 

(

session_id INT NOT NULL,

cpu_time BIGINT NULL ,

initialCPUFlag BIT NOT NULL

)

INSERT INTO @sessionsCPU

SELECT 

session_id

,sum(cpu_time) as cpu_time

,1 as initialCPUFlag

FROM sys.dm_exec_requests

WHERE session_id > 20

GROUP BY session_id

WAITFOR DELAY '0:00:10' -- 等待10秒

INSERT INTO @sessionsCPU

SELECT

session_id

,sum(cpu_time) as cpu_time

,0 as initialCPUFlag

FROM sys.dm_exec_requests

WHERE session_id > 20

GROUP BY session_id;

WITH total AS

(

select

initialCPUFlag

,sum(cpu_time) as cpu_time

FROM @sessionsCPU

GROUP BY initialCPUFlag

), 

delta AS

(

SELECT 

s.cpu_time - f.cpu_time as total_cpu

FROM total f

CROSS JOIN total s

WHERE f.initialCPUFlag = 1

AND s.initialCPUFlag = 0

)  

SELECT

i.session_id

,convert(numeric(5,2), (100. * (((i.cpu_time - l.cpu_time) * 1.) / (d.total_cpu * 1.)))) AS percentCPU

,convert(char(8), getdate() - r.start_time, 108) AS run_duration

,substring( st.text, 

(r.statement_start_offset/2)+1,

((CASE r.statement_end_offset

WHEN -1 THEN datalength(st.text)

ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1)

AS statement_text

,st.text AS full_query

,pt.query_plan

,s.login_name

,s.host_name

,s.program_name

FROM @sessionsCPU i

INNER JOIN @sessionsCPU l

ON i.session_id = l.session_id

AND i.initialCPUFlag = 0 AND l.initialCPUFlag = 1

INNER JOIN sys.dm_exec_requests r

ON i.session_id = r.session_id

INNER JOIN sys.dm_exec_sessions s

ON i.session_id = s.session_id

CROSS JOIN delta d

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st

cross apply sys.dm_exec_query_plan(r.plan_handle) as pt

WHERE d.total_cpu <> 0

ORDER BY percentCPU DESC;

--获取死锁信息 sql server 2008 r2

SELECT  XEventData.XEvent.value('@timestamp', 'datetime2(3)'),

cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml)

FROM (SELECT CAST (target_data AS XML) AS TargetData

    FROM sys.dm_xe_session_targets st

    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address

    WHERE [name] = 'system_health' AND st.target_name = 'ring_buffer') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

    WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

--or

SELECT event.c.value('@timestamp', 'datetime2(3)'), 

       datavalue.c.value('(./text())[1]', 'nvarchar(max)') AS DeadlockGraph

FROM

(

SELECT CAST(target_data AS XML) AS TargetData

FROM sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address

WHERE name = 'system_health'

) AS DATA

CROSS APPLY TargetData.nodes('/RingBufferTarget/event') AS event(c)

CROSS APPLY event.c.nodes ('data/value') AS datavalue(c)

WHERE event.c.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

--or

SELECT

xed.value('@timestamp', 'datetime2(3)') as CreationDate,

xed.query('.') AS XEvent

FROM

(

SELECT CAST([target_data] AS XML) AS TargetData

FROM sys.dm_xe_session_targets AS st

INNER JOIN sys.dm_xe_sessions AS