SQL Server Extended Events

DROP EVENT SESSION (Transact-SQL)

Drops an event session.

SQL> DROP EVENT SESSION [Collect_Deadlocks_New] ON SERVER

View all the event session that exists in SQL Server

SQL> select * from sys.server_event_sessions

Create a Dead Lock Ex. session in SQL Server 2008

use [master];

CREATE EVENT SESSION [Collect_Deadlocks_New] ON SERVER

    ADD EVENT sqlserver.xml_deadlock_report(

        ACTION (

            package0.collect_system_time,

            sqlos.task_time,

            sqlserver.client_app_name,

            sqlserver.client_hostname,

            sqlserver.database_id,

            sqlserver.is_system,

            sqlserver.username

              )

                                            )

    ADD TARGET package0.asynchronous_file_target(

        SET filename           = N'C:\SQL\xE\Collect-Deadlocks_New\Collect-Deadlocks.xel',

            max_file_size      = (512),

            max_rollover_files = (3)

                                                  )

WITH (

     MAX_MEMORY            = 4096 KB,

     EVENT_RETENTION_MODE  = ALLOW_SINGLE_EVENT_LOSS,

     MAX_DISPATCH_LATENCY  = 20 SECONDS,

     MAX_EVENT_SIZE        = 0 KB,

     MEMORY_PARTITION_MODE = NONE,

     TRACK_CAUSALITY       = OFF,

     STARTUP_STATE         = ON

);

Create a Dead Lock Ex. session in SQL Server 2012 or higher

use [master];

CREATE EVENT SESSION [Collect-Deadlocks] ON SERVER

    ADD EVENT sqlserver.xml_deadlock_report(

        ACTION (

            package0.collect_system_time,

            sqlos.task_time,

            sqlserver.client_app_name,

            sqlserver.client_hostname,

            sqlserver.database_id,

            sqlserver.is_system,

            sqlserver.username

        )

    )

    ADD TARGET package0.event_file(

        SET filename           = N'C:\Collect-Deadlocks.xel',

            max_file_size      = (512),

            max_rollover_files = (3)

    )

WITH (

     MAX_MEMORY            = 4096 KB,

     EVENT_RETENTION_MODE  = ALLOW_SINGLE_EVENT_LOSS,

     MAX_DISPATCH_LATENCY  = 20 SECONDS,

     MAX_EVENT_SIZE        = 0 KB,

     MEMORY_PARTITION_MODE = NONE,

     TRACK_CAUSALITY       = OFF,

     STARTUP_STATE         = ON

);

sys.fn_xe_file_target_read_file

Reads files that are created by the Extended Events asynchronous file target. One event, in XML format, is returned per row.  Dead lock info we can get from event data column.

Warning

SQL Server 2008 and SQL Server 2008 R2 accept trace results generated in XEL and XEM format. SQL Server 2012 (11.x) Extended Events only support trace results in XEL format. We recommend that you use SQL Server Management Studio to read trace results in XEL format.

Syntax:

sys.fn_xe_file_target_read_file ( path, mdpath, initial_file_name, initial_offset ) 

Example:

In SQL 2008

SELECT * FROM sys.fn_xe_file_target_read_file('C:\SQL\xE\Collect-Deadlocks_New\Collect-Deadlocks_0_132216503855490000.xel',

 'C:\SQL\xE\Collect-Deadlocks_New\Collect-Deadlocks_0_132216503855500000.xem', null, null);

In SQL 2012 +

SELECT * FROM sys.fn_xe_file_target_read_file('C:\Collect-Deadlocks_0_132216492977010000.xel', null, null, null);

Start and Stop a Session

ALTER EVENT SESSION <YOUR_XE_SESSION_NAME>

ON SERVER 

STATE = START;

GO 

ALTER EVENT SESSION <YOUR_XE_SESSION_NAME>

ON SERVER 

STATE = STOP;

GO 

Session Status check

SELECT rs.*

FROM sys.dm_xe_sessions RS

RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name

WHERE es.name like '<YOUR_XE_SESSION_NAME>'

SQL Server Black Box Analysis

"SQL Server Black Box Analysis" script give you a MRI report of SQL Server. Haha ha...










Script...

create table #ServerStats (create_time datetime,component_type sysname,component_name sysname, state int,state_desc sysname, data xml)

insert into #ServerStats execute sp_server_diagnostics

-- Overview

-- Note: this events component will always show a status of unknown

select create_time as "Date",

       component_name as "Component",

  state_desc as "Status"

  from #ServerStats

-- System

select  'System' as "System",

data.value('(/system/@systemCpuUtilization)[1]','bigint') as "System CPU",

        data.value('(/system/@sqlCpuUtilization)[1]','bigint') as "SQL CPU",

        data.value('(/system/@nonYieldingTasksReported)[1]','bigint') as "Non-yielding Tasks",

data.value('(/system/@pageFaults)[1]','bigint') as "Page Faults",

data.value('(/system/@latchWarnings)[1]','bigint') as "LatchWarnings"

  from #ServerStats

 where component_name like 'system'

-- Memory

select 'Memory' as "Memory",

       data.value('(/resource/memoryReport/entry[@description="Working Set"]/@value)[1]','float')/1024/1024 "Memory Used by SQL Server (MB)",

       data.value('(/resource/memoryReport/entry[@description="Available Physical Memory"]/@value)[1]','float')/1024/1024 "Physical Memory Available (MB)",

  data.value('(/resource/@lastNotification)[1]','varchar(100)') as "Last Notification",

       data.value('(/resource/@outOfMemoryExceptions)[1]','bigint') as "Out of Memory Exceptions"

  from #ServerStats

 where component_name like 'resource'

-- Nonpreemptive waits by duration

select 'Non Preemptive by duration' as "Wait",

  tbl.evt.value('(@waitType)','varchar(100)') as "Wait Type",

    tbl.evt.value('(@waits)','bigint') as "Waits",

  tbl.evt.value('(@averageWaitTime)','bigint') as "Avg Wait Time",

  tbl.evt.value('(@maxWaitTime)','bigint') as "Max Wait Time"

from #ServerStats

    CROSS APPLY data.nodes('/queryProcessing/topWaits/nonPreemptive/byDuration/wait') AS tbl(evt)

 where component_name like 'query_processing'

-- Preemptive waits by duration

select 'Preemptive by duration' as "Wait",

  tbl.evt.value('(@waitType)','varchar(100)') as "Wait Type",

    tbl.evt.value('(@waits)','bigint') as "Waits",

  tbl.evt.value('(@averageWaitTime)','bigint') as "Avg Wait Time",

  tbl.evt.value('(@maxWaitTime)','bigint') as "Max Wait Time"

from #ServerStats

    CROSS APPLY data.nodes('/queryProcessing/topWaits/preemptive/byDuration/wait') AS tbl(evt)

 where component_name like 'query_processing'

-- CPU intensive queries

select 'CPU Intensive Queries' as "CPU Intensive Queries",

  tbl.evt.value('(@sessionId)','bigint') as "Session ID",

  tbl.evt.value('(@command)','varchar(100)') as "Command",

  tbl.evt.value('(@cpuUtilization)','bigint') as "CPU",

  tbl.evt.value('(@cpuTimeMs)','bigint') as "CPU Time (ms)"

  from #ServerStats

    CROSS APPLY data.nodes('/queryProcessing/cpuIntensiveRequests/request') AS tbl(evt)

 where component_name like 'query_processing'

-- Blocked Process Reports

select 'Blocked Process Report' as "Blocked Process Report",

       tbl.evt.query('.') as "Report XML"

from #ServerStats

    CROSS APPLY data.nodes('/queryProcessing/blockingTasks/blocked-process-report') AS tbl(evt)

 where component_name like 'query_processing'

-- IO report

select 'IO Subsystem' as "IO Subsystem",

       data.value('(/ioSubsystem/@ioLatchTimeouts)[1]','bigint') as "Latch Timeouts",

  data.value('(/ioSubsystem/@totalLongIos)[1]','bigint') as "Total Long IOs"

from #ServerStats

 where component_name like 'io_subsystem'

-- Event information

select tbl.evt.value('(@name)','varchar(100)') as "Event Name",

       tbl.evt.value('(@package)','varchar(100)') as "Package",

       tbl.evt.value('(@timestamp)','datetime') as "Event Time",

       tbl.evt.query('.') as "Event Data"

 from #ServerStats

  CROSS APPLY data.nodes('/events/session/RingBufferTarget/event') AS tbl(evt)

where component_name like 'events'

drop table #ServerStats

      

Why are spids in suspended state.

SELECT es.session_id AS session_id

,COALESCE(es.original_login_name, '') AS login_name

,COALESCE(es.host_name,'') AS hostname

,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch

,es.status

,COALESCE(er.blocking_session_id,0) AS blocked_by

,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype

,COALESCE(er.wait_time,0) AS waittime

,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype

,COALESCE(er.wait_resource,'') AS waitresource

,coalesce(db_name(er.database_id),'No Info') as dbid

,COALESCE(er.command,'AWAITING COMMAND') AS cmd

,sql_text=st.text

,transaction_isolation =

    CASE es.transaction_isolation_level

    WHEN 0 THEN 'Unspecified'

    WHEN 1 THEN 'Read Uncommitted'

    WHEN 2 THEN 'Read Committed'

    WHEN 3 THEN 'Repeatable'

    WHEN 4 THEN 'Serializable'

    WHEN 5 THEN 'Snapshot'

END

,COALESCE(es.cpu_time,0)

    + COALESCE(er.cpu_time,0) AS cpu

,COALESCE(es.reads,0)

    + COALESCE(es.writes,0)

    + COALESCE(er.reads,0)

    + COALESCE(er.writes,0) AS physical_io

,COALESCE(er.open_transaction_count,-1) AS open_tran

,COALESCE(es.program_name,'') AS program_name

,es.login_time

FROM sys.dm_exec_sessions es

    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id

    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id

    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid

    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id

    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address

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

where es.is_user_process = 1

  and es.session_id <> @@spid

ORDER BY es.session_id