will killing a process using cpu of 1,800,000 that is SLEEPING speed things up

Question

Post reply

will killing a process using cpu of 1,800,000 that is SLEEPING speed things up

adam spencer

Hall of Fame

Points: 3926
More actions
October 19, 2009 at 9:03 am

#136618

will killing a process using cpu of 1,800,000 that is SLEEPING speed things up
when users complain about the apps that run off sql server 2005, I do a sp_who2 to see
what is running. If something is sleeping, is not not slowing anything down even if
it says that SPID is using cpu_time of 1,800,000.
my boss thinks he is fixing user complaints by dropping and rebuilding indexes.
of course that will help, but what about large sleeping SPIDs.
We do a lot of development on the same server, just on a test database on the same instance, some times our jacked up code
runs wild. I noticed this a few weeks ago after users slow running complaints, since it was my SPID I killed it, and things were much faster, but at the same time my boss said another person simply had all locked out of sql and that was the problem, if that were the problem they would of emailed us with locked out/ no permission type errors i would think not slow running errors, Am I right.
I just do not think he is on the right track by dropping and rebuilding when its slow when i see gigantic SPIDS using nearly 2 million (I guess in cpu millisecond time) either way it way larger than others, but agian does it matter if its that process' status is SLEEPING anyway.

Viewing 5 posts - 1 through 4 (of 4 total)

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

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 1

It depends what process it is.. Can you find out? Maybe using sp_who2?

For example I have a LogReader process that has used 6 million ms of CPU.

Animal Magic SSChampion Points: 13964 More actions · Answer 2

you cant just kill it because it has a large CPU usage. If your server has been online weeks/months etc you may have an app that uses one connection and over that time period the CPU has slowly built up. it doesnt neccesarily mean its causing issues.

What symptoms are the users reporting?

adam spencer Hall of Fame Points: 3926 More actions · Answer 3

Thanks for you answers. The say it is running slow. They are using an Access front end through citrix terminal server to ,select update, and insert. Of corse Access is calling server side SP's that does that runs these quieries. We have ruled out

that maybe Citrix or there remote connection is slow becuase there Outlook and other programs are running fast remotely.

Animal Magic SSChampion Points: 13964 More actions · Answer 4

adam spencer (10/19/2009)
Thanks for you answers. The say it is running slow. They are using an Access front end through citrix terminal server to ,select update, and insert. Of corse Access is calling server side SP's that does that runs these quieries. We have ruled out
that maybe Citrix or there remote connection is slow becuase there Outlook and other programs are running fast remotely.

I would probably start by doing a profiler trace of a few of the most commonly run SP's and pay close attention to the duration. See if there is a pattern as to when the duration spikes (assuming thats what its doing). If the system slow downs are on a repeated time cycle then it will be easier to track down what runs on that time interval.

If they are sporadic then you may have to setup another trace to trace locks. It could be that you have excessive locking going on at certain times and this is causing the queries to take longer than expected.

John