Monday, September 19, 2016

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 :)

Wednesday, December 9, 2015

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