May 28, 2012 at 3:55 pm
Hello,
Does anyone have a saple SQL Script that can monitor a SPID/SPIDS and check if the DISKIO has stopped for 5 minutes and send an email if there is no DISKIO activity.
We have a large DataBase upgrade that runs 6 hours and we need to check to make sure there are no hangs or DISKIO issues or if the spid has finsihed.
Thanks
June 2, 2012 at 7:30 pm
Just poll sys.dm_exec_requests for the status, blocked by, and wait type columns.
As an example, here is a query I wrote to raise an alert in the application event log (then we set up a SCOM alert to watch for the event) if any queries were blocked for more than 5 seconds.
Use master;
Go
If Exists (Select 1 From information_schema.routines
Where routine_name = 'ops_AlertIfSPIDBlocked'
And routine_schema = 'dbo')
Drop Procedure dbo.ops_AlertIfSPIDBlocked
Go
Create Procedure dbo.ops_AlertIfSPIDBlocked
@threshold smallint = 5, -- Minimum seconds that a SPID should be blocked to be detected
@RaiseAlert bit = 0 -- 0 = Do not alert, return info about blocked SPID's, 1 = Raise alert
As
Declare @threshold_ms int,
@AlertMsg nvarchar(255)
Declare @SPIDBlocks Table (
BlockedSPID smallint null,
SPIDStarted datetime null,
SPIDStatus nvarchar(30) null,
Command nvarchar(16) null,
DatabaseID smallint null,
UserID int null,
WaitType nvarchar(60) null,
WaitTime int null,
WaitResource nvarchar(256) null,
LastWaitType nvarchar(60) null,
OpenTrans int null,
CPUTime int null,
TotalTime int null,
Reads bigint null,
Writes bigint null,
LogicalReads bigint null,
GrantedMemory int null,
BlockedQuery nvarchar(max) null,
BlockedQueryPlan XML null,
BlockingSPID smallint null,
BlockingSPIDStarted datetime null,
BlockingSPIDStatus nvarchar(30) null,
BlockingCommand nvarchar(16) null,
BlockingDatabaseID smallint null,
BlockingUserID int null,
BlockingOpenTrans int null,
BlockingCPUTime int null,
BlockingTotalTime int null,
BlockingReads bigint null,
BlockingWrites bigint null,
BlockingLogicalReads bigint null,
BlockingGrantedMemory int null,
BlockingQuery nvarchar(max) null,
BlockingQueryPlan XML null)
Set NoCount On
Set @threshold = 5
Set @threshold_ms = @threshold * 1000
Set @AlertMsg = 'Blocked SPIDs exceeding threshold of ' + Cast(@threshold as nvarchar) +
' seconds detected on server ' + quotename(@@ServerName)
Insert Into @SPIDBlocks
Select R.session_id,
R.start_time,
R.status,
R.command,
R.database_id,
R.user_id,
R.wait_type,
R.wait_time,
R.wait_resource,
R.last_wait_type,
R.open_transaction_count,
R.cpu_time,
R.total_elapsed_time,
R.reads,
R.writes,
R.logical_reads,
R.granted_query_memory,
BlockedSQLText = SubString(S.text, (R.statement_start_offset/2)+1,
((Case R.statement_end_offset
When -1 Then DataLength(S.text)
Else R.statement_end_offset
End - R.statement_start_offset)/2) + 1),
QP.query_plan,
R.blocking_session_id,
blockR.start_time,
blockR.status,
blockR.command,
blockR.database_id,
blockR.user_id,
blockR.open_transaction_count,
blockR.cpu_time,
blockR.total_elapsed_time,
blockR.reads,
blockR.writes,
blockR.logical_reads,
blockR.granted_query_memory,
BlockingSQLText = SubString(blockS.text, (blockR.statement_start_offset/2)+1,
((Case blockR.statement_end_offset
When -1 Then DataLength(blockS.text)
Else blockR.statement_end_offset
End - blockR.statement_start_offset)/2) + 1),
blockQP.query_plan
From sys.dm_exec_requests R
Cross Apply sys.dm_exec_sql_text(R.sql_handle) S
Cross Apply sys.dm_exec_query_plan(R.plan_handle) QP
Inner Join sys.dm_exec_requests blockR On blockR.session_id = R.blocking_session_id
Cross Apply sys.dm_exec_sql_text(blockR.sql_handle) blockS
Cross Apply sys.dm_exec_query_plan(blockR.plan_handle) blockQP
Where R.blocking_session_id is not null
And R.blocking_session_id <> 0
And R.wait_time >= @threshold_ms
If Exists (Select 1 From @SPIDBlocks)
Begin
If @RaiseAlert = 0
Begin
Select *
From @SPIDBlocks;
End
Else
Begin
Exec xp_logevent 50101, @AlertMsg, warning
End
End
Set NoCount Off
June 2, 2012 at 7:44 pm
Thanks I will try it. have you run into cases where the SPID was not blocked but had no DISKIO? We were concerned of a HANG condition
June 2, 2012 at 8:40 pm
Absolutely. It's key to look at the wait columns. Mainly, what it is waiting on and how long it has been waiting. The wait duration column will tell you how long it has been waiting with the current wait. You can also look at the statement offsets to see if the currently running query in a batch or procedure is changing.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply