(sample output from PerformanceCollector.BlockAndWaitMonitor)
Manually Capturing a Spid
You can manually run EXEC PerformanceCollector.WaitAndBlockInspector at any time and it will log current waiting and blocking data. This may be useful if you notice your query is taking longer than it should. You can also pass a @spid parameter like this EXEC PerformanceCollector.WaitAndBlockInspector @spid = 100; and it will show you any waiting and blocking as well as any activity for the spid. This data is output to the screen and logged in the table.
Abandoned Spid Handling
BlockAndWaitMonitor will also show "abandoned spids." An abandoned spid is any spid with an open transaction for at least 60 seconds that is blocking something else. Here is the most important aspect...the spid must be sitting on AWAITING COMMAND. That is what differentiates an abandoned spid from an ordinary, run-of-the-mill blocking spid. When the spid is sitting on AWAITING COMMAND that means the SQL Server is waiting for another command from the spid to do something...most likely issue a COMMIT TRAN.
I have never seen a case where the spid will come back and issue the COMMIT. Hopefully your developers are smart enough that they don't allow user input while a database transaction is open. In every case an abandoned spid occurs because a data access framework thinks it is NOT in implicit_transaction mode and SQL Server thinks it is. For instance, various combinations of jdbc and websphere produce this behavior because jdbc does not issue sp_reset_connection commands like .NET does.
An abandoned spid is first logged after 60 seconds. After 5 minutes of being in an abandoned state, and after another spid has been blocked by it for 60 seconds, the spid is killed. It is logged to PerformanceCollector.SpidsKilled.
You can of course disable this feature in PerformanceCollector.Config.
BlockAndWaitMonitor is the most important aspect of the PerformanceCollector tool. It will show you how the SQL Server subsystems (IO, Memory, lock manager, log manager, etc) are contributing to "waiting" on your SQL Server. You will see more than just the SQL command that is blocking...you'll see an approximation of why it is blocking. This is important. Just because a procedure is long-running does not mean a new index will fix it, nor will a rewrite either. You have to understand why it is behaving like it is and which subsystem is contributing. Only then will you know what needs to be fixed.
In the next post I'll cover some of the other features of Performance Collector.