Yes, I was looking into the conditions why KPID =0 and at the sametime I see Open_tran>0 and server not doing anything SPid in Sleeping state, so I concur that if SPIDs are in Running state, KPID is allocated (Worker thread is allocated);
Does this hold correct.
If you see sleeping SPIDs with open_tran>0, awaiting command and sleeping, this is a clear indication the session has an open transaction. Like Erland, I suspect this might not be intentional and the root cause might be that the application
is not managing transactions and query timeouts properly. This can result in the "hung application" symptom as well as the symptom of many connections with open transactions.
I once experienced a scenario very much like this. The application used a home-grown connection pool rather than leveraging connection pool feature of the client API (which does a better job of cleaning up connection state). Some stored
procedures had explicit transactions.
After a timeout occurred executing a proc with an explicit transaction, the application did not know to execute a rollback because the transaction was started in the procedure code. All subsequent activity on that connection after the timeout was inadvertently
done in the original transaction context, resulting in much blocking and inadvertently nested transactions. Only a SQL Server restart could correct the problem.
We then added SET XACT_ABORT ON to all stored procedures with explicit transactions. This solved the open transaction problem because the transaction was automatically rolled back upon receiving the attention event from the client after the timeout.
Dan Guzman, SQL Server MVP, http://www.dbdelta.com
