sql performance

Question

Post reply

sql performance

premkuttan

SSCommitted

Points: 1934
More actions
December 15, 2010 at 5:16 am

#146550

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?

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

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

grahamc SSCertifiable Points: 6762 More actions · Answer 1

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.

SQL Editor SSCommitted Points: 1980 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

premkuttan SSCommitted Points: 1934 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

grahamc SSCertifiable Points: 6762 More actions · Answer 7

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 😛

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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