Blocking in SQL Server can degrade performance and lead to application slowdowns. Identifying and addressing long-running blocking queries is crucial for database administrators. In this post, we’ll create an automated job that runs every 10 minutes to detect and alert on blocking queries that persist for more than 5 minutes.
SET
NOCOUNT ON
DECLARE
@xml nvarchar(max)
SELECT
@xml = Cast((SELECT @@SERVICENAME AS 'td','',b.session_id AS 'td',
'',
(b.wait_duration_ms/1000)/60 AS 'td',
'',
b.wait_type AS 'td',
'',
b.blocking_session_id AS
'td',
'',
t.text AS 'td'
FROM
sys.dm_os_waiting_tasks b inner
join sys.dm_exec_requests r on r.session_id= b.session_id
OUTER
APPLY
sys.dm_exec_sql_text(sql_handle) t
WHERE
b.blocking_session_id <>
0 and b.wait_duration_ms>300000
FOR
xml path('tr'), elements) AS NVARCHAR(max))
Declare
@body nvarchar(max)
SET
@body =
'<html>
<head>
<style>
table,
th, td
{
border:
1px solid black;
border-collapse:
collapse;
text-align:
center;
}
</style>
</head>
<body>
<H2>
Blocking
queries
</H2>
<table>
<tr>
<th>Instance
Name</th><th> Blocked Session ID </th> <th> Wating in
minutes </th> <th> Wait type</th>
<th>
Blocking Session ID</th><th>Query waiting to execute</th>
</tr>'
SET
@body = @body +
@xml + '
</table>
</body>
</html>'
if(@xml is not null)
BEGIN
EXEC
msdb.dbo.Sp_send_dbmail
@profile_name
= 'sqlmail',
@body = @body,
@body_format
='html',
@recipients
= 'bajeyudu@sqldbanow.com',
@copy_recipients
='ajay@sqldbanow.com', -- list of Email
recipients
@subject = 'Blocking queries
Alert-SQLDBANOWDB01';
END
SET NOCOUNT OFF
No comments:
Post a Comment