How to setup email notification when blocking occurs?
Blocking is one of the most common reasons responsible for causing performance issues in database server. Many times application teams come back to database administrators with a concern that their application is running slow and some times it’s getting hanged while querying to database. And when you connect to database server you will find that some processes are causing blocking. This in turn is causing performance issue and as a chain reaction application is not running BAU.
Its possible that this blocking started occurring may be couple of hours earlier (by some bad query) and by the time you connected to database server that query has caused am chain of processes being blocked by others.
So, how about writing and implementing some scripts so that database administrators get notified as soon as blocking occurs in the database. By getting notified, DBA can always connect to database server and try to resolve the issue. This can avoid the possible impact on applications.
Concept used for this notification setup
There may be many ways to get notified about blocking on the database server. You can either schedule a job to run every few minutes and check for blocking but may be possible that when this job was running their was no blocking and immediate after this job completes the blocking started. So, this may not be a good idea to schedule a job to run after specific time period.
The concept used here in this article is that we will first create a SQL server performance alert. This alert will get triggered when ever there is some process with wait time more than 10seconds (you can change it as per your requirement). Then the alert will trigger a SQL Server job which will check for blocking on the server. If the job finds any blocking it will send email to the database administrators with the spid details and the commands running against each process. So, the DBA will get notified in 10-12sec if there is any blocking on the server (provided email is sent immediately and not queued).
Setting up email notification
We will setup this notification in two parts. First we will create a job and then we will create a SQL Alert. Before we start implementing this notification, please make sure you can email from your database server.
Create a new job ‘Blocking Notification’ which will perform the below actions:-
1) Check if there is any blocking in the database server by running below query
Select * from sysprocesses where blocked>0
If the job finds any blocked process then move to next step else the job will complete successfully without sending any notification email.
2) In this step just check the spid’s which are blocked by other processes into a temporary table.
Select spid into #temp_blockedspid from sysprocesses where blocked > 0
Now, you can check the SQL query running under each blocked spid by running below command:
In the output of above command you will get the sql query running under the column ‘EventInfo’. DBCC INPUTBUFFER cannot show the full SQL text incase the query is very long. It shows only first few characters of the SQL query.
In SQL Server 2005 and higher editions, you can also use of function fn_get_sql to get the exact complete query running against a spid. The below query can be use to check the SQL query running against a spid id 55
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
SELECT * FROM ::fn_get_sql(@Handle)
Here you may have to make use of cursors to get the SQL query running against every spid recorded in temporary table.
Just enable the output file for this step and store the spid and query details in the output file.
3) In this step just make use of SQL mail to send email notification to the respective people. Also attach the output file of step2 in the email.
Now we will move to the second part of this notification step and that is to create Alert. Below are the steps to be followed:-
1) Create SQL Server Alert
To create a SQL Server alert navigate to Management -> SQL Server Agent -> Alert. Then click on New Alert.
2) Now the new window will open. Here give name to the alert say ‘Blocking’
Then against the Type of Alert we will select ‘SQL Server performance condition alert’ and make sure it is enabled (check box on right).
3) Now we have to select the severity, we have to select the Performance condition alert definition as shown in the screenshot below.
We will select Locks against object and the performance counter we are looking at is Average wait time (in ms). We will also select if the average wait time rises above 10000ms i.e. 10sec, this alert will be triggered.
4) Once the above settings are complete click on response tab. Here click on execute Job and select the SQL job which will check the blocking (created earlier) and notify DBA’s and click OK.
Now we have setup the notification Job and alert. So, next time when ever blocking occurs on the database server a notification email will be sent with spid and other details. This will increase the response time of DBA to act on any blocking issues and will avoid any possible impact on application.