I'm fairly new to actually looking closely at the performance monitors on Server 2008.
So I've selected Lock Requests/Sec and Timeouts/Sec in the window, and I look at the graph, and it's fairly flat--this is a dev instance of sql server, nothing really active.
I do a query in an SMS query window:
(No Commit is done here)
Then I see right then a spike in lock requests. Good so far.
In another window I do
So of course the second says "executing..." because the first hasn't committed. But isn't this when I should see a spike in Lockwaits and Locks-per-second?
I can let this wait for 30 seconds and the perfmon shows zero lock-waits and zero lock reuests.
I go back to the first window, type Commit and execute that, now the second window completes it's update.
All good so far *but*...now on perfmon I see the Lock Requests spike *after* the second query has finished. There is spike in the lockwaits too--but this is after everything is done. The spike in the Lock Requests is more of a running plateau, then a series of spikes every 5 seconds for the next 30 seconds or so.
So my question is: Why does perfmon say "There are 10,000 lock requests per second at 1:55:20 PM", when in reality, that very time period is a full 30 seconds *after* both querys executed?
I guess I'm wondering also--how would I know if someone had an uncomitted transaction that was causing others to wait--the perfmon shows zero lock request and lockwait activity so one could look at that and think "there's nothing locked, nothing waiting, everything is fine".
Thanks for any info on this,
--Jim
So I've selected Lock Requests/Sec and Timeouts/Sec in the window, and I look at the graph, and it's fairly flat--this is a dev instance of sql server, nothing really active.
I do a query in an SMS query window:
Code:
Begin tran
Update f set f.blah = 'blah' from foo f with (rowlock);Then I see right then a spike in lock requests. Good so far.
In another window I do
Code:
Update f set f.blah = 'somethingelse' from foo f with (rowlock);I can let this wait for 30 seconds and the perfmon shows zero lock-waits and zero lock reuests.
I go back to the first window, type Commit and execute that, now the second window completes it's update.
All good so far *but*...now on perfmon I see the Lock Requests spike *after* the second query has finished. There is spike in the lockwaits too--but this is after everything is done. The spike in the Lock Requests is more of a running plateau, then a series of spikes every 5 seconds for the next 30 seconds or so.
So my question is: Why does perfmon say "There are 10,000 lock requests per second at 1:55:20 PM", when in reality, that very time period is a full 30 seconds *after* both querys executed?
I guess I'm wondering also--how would I know if someone had an uncomitted transaction that was causing others to wait--the perfmon shows zero lock request and lockwait activity so one could look at that and think "there's nothing locked, nothing waiting, everything is fine".
Thanks for any info on this,
--Jim
