sql performance

  • My customer told me that he is facing a slow performance and when i executed the query "select * from sysprocesses where blocked <> 0 " it was showing a row with spid 101 and waittime is about 17528000 and i killed the SPID (kill 101) by getting permission from customer but still it was showing in the result set wat should i do to get rid of that?

  • Sounds like its doing a rollback...

    Perhaps next time, find out what its doing and why its taking so long before you kill it off.

  • Check this spid in using sp_who2 proc,and check whether its wait for other request id(may be deadlock condition)

  • premkuttan.lakshmanan (12/15/2010)


    wat should i do to get rid of that?

    Wait. It was doing some large data modification and has to roll back all of the changes. Rollback usually takes as long or longer than the operation to the point you killed it.

    Do not try to restart the sQL service. If you do, the rollback will continue after the restart and the database will be offline for the duration.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Developer 2005 (12/15/2010)


    (may be deadlock condition)

    If it was a deadlock SQL would have detected and handled it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • in sp_who2 the coressponding blkby for spid 101 is 101 and in the corresponding command column it shows as killed/Rollback . Does it mean that the activity is getting rolled back

  • Yes. The connection has been killed and SQL is rolling it back. Be patient.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (12/15/2010)


    Yes. The connection has been killed and SQL is rolling it back. Be patient.

    Gail, as a matter of interest, will DBCC inputbuffer on the SPID work now to show what statement was causing all the grief? I normally run that before killing the SPID 😛

  • Offhand, no idea. The DMVs should. sys.dm_exec_sessions cross apply sys.dm_exec_sql_text.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • premkuttan.lakshmanan (12/15/2010)


    in sp_who2 the coressponding blkby for spid 101 is 101 and in the corresponding command column it shows as killed/Rollback . Does it mean that the activity is getting rolled back

    If a spid such as 101 shows that it is blocking itself, that is not really a problem.

    What if 101 was doing an important update ?

    DBCC inputbuffer will often show what is being executed

  • homebrew01 (12/15/2010)


    premkuttan.lakshmanan (12/15/2010)


    in sp_who2 the coressponding blkby for spid 101 is 101 and in the corresponding command column it shows as killed/Rollback . Does it mean that the activity is getting rolled back

    If a spid such as 101 shows that it is blocking itself, that is not really a problem.

    What if 101 was doing an important update ?

    Hopefully it wasn't doing the December payroll and bonus run. :hehe:

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply