In DPA an idle blocker is a connection that is not currently running a statement but is holding up connections.
What the connection IS doing, is holding onto locks that are preventing other connections from getting their work done. It is a bit of a mystery because my idle blockers show CPU/MEMORY and do not reveal that they are holding locks, and more importantly do not reveal what objects/rows they are holding locks for. This makes it really hard to track down. But some of the other connections blocked show shared lock waits, and some do not. I don't see intent locks and am not sure why.
Also the connection is idle in the current time period that is shown, but the former commands run may be available in earlier time periods. The problem is, the last command run is not at all necessarily the one that is holding the locks that are blocking other sessions. So for example, "sp_tables" is my last command run, and I don't know for sure if there are system table locks involved, but since it completed I think earlier application SQL is to blame.
What is needed is for DPA to keep track of what objects are locked and for what period of time they are locked, in the event that the locking time is excessive. Also, what is needed is the intent locking, so that connections that are waiting for locks can be analyzed. Connections that are in a wait state can have the locks held/desired analyzed. The "Details.." , "statements executed by session NN while holding the lock" feature is helpful when it appears, but it is tricky to get the link to appear by manipulating the time period. Ideally, we would get a summary of lock counts and types by object , such as table lock, row lock rowct=nn, and we would further know which statement caused the lock.
Also ideally, we would be able to know about transaction start and end. Due to connection pooling and filtering of commands by whether are not they are logged at all, I cannot tell if subsequent commands are in the same transaction or not. It would be nice to know what the grouping of commands is, that a bunch of SQL statements frequently appear in the same transactions together. Because ultimately, transactions block each other, not connections. You need a connection to create a transaction, and some transactions are single statement, but most of our blocking issues will be due to large transactions and we have trouble identifying what those are, and where they start and end in the application code. It should be fairly straightforward to figure out where they start and end on the database using transaction nest level and transaction ids. Just knowing the nest level when not 0 would be a great easy start.
Another tool that I have used, Precise I3, was able to show historical object contention analysis indicating which table and index objects were frequently locked, which statements locked them, and which statements had lock contention over an object. I replaced Precise I3 with Confio Ignite because of the difficulty licensing and installing and upgrading Precise I3 agents on monitored machines. But this feature was awesome, and another related feature that kept track of table index usage using the same data to identify important indexes and indexes that were not actually used at all.
Anyway, if there is a way to identify what objects are locked, please let me know. Otherwise, it is time for a new product feature, because I am spending an inordinate amount of time in forensic analysis of locking contention in order to make proper tuning recommendations.
