none
KPID SPID state ?

    Question

  • If KPID=0 and Open_tran>0 for a SPID what does it signify; How is it possible. Does it mean KPID is assigned for Running spid.
    Saturday, December 07, 2013 3:52 AM

Answers

  • Hello,

    I guess you are talking about the result of the system view sys.sysprocesses (Transact-SQL)? First, this view is already deprecated, so don't use it any longer.

    KPID presents the Windows Thread id and you are wondering why a SQL Server session which don't belong to a Windows thread have an open transaction? Everything starts a transaction (=>ACID) in SQL Server, so even system processes can have open transactions.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, December 07, 2013 4:25 AM
  • And particularly, a process can start a transaction but for some reason not commit or roll it back before it goes idle. It is usually not a good thing when it happens, and the cause is in many bad coding or application design.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SQLguy1001 Sunday, December 22, 2013 7:58 PM
    Saturday, December 07, 2013 10:18 AM
  • 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

    • Marked as answer by SQLguy1001 Tuesday, December 24, 2013 6:00 PM
    Tuesday, December 24, 2013 5:08 PM
  • If the application experiences a query timeout, it should always respond with "IF @@trancount > 0 ROLLBACK TRANSACTION".

    It is also a good idea to issue "SET XACT_ABORT ON" upon connection as well as include it in all stored procedure as Dan says. But the application should not assume that just because it issued SET XACT_ABORT ON when it connected it does not have to roll back, since some SQL code for whatever reason my fiddle with the setting.

    This is a typical example of defensive program. The less various components in a system trust each other, the more stable the overall system will be.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SQLguy1001 Thursday, December 26, 2013 10:34 AM
    Tuesday, December 24, 2013 10:33 PM

All replies

  • Hello,

    I guess you are talking about the result of the system view sys.sysprocesses (Transact-SQL)? First, this view is already deprecated, so don't use it any longer.

    KPID presents the Windows Thread id and you are wondering why a SQL Server session which don't belong to a Windows thread have an open transaction? Everything starts a transaction (=>ACID) in SQL Server, so even system processes can have open transactions.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, December 07, 2013 4:25 AM
  • And particularly, a process can start a transaction but for some reason not commit or roll it back before it goes idle. It is usually not a good thing when it happens, and the cause is in many bad coding or application design.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SQLguy1001 Sunday, December 22, 2013 7:58 PM
    Saturday, December 07, 2013 10:18 AM
  • 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.
    Sysprocesses still very useful (may be deprecated, but very reliable). Ms should continue with it, if it can continue with undocumented stuff from Sybase Era.
    Sunday, December 22, 2013 8:02 PM
  • I have seen such situation and was wondering why SQL Server is not able to handle that.
    400 Spids doing nothing - all in sleeping state , 1 in rollback sate with waittype logbuffer??
    This was in SQL Server 2008. 

    Sunday, December 22, 2013 8:08 PM
  • I have seen such situation and was wondering why SQL Server is not able to handle that.
    400 Spids doing nothing - all in sleeping state , 1 in rollback sate with waittype logbuffer??

    Are the sleeping spids all awaiting command?  In that case, it simply means the application is connected but not currently executing a query on the connection.  There is nothing SQL Server does except wait for the application to execute another command.  It's up to the client application to handle the connection management.  It would be bad if SQL Server unilaterally closed the client connection on the server side.

    It is common for a rolling back spid show logbuffer waits.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, December 22, 2013 8:24 PM
  • 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.
    Sysprocesses still very useful (may be deprecated, but very reliable). Ms should continue with it, if it can continue with undocumented stuff from Sybase Era.

    Awaiting for Reply !!!

    Monday, December 23, 2013 5:14 AM
  • I have seen such situation and was wondering why SQL Server is not able to handle that.
    400 Spids doing nothing - all in sleeping state , 1 in rollback sate with waittype logbuffer??

    Are the sleeping spids all awaiting command?  In that case, it simply means the application is connected but not currently executing a query on the connection.  There is nothing SQL Server does except wait for the application to execute another command.  It's up to the client application to handle the connection management.  It would be bad if SQL Server unilaterally closed the client connection on the server side.

    It is common for a rolling back spid show logbuffer waits.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Yes, that what happened, the Application was hung and they stopped it, but Spids were still in Sleeping state!! We found KPID is zero for them, Open_tran>1 , all spid pending on Logbuffer, and SQL Server silent ( smiling on us) doing nothing.  Restart of the SQL Server resolved the issue. Application team put onus on SQL Server that is is not able to handle Idle/sleeping connections as they have stopped the application. That was so embarassing.
    Monday, December 23, 2013 7:51 AM
  • We was not there to tell what happened, but my diagnosis is that they did not really stop the application (of which the architecture is completely unknown to me). Then again, if you manage to stop the application in such away that the network library does not notice that the other end is gone, how would SQL Server to know.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 23, 2013 8:50 AM
  • We was not there to tell what happened, but my diagnosis is that they did not really stop the application (of which the architecture is completely unknown to me). Then again, if you manage to stop the application in such away that the network library does not notice that the other end is gone, how would SQL Server to know.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    When we ran netstat on SQL Server, it was showing connections to SQL Server, so application team put ONUS on our team that SQL server is not able to handle such issues (one spid was in rollback state waiting on logbuffer).  They have very confirmed that application has been stopped.  The surprising things was SQL Server was lying dormant. It did not have any mechanism to detect such issues, it was waiting on signal from application (Awaiting state), nor did it show any orphaned connections.

    • Edited by SQLguy1001 Monday, December 23, 2013 9:47 AM spelling error
    Monday, December 23, 2013 9:45 AM
  • Yes, if the application is incorrectly coded and does not handle query timeouts correctly (it needs to issue "IF @@trancount > 0 ROLLBACK TRANSACTION"), this can cause a lot of problems, particulary if the application uses the connected model. You get one session with a orphan transaction holding locks. Other processes gets blocked by that process, times out, etc. And then if these process jog along and perform updates, there is quite some work to rollback when the disconnect.

    SQL Server is able to handle the disconnections, and according to the testimony it did it the correct way. The more interesting question is why there were so many processes with open transactions when the application waved bye-bye.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 23, 2013 11:57 AM
  • Yes, if the application is incorrectly coded and does not handle query timeouts correctly (it needs to issue "IF @@trancount > 0 ROLLBACK TRANSACTION"), this can cause a lot of problems, particulary if the application uses the connected model. You get one session with a orphan transaction holding locks. Other processes gets blocked by that process, times out, etc. And then if these process jog along and perform updates, there is quite some work to rollback when the disconnect.

    SQL Server is able to handle the disconnections, and according to the testimony it did it the correct way. The more interesting question is why there were so many processes with open transactions when the application waved bye-bye.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Coz, everything was waiting on Logbuffer on that particular database which had the issue. Even the checkpoint and the backup log operations. Manual checkpoint never completed on the db. The db was in the state for 17 hour undetected, after that we restarted the SQL Server. So I raised question related to KPID=0, but all had open_tran>0.  The intention to raise question was to find  contradiction between - KPID=0 (thread not allocated)  and Open_tran>0 to improve my understanding in this scenario.

    • Edited by SQLguy1001 Monday, December 23, 2013 5:53 PM wanted to explain further
    Monday, December 23, 2013 5:49 PM
  • It could certainly have helped if you had given the full scenario from the beginning. Whether SQL Server actually got stuck is hard to tell. It appears that there was some massive rollback activity going on that server.

    Restarting helped, because in this case the rollback is handled by recovery which is usually faster.

    But the application needs to be fixed to prevent this from happning again.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 23, 2013 11:24 PM
  • 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

    • Marked as answer by SQLguy1001 Tuesday, December 24, 2013 6:00 PM
    Tuesday, December 24, 2013 5:08 PM
  • 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

    Thanks to provide more insight. So, you mean to say that Onus of handling such scenario lies on Application and not SQL Server or if new SQL Server Version will have some mechanism to handle such situations or atleast some alert mechanism.
    Tuesday, December 24, 2013 6:04 PM
  • If the application experiences a query timeout, it should always respond with "IF @@trancount > 0 ROLLBACK TRANSACTION".

    It is also a good idea to issue "SET XACT_ABORT ON" upon connection as well as include it in all stored procedure as Dan says. But the application should not assume that just because it issued SET XACT_ABORT ON when it connected it does not have to roll back, since some SQL code for whatever reason my fiddle with the setting.

    This is a typical example of defensive program. The less various components in a system trust each other, the more stable the overall system will be.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SQLguy1001 Thursday, December 26, 2013 10:34 AM
    Tuesday, December 24, 2013 10:33 PM