The following extended events script has proven to be useful to automate the collection of memory dumps when spinlock contention becomes significant. In some cases memory dumps will be required to perform a complete diagnosis of the problem or will be requested by Microsoft support teams to perform in depth analysis. In SQL Server 2008 there is a limit of 16 frames in callstacks captured by the bucketizer, which may not be deep enough to determine exactly where in the engine the callstack is being entered from. This is improved in the next version of SQL Server codename ‘Denali’ by increasing the number of frames in callstacks captured by the bucketizer to 32.
SQL Query for Capturing Memory Dumps
The following SQL script can be used to automate the process of capturing memory dumps to help analyze spinlock contention:
/*
This script is provided "AS IS" with no warranties, and confers no rights.
Use: This procedure will monitor for spinlocks with a high number of backoff events
over a defined time period which would indicate that there is likely significant
spin lock contention.
Modify the variables noted below before running.
Requires:
xp_cmdshell to be enabled
sp_configure 'xp_cmd', 1
go
reconfigure
go
*********************************************************************************************************/
use tempdb
go
if object_id('sp_xevent_dump_on_backoffs') is not null
drop proc sp_xevent_dump_on_backoffs
go
create proc sp_xevent_dump_on_backoffs
(
@sqldumper_path nvarchar(max) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"'
,@dump_threshold int = 500 --capture mini dump when the slot count for the top bucket exceeds this
,@total_delay_time_seconds int = 60 --poll for 60 seconds
,@PID int = 0
,@output_path nvarchar(max) = 'c:\'
,@dump_captured_flag int = 0 OUTPUT
)
as
/*
--Find the spinlock types
select map_value, map_key, name from sys.dm_xe_map_values
where name = 'spinlock_types'
order by map_value asc
--Example: Get the type value for any given spinlock type
select map_value, map_key, name from sys.dm_xe_map_values
where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')
*/
if exists (select * from sys.dm_xe_session_targets xst
inner join sys.dm_xe_sessions xs on (xst.event_session_address = xs.address)
where xs.name = 'spinlock_backoff_with_dump')
drop event session spinlock_backoff_with_dump on server
create event session spinlock_backoff_with_dump on server
add event sqlos.spinlock_backoff (action (package0.callstack)
where
type = 61 --LOCK_HASH
--or type = 144 --SOS_CACHESTORE
--or type = 8 --MUTEX
--or type = 53 --LOGCACHE_ACCESS
--or type = 41 --LOGFLUSHQ
--or type = 25 --SQL_MGR
--or type = 39 --XDESMGR
)
add target package0.asynchronous_bucketizer (
set filtering_event_name='sqlos.spinlock_backoff',
source_type=1, source='package0.callstack')
with (MAX_MEMORY=50MB, MEMORY_PARTITION_MODE = PER_NODE)
alter event session spinlock_backoff_with_dump on server state=start
declare @instance_name nvarchar(max) = @@SERVICENAME
declare @loop_count int = 1
declare @xml_result xml
declare @slot_count bigint
declare @xp_cmdshell nvarchar(max) = null
--start polling for the backoffs
print 'Polling for: ' + convert(varchar(32), @total_delay_time_seconds) + ' seconds'
while (@loop_count < CAST (@total_delay_time_seconds/1 as int))
begin
waitfor delay '00:00:01'
--get the xml from the bucketizer for the session
select @xml_result= CAST(target_data as xml)
from sys.dm_xe_session_targets xst
inner join sys.dm_xe_sessions xs on (xst.event_session_address = xs.address)
where xs.name = 'spinlock_backoff_with_dump'
--get the highest slot count from the bucketizer
select @slot_count = @xml_result.value(N'(//Slot/@count)[1]', 'int')
--if the slot count is higher than the threshold in the one minute period
--dump the process and clean up session
if (@slot_count > @dump_threshold)
begin
print 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(nvarchar(max), @PID) + ' 0 0x800 0 c:\ '''
select @xp_cmdshell = 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(nvarchar(max), @PID) + ' 0 0x800 0 ' + @output_path + ' '''
exec sp_executesql @xp_cmdshell
print 'loop count: ' + convert (varchar(128), @loop_count)
print 'slot count: ' + convert (varchar(128), @slot_count)
set @dump_captured_flag = 1
break
end
--otherwise loop
set @loop_count = @loop_count + 1
end
--see what was collected then clean up
DBCC traceon (3656, -1)
select event_session_address, target_name, execution_count, cast (target_data as XML)
from sys.dm_xe_session_targets xst
inner join sys.dm_xe_sessions xs on (xst.event_session_address = xs.address)
where xs.name = 'spinlock_backoff_with_dump'
alter event session spinlock_backoff_with_dump on server state=stop
drop event session spinlock_backoff_with_dump on server
go
/* CAPTURE THE DUMPS
******************************************************************/
--Example: This will run continuously until a dump is created.
declare @sqldumper_path nvarchar(max) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"'
declare @dump_threshold int = 300 --capture mini dump when the slot count for the top bucket exceeds this
declare @total_delay_time_seconds int = 60 --poll for 60 seconds
declare @PID int = 0
declare @flag tinyint = 0
declare @dump_count tinyint = 0
declare @max_dumps tinyint = 3 --stop after collecting this many dumps
declare @output_path nvarchar(max) = 'c:\' --no spaces in the path please :)
--Get the process id for sql server
declare @error_log table (LogDate datetime,
ProcessInfo varchar(255),
Text varchar(max)
)
insert into @error_log
exec ('xp_readerrorlog 0, 1, ''Server Process ID''')
select @PID = convert(int, (REPLACE(REPLACE(Text, 'Server Process ID is ', ''), '.', '')))
from @error_log where Text like ('Server Process ID is%')
print 'SQL Server PID: ' + convert (varchar(6), @PID)
--Loop to monitor the spinlocks and capture dumps. while (@dump_count < @max_dumps)
begin
exec sp_xevent_dump_on_backoffs @sqldumper_path = @sqldumper_path,
@dump_threshold = @dump_threshold,
@total_delay_time_seconds = @total_delay_time_seconds,
@PID = @PID,
@output_path = @output_path,
@dump_captured_flag = @flag OUTPUT
if (@flag > 0)
set @dump_count=@dump_count + 1
print 'Dump Count: ' + convert(varchar(2), @dump_count)
waitfor delay '00:00:02'
end
No comments:
Post a Comment