December 9, 2009 at 10:59 am
We just recently upgraded a server to 2008 (all the way from 2000), and are looking for new features to take advantage of. So far, the best I found was this link: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx, which talks about using the BLOCKED_PROCESS_REPORT event notification in SQL 2005, and leaves the email part to the reader.
Does anyone know of an easier way than this, perhaps something new to SQL Server 2008?
Thanks.
- Dave
December 9, 2009 at 11:47 am
Hi Dave,
There are a number of ways to ago about this; extended events, etc. You might want to check out the extended event project on CodePlex (http://www.codeplex.com/ExtendedEventManager). If your looking for something simple you could query the DMV (dynamic management views) and e-mail the output using sp_send_dbmail (see BOL for some examples); i.e.
select blocked.session_id as blocked_session_id,
blocked_sql.text as blocked_sql,
waits.wait_type as blocked_resource,
blocking.session_id as blocking_session_id,
blocking_sql.text as blocking_sql,
getdate() as blocked_date
from sys.dm_exec_connections as blocking
inner join sys.dm_exec_requests as blocked
on blocked.blocking_session_id = blocking.session_id
cross apply
(
select * from sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
) as blocking_sql
cross apply
(
select * from sys.dm_exec_sql_text(blocked.sql_handle)
) as blocked_sql
inner join sys.dm_os_waiting_tasks as waits
on waits.session_id = blocked.session_id
As far as new features goes there are a quite a few 🙂
Here are a couple of links to help get you going -
http://www.microsoft.com/sqlserver/2005/en/us/top-30-features.aspx
http://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx
http://technet.microsoft.com/en-us/library/cc721270.aspx
Tommy
Follow @sqlscribeDecember 9, 2009 at 1:44 pm
Thanks. I'll look into that.
I found another way that seems to be the easies - what about setting up an alert in the SQLServer:Locks object? Are you familiar with this?
I set up an alert where Total "Number of Deadlocks/sec" is >0, set the options to send an email, then created a blocking situation (verified with activity monitor). Screen shot attached.
Problem is, I never got alerted, and the history on the alert says it never fired.
Am I missing something? Is there something I have to do to enable the alerting engine?
- Dave
December 9, 2009 at 2:02 pm
dpt (12/9/2009)
Thanks. I'll look into that.I found another way that seems to be the easies - what about setting up an alert in the SQLServer:Locks object? Are you familiar with this?
I set up an alert where Total "Number of Deadlocks/sec" is >0, set the options to send an email, then created a blocking situation (verified with activity monitor). Screen shot attached.
Problem is, I never got alerted, and the history on the alert says it never fired.
Am I missing something? Is there something I have to do to enable the alerting engine?
- Dave
NP - you mentioned you created a blocking situation - did it actually trigger a deadlock? Try the example below to trigger the alert notification:
-- 1) Create Objects for Deadlock Example
USE TEMPDB
CREATE TABLE dbo.foo (col1 INT)
INSERT dbo.foo SELECT 1
CREATE TABLE dbo.bar (col1 INT)
INSERT dbo.bar SELECT 1
-- 2) Run in first connection
BEGIN TRAN
UPDATE tempdb.dbo.foo SET col1 = 1
-- 3) Run in second connection
BEGIN TRAN
UPDATE tempdb.dbo.bar SET col1 = 1
UPDATE tempdb.dbo.foo SET col1 = 1
-- 4) Run in first connection
UPDATE tempdb.dbo.bar SET col1 = 1
I've used the method below to monitor deadlocks in the past w/ great success -
http://sqlblogcasts.com/blogs/martinbell/archive/2009/05/26/WMI-Event-Alerts.aspx
Tommy
Follow @sqlscribeDecember 9, 2009 at 2:10 pm
No, sorry - you're right. I didn't create a deadlock. I want to monitor locks, not deadlocks. I've tried various locking counters and the last one I tried was deadlocks/sec. I know the difference between a lock and deadlock, but had a brain f@rt.
The way I created a lock was to start query analyzer, and in one session do:
Begin tran
update table set ...
and then in another session do:
select * from table...
Thus generating an infinite lock.
Anyway, I've used PerfMon to see what counters are being updated, and it looks like this isn't going to work. Lock Request/ms looks like it would work for my needs, but it stays at 0. Any idea why that isn't updating when I have an active blocking transaction?
December 9, 2009 at 2:38 pm
Regarding performance counters, Lock Wait Time (ms) and Lock Waits/sec should offer some more insight into lock escalation issues;
Lock Wait Time (ms)
Total wait time (milliseconds) for locks in the last second. Look for any peaks that are close (or exceeds) to a wait of 60 sec. Though this counter counts how many total milliseconds SQL Server is waiting on locks during the last second, but the counter actually records at the end of locking event. So most probably the peaks represent one huge locking event. If those events exceeds more than 60 seconds then they may have extended blocking and could be an issue. In such cases, thoroughly analyze the blocking script output. Some applications are written for timing out after 60 seconds and that’s not acceptable response for those applications.
Lock Waits/sec
This counter reports how many times users waited to acquire a lock over the past second. Note that while you are actually waiting on the lock that this is not reflected in this counter—it gets incremented only when you “wake up” after waiting on the lock. If this value is nonzero then it is an indication that there is at least some level of blocking occurring. If you combine this with the Lock Wait Time counter, you can get some idea of how long the blocking lasted. A zero value for this counter can definitively prove out blocking as a potential cause; a nonzero value will require looking at other information to determine whether it is significant.
Tommy
Follow @sqlscribeDecember 9, 2009 at 2:44 pm
notified in real time when a lock is still locking. Maybe I can't use the alerter after all. Rats.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply