It was only in due time “Erik please find a third party tool that can monitor our database for performance issues and blocking.” The time is now. So in my option I have about three options.
- Create a custom script to monitor for blocking
- Run a SQL Profiler trace during high usage hours
- Research SQL Monitor (RedGate Tool) to monitor my database. This would be getting a sharper more expensive ax.
I have made numerous suggestions on how to speed up the databases. However, keep in mind with vendor supported databases, those databases are their babies. Asking them to change something, even though they are getting paid by your company – is like asking a mother to cut her daughters long blond beautiful hair to rival Sinead O’Connor. There has been weekly calls with the vendor to discuss how we can improve performance. As of right now they are focusing their time and energy to prove it’s a network/end user PC issue. Since I believe this is a waste of time I know they will come to the DBA to say, how can we improve performance. Therefore, my new task could not come at a better time. My new goal is to find way to kill two birds with one stone. Let the research being.
First, a script that will monitor for blocking:
NOTE: Before you do this you will need to Configure Database Mail on the server.
Add this script to a SQL job and have the job run how ever often you feel as necessary.
DECLARE @Email varchar(max)
select @Email= ‘SPID ‘+convert(varchar(4), B.spid)+’ is blocking SPID ‘+ convert(varchar(4), A.spid)+char(13)+char(10)
+ ‘Transaction Information: ‘+case B.open_tran when 1 then ‘Open Transaction’ else ‘No Transaction’ end +char(13)+char(10)
+ ‘Waittime: ‘+convert(varchar(20), A.waittime) +char(13)+char(10)
+ ‘Blocked User: ‘+convert(varchar(20), A.loginame) +char(13)+char(10)
+ ‘Blocking User: ‘+convert(varchar(20), B.loginame) +char(13)+char(10)
+ ‘Command: ‘+ convert(varchar(20), A.cmd) +char(13)+char(10)
from master..sysprocesses A join master..sysprocesses B on A.blocked = B.spid
where B.spid <> A.spid
@profile_name = ‘ProfileNameFromDBMailConfiguration‘,
@recipients = ‘YourEmail.com; anotheremail.com‘,
@body = @Email,
@subject = ‘ServerName – Blocking Occurring!!!’,
@body_format = ‘HTML’
You do need to keep in mind that blocking is normal. If you have this job run every 5 minutes you will see periodic blocking. However, by the time you get into the server the block maybe have already been cleared. Essentially this script is a good starting point for monitoring. I like to run the scrip every five minutes. If I get an email I will read the email but not take action until I see another email.
Next Blog: Run a SQL Profiler trace during high usage hours. We will kick off one of the most low impact profile traces on a server.