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 s

ON s.address = st.event_session_address

WHERE s.name = N'system_health'

AND st.target_name = N'ring_buffer'

) AS Data

CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)

ORDER BY CreationDate DESC

SELECT  CAST(event_data.value('(event/data/value)[1]',

                               'varchar(max)') AS XML) AS DeadlockGraph

FROM    ( SELECT    XEvent.query('.') AS event_data

          FROM      (    -- Cast the target_data to XML 

                      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 target_name = 'ring_buffer'

                    ) AS Data -- Split out the Event Nodes 

                    CROSS APPLY TargetData.nodes('RingBufferTarget/

                                     event[@name="xml_deadlock_report"]')

                    AS XEventData ( XEvent )

        ) AS tab ( event_data )

-- Retrieve from Extended Events in 2012

SELECT  XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph

FROM    ( SELECT    XEvent.query('.') AS XEvent

          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     s.name = 'system_health'

                                AND st.target_name = 'ring_buffer'

                    ) AS Data

                    CROSS APPLY TargetData.nodes

                  ('RingBufferTarget/event[@name="xml_deadlock_report"]')

                    AS XEventData ( XEvent )

        ) AS src;

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph

, XEvent.value('(/event/@timestamp)[1]','DATETIMEOFFSET') AS BeginTime

    FROM ( SELECT XEvent.query('.') AS XEvent

        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 s.name = 'system_health'

                AND st.target_name = 'ring_buffer' 

              ) AS Data 

              CROSS APPLY 

                 TargetData.nodes 

                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')

              AS XEventData ( XEvent ) 

      ) AS src ;

--死锁分析

DECLARE @SessionName SysName 

SELECT @SessionName = 'system_health'

/* 

SELECT  Session_Name = s.name, s.blocked_event_fire_time, s.dropped_buffer_count, s.dropped_event_count, s.pending_buffers

FROM sys.dm_xe_session_targets t

INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address

WHERE target_name = 'event_file'

--*/

IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN

DROP TABLE #Events

END

DECLARE @Target_File NVarChar(1000)

, @Target_Dir NVarChar(1000)

, @Target_File_WildCard NVarChar(1000)

SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')

FROM sys.dm_xe_session_targets t

INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address

WHERE s.name = @SessionName

AND t.target_name = 'event_file'

SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File))) 

SELECT @Target_File_WildCard = @Target_Dir + '\'  + @SessionName + '_*.xel'

--Keep this as a separate table because it's called twice in the next query.  You don't want this running twice.

SELECT DeadlockGraph = CAST(event_data AS XML)

, DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset)

INTO #Events

FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F

WHERE event_data like '<event name="xml_deadlock_report%'

;WITH Victims AS

(

SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)')

, e.DeadlockID 

FROM #Events e

CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims)

)

, DeadlockObjects AS

(

SELECT DISTINCT e.DeadlockID

, ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)')

FROM #Events e

CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources)

)

SELECT *

FROM

(

SELECT e.DeadlockID

, TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime')

, DeadlockGraph

, DeadlockObjects = substring((SELECT (', ' + o.ObjectName)

FROM DeadlockObjects o

WHERE o.DeadlockID = e.DeadlockID

ORDER BY o.ObjectName

FOR XML PATH ('')

), 3, 4000)

, Victim = CASE WHEN v.VictimID IS NOT NULL 

THEN 1 

ELSE 0 

END

, SPID = Deadlock.Process.value('@spid', 'int')

, ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)')

, LockMode = Deadlock.Process.value('@lockMode', 'char(1)')

, Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)')

, ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29)

WHEN 'SQLAgent - TSQL JobStep (Job '

THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67)

ELSE Deadlock.Process.value('@clientapp', 'varchar(100)')

END 

, HostName = Deadlock.Process.value('@hostname', 'varchar(20)')

, LoginName = Deadlock.Process.value('@loginname', 'varchar(20)')

, InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')

FROM #Events e

CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process)

LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)')

) X --In a subquery to make filtering easier (use column names, not XML parsing), no other reason

ORDER BY DeadlockID DESC

CREATE TABLE #errorlog (

            LogDate DATETIME 

            , ProcessInfo VARCHAR(100)

            , [Text] VARCHAR(MAX)

            );

DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);

INSERT INTO #errorlog EXEC sp_readerrorlog;

SELECT @tag = text

FROM #errorlog 

WHERE [Text] LIKE 'Logging%MSSQL\Log%';

DROP TABLE #errorlog;

SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);

SELECT 

  CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,

  switchoffset(CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime2'),'+08:00')

  AS Execution_Time

FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)

WHERE OBJECT_NAME like 'xml_deadlock_report';