Moving on with Extended Events

Lovely tool everyone of us were fond of, SQL Server Profiler, is announced to be retired. I was curious to find what it is like to do the same thing, one of the most common scenarios to capture what parameters are being  passed to a Stored Procedure and to capture the resource consumption.

It was easier than expected.

Below are the simple steps for you to capture rpc_completed (I am sure you or google knows this) data.

CREATE EVENT SESSION [CatchLive_Executions] ON SERVER

ADD EVENT sqlserver.rpc_completed(

ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)

WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0))))

ADD TARGET package0.ring_buffer

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)

GO

Change the database_id accordingly or get rid of it.

--To Start Monitoring Execute Below

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

-- WAITFOR DELAY @pMonitoringTime;

--Think about having this as a proc to accept the wait time as a parameter..

--When you are done Monitoring Execute Below

SELECT CAST(target_data as xml) AS targetdata

INTO #capture_live_data

FROM sys.dm_xe_session_targets xet

JOIN sys.dm_xe_sessions xes

ON xes.address = xet.event_session_address

WHERE xes.name = 'CatchLive_Executions'

 AND xet.target_name = 'ring_buffer';

ALTER EVENT SESSION [CatchLive_Executions] ON SERVER STATE=STOP;

--To View the captured data Execute below

SELECT xed.event_data.value('(@timestamp)[1]', 'datetime2') AS [timestamp],

 xed.event_data.value('(action[@name="database_name"]/value)[1]', 'varchar(25)') AS database_name,

 xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(25)') AS username,

 xed.event_data.value('(action[@name="session_id"]/value)[1]', 'varchar(25)') AS session_id,

 xed.event_data.value('(data[@name="object_name"]/value)[1]', 'varchar(100)') AS object_name,

 xed.event_data.value('(data[@name="statement"]/value)[1]', 'varchar(256)') AS statement,

 xed.event_data.value('(data[@name="duration"]/value)[1]', 'int') AS duration,

 xed.event_data.value('(data[@name="cpu_time"]/value)[1]', 'int') AS cpu_time,

 xed.event_data.value('(data[@name="physical_reads"]/value)[1]', 'int') AS physical_reads,

 xed.event_data.value('(data[@name="logical_reads"]/value)[1]', 'int') AS logical_reads,

 xed.event_data.value('(data[@name="writes"]/value)[1]', 'int') AS writes

FROM #capture_live_data

 CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed (event_data)

WHERE xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(25)') IS NOT NULL

ORDER BY timestamp desc

--Clean up

DROP TABLE #capture_live_data;

Change the queries as required..

Simple Enough right :)

How many plans will SQLServer cache for the same query :)

Hey everyone,

Check out this interesting piece in relevance to Adhoc query caching.

Howmany plans you think will sql server cache for below queries.

SELECT * FROM Production.Product where name ='Adjustable Race';
GO
SELECT * FROM Production.Product where Name ='Adjustable Race';
GO
select * from Production.Product where name ='Adjustable Race';
GO
select * from Production.Product where name ='Adjustable Race';
GO
--here we go
select * from Production.Product where name ='Adjustable Race';
GO

Well, you can verify your answer by asking the DMVs as below.

select usecounts,cacheobjtype,objtype,text from
sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(plan_handle)
where text not like '%dm_exec_cached_plans%'
and cacheobjtype='Compiled Plan' and objtype='adhoc'

What is the value you have for below query? and what is the relationship?


select * from sys.configurations
where name ='optimize for ad hoc workloads'

Try it on your own(ofcourse, in your own instance :) ) and dig-in to understand the adhoc query caching behavior in SQL Server.

EXEC sp_configure 'show advanced option', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

What the hell happened to other plans ??

Cheers!!

Source: Microsoft sql server 2012 internals by kalen delaney