Data Science Lab

-- Remove event session if it exists

IF EXISTS (SELECT 1 FROM [sys].[server_event_sessions]

WHERE [name] = 'InterestingPlanEvents')

BEGIN

DROP EVENT SESSION [InterestingPlanEvents] ON SERVER

END

GO

-- Define event session

CREATE EVENT SESSION [InterestingPlanEvents]

ON SERVER

ADD EVENT sqlserver.missing_column_statistics

(

ACTION(sqlserver.database_id,sqlserver.plan_handle,sqlserver.sql_text)

WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))

AND [sqlserver].[database_id]>(4))

),

ADD EVENT sqlserver.missing_join_predicate

(

ACTION(sqlserver.database_id,sqlserver.plan_handle,sqlserver.sql_text)

WHERE ([sqlserver].[is_system]=(0) AND [sqlserver].[database_id]>(4))

),

ADD EVENT sqlserver.plan_affecting_convert

(

ACTION(sqlserver.database_id,sqlserver.plan_handle,sqlserver.sql_text)

WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))

AND [sqlserver].[database_id]>(4))

),

ADD EVENT sqlserver.unmatched_filtered_indexes

(

ACTION(sqlserver.plan_handle,sqlserver.sql_text)

WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))

AND [sqlserver].[database_id]>(4))

)

ADD TARGET package0.event_file

(

SET filename=N'd:\SQL_Data\InterestingPlanEvents' /* change location if appropriate */

)

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,

TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)

GO

-- Start the event session

ALTER EVENT SESSION [InterestingPlanEvents] ON SERVER STATE=START;

GO

-- These queries assume a FRESH restore of AdventureWorks2014

ALTER DATABASE [AdventureWorks2012] SET AUTO_CREATE_STATISTICS OFF;

GO

USE [AdventureWorks2012];

GO

CREATE INDEX [NCI_SalesOrderHeader] ON [Sales].[SalesOrderHeader] (

[PurchaseOrderNumber], [CustomerID], [TotalDue], [DueDate]

)

WHERE [SubTotal] = 10000.00;

GO

/*

No join predicate

NOTE: We clear procedure here because the event ONLY fires for the *initial* compilation

*/

DBCC FREEPROCCACHE; /* Not for production use */

SELECT [h].[SalesOrderID], [d].[SalesOrderDetailID], [h].[CustomerID]

FROM [Sales].[SalesOrderDetail] [d],

[Sales].[SalesOrderHeader] [h]

WHERE [d].[ProductID] = 897;

GO

-- Columns with no statistics

SELECT [BusinessEntityID], [NationalIDNumber], [JobTitle], [HireDate], [ModifiedDate]

FROM [HumanResources].[Employee]

WHERE [HireDate] = '2013-01-01';

GO

-- Unmatched Index

DECLARE @Total MONEY = 10000.00;

SELECT [PurchaseOrderNumber], [CustomerID], [TotalDue], [DueDate]

FROM [Sales].[SalesOrderHeader]

WHERE [SubTotal] = @Total;

GO

-- Plan Affecting Convert

SELECT [BusinessEntityID], [NationalIDNumber], [JobTitle], [HireDate], [ModifiedDate]

FROM [HumanResources].[Employee]

WHERE [NationalIDNumber] = 345106466;

GO

ALTER EVENT SESSION [InterestingPlanEvents]

ON SERVER

STATE=STOP;

GO

select * from sys.dm_exec_query_plan(0x06000600B9B79001F036B96B0400000001000000000000000000000000000000000000000000000000000000)

DROP EVENT SESSION [InterestingPlanEvents]

ON SERVER;

GO

ALTER DATABASE [AdventureWorks2012] SET AUTO_CREATE_STATISTICS ON;

GO

DROP INDEX [NCI_SalesOrderHeader] ON [Sales].[SalesOrderHeader];

GO

CREATE TABLE TestTable (

c1 INT IDENTITY,

c2 CHAR (1000) DEFAULT 'a');

GO

INSERT INTO [TestTable] DEFAULT VALUES;

GO

-- Drop the session if it exists.

IF EXISTS (

SELECT * FROM sys.server_event_sessions

WHERE [name] = N'MonitorLog')

DROP EVENT SESSION [MonitorLog] ON SERVER

GO

-- Create the event session

CREATE EVENT SESSION [MonitorLog] ON SERVER

ADD EVENT [sqlserver].[file_write_completed],

ADD EVENT [sqlserver].[transaction_log]

ADD TARGET [package0].[ring_buffer]

WITH (MAX_MEMORY = 50MB, max_dispatch_latency = 1 seconds)

GO

-- Start the session

ALTER EVENT SESSION [MonitorLog] ON SERVER

STATE = START;

GO

CREATE EVENT SESSION [Backup progress] ON SERVER

ADD EVENT sqlserver.backup_restore_progress_trace

(

ACTION(package0.event_sequence)

-- to only capture backup operations:

--WHERE [operation_type] = 0

-- to only capture restore operations:

--WHERE [operation_type] = 1

)

ADD TARGET package0.event_file

(

SET filename = N'C:\SQL_Backup\BackupProgress.xel'

); -- default options are probably ok

GO

ALTER EVENT SESSION [Backup progress] ON SERVER STATE = START;

GO

create database SW_Test

go

SELECT s1.* INTO SW_Test.dbo.what

FROM sys.all_objects AS s1

CROSS JOIN sys.all_objects;

GO

BACKUP DATABASE SW_Test TO DISK = 'c:\SQL_Backup\SW_Test.bak'

WITH INIT, COMPRESSION, STATS = 30;

GO

DROP DATABASE SW_Test;

GO

RESTORE DATABASE SW_Test FROM DISK = 'c:\SQL_Backup\SW_Test.bak'

WITH REPLACE, RECOVERY;

;WITH x AS

(

SELECT ts,op,db,msg,es

FROM

(

SELECT

ts = x.value(N'(event/@timestamp)[1]', N'datetime2'),

op = x.value(N'(event/data[@name="operation_type"]/text)[1]', N'nvarchar(32)'),

db = x.value(N'(event/data[@name="database_name"])[1]', N'nvarchar(128)'),

msg = x.value(N'(event/data[@name="trace_message"])[1]', N'nvarchar(max)'),

es = x.value(N'(event/action[@name="event_sequence"])[1]', N'int')

FROM

(

SELECT x = CONVERT(XML, event_data)

FROM sys.fn_xe_file_target_read_file

(N'c:\SQL_Backup\BackupProgress*.xel', NULL, NULL, NULL)

) AS y

) AS x

WHERE op = N'Backup' -- N'Restore'

AND db = N'SW_Test'

AND ts > CONVERT(DATE, SYSDATETIME())

)

SELECT x.db, x.op, x.ts,

[Message] = x.msg,

Duration = COALESCE(DATEDIFF(MILLISECOND, x.ts,

LEAD(x.ts, 1) OVER(ORDER BY es)),0)

FROM x

ORDER BY es;