
-- Correlate SQL and Windows trace
-- You can postprocess this with XPerf

CREATE EVENT SESSION IOActivity
ON SERVER
   ADD EVENT sqlos.async_io_requested,
   ADD EVENT sqlos.async_io_completed,
   ADD EVENT sqlserver.file_read,
   ADD EVENT sqlserver.file_read_completed,
   ADD EVENT sqlserver.file_write_completed,
   ADD EVENT sqlserver.file_written
   ADD TARGET package0.etw_classic_sync_target
    (SET default_etw_session_logfile_path = N'c:\temp\sqletw.etl')
GO

ALTER EVENT SESSION IOActivity ON SERVER STATE = start;
GO

select * from sys.dm_xe_sessions;
select * from sys.dm_xe_session_events;
GO

-- add more events to a running session
ALTER EVENT SESSION IOActivity ON SERVER
   ADD EVENT sqlserver.database_transaction_begin,
   ADD EVENT sqlserver.database_transaction_end,
   ADD EVENT sqlserver.checkpoint_begin,
   ADD EVENT sqlserver.checkpoint_end
GO

select * from sys.dm_xe_sessions;
select * from sys.dm_xe_session_events;
GO

!!logman start "NT Kernel Logger" -p  "Windows Kernel Trace" (disk,file) -ets -o c:\temp\trace.etl -bs 1024 -ct system

use tempdb
go

drop table bigtable
go

CREATE TABLE bigtable (
 id int identity primary key,
 col1 char(8000)
)
go

-- add 100 rows
declare @x int = 0;
while @x < 1000
  begin 
    insert bigtable values ('vf nifninivnidnvidnmcdomdcd');
    set @x += 1;
  end
GO

BEGIN TRAN 
  SELECT top 1 * FROM bigtable
COMMIT
GO
CHECKPOINT
GO

-- do decision support query
use AdventureWorksDW
go
select ProductAlternateKey, CalendarYear, SUM(SalesAmount)
from FactInternetSales fact
join DimTime
  on fact.OrderDateKey = TimeKey
join DimProduct
  on DimProduct.ProductKey = fact.ProductKey
where CalendarYear BETWEEN 2003 AND 2004
  and ProductAlternateKey LIKE 'BK%'
group by ProductAlternateKey, CalendarYear
go

-- then shut everything down
!!logman stop "NT Kernel Logger" -ets
!!logman stop XE_DEFAULT_ETW_SESSION -ets

-- not sure if this is redundant
ALTER EVENT SESSION IOActivity ON SERVER STATE = stop;
GO

DROP EVENT SESSION IOActivity ON SERVER
GO

!!C:\utilities\xperf_4.1.1\xperf -merge c:\temp\sqletw.etl c:\temp\sql2.etl
!!C:\utilities\xperf_4.1.1\xperf -merge c:\temp\trace.etl c:\temp\sql2.etl c:\temp\out.etl