Sleeping mode issue

Question

Sleeping mode issue

saidapurs

Hall of Fame

Points: 3477
More actions
August 15, 2012 at 11:54 pm

#262946

Hi All,
I have single Database server using 8GB RAM & good space for DB.
Issue :- SA users are going to Sleeping mode & they using CUPTIME more than 20000. My customer asking why user are going to sleeper mode & if user goes sleeper mode also than why using CPU TIME like 20000.
Please help me on...
Thanks
Satish

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

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

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

A sleeping connection doesn't use CPU. Sleeping means it's finished running any queries and the connection is idle. eg if you open a SSMS query window, run a single query and then leave the window open, that connection will be sleeping as it has no more work to do.

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

saidapurs Hall of Fame Points: 3477 More actions · Answer 2

Hi Gila,

I agree with you but when i check sp_who2 in particular database users are in sleeping mode & CPUTIME is more than 20k. When i kill that users server is coming to normal condition.

Please advice me what shall i do...

Thanks

Satish

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

That's not the CPU they are using. That's the total CPU that they used since connecting. The CPU was used when they ran queries. No CPU is used for a sleeping connection. Ignore them, no need to kill them (and killing can in some cases mess the apps up)

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

saidapurs Hall of Fame Points: 3477 More actions · Answer 4

Hi Gila,

I observed that some other database user sleeping mode sessions are available but CPUTIME is 0 to 100 but this particular database sleeping users CPUTIME is more than 20000.

If i not kill these users than how do i bring down my CPU??

Thanks

Satish

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

As I said...

GilaMonster (8/16/2012)
That's not the CPU they are using. That's the total CPU that they used since connecting.

A sleeping connection does not use any CPU. The CPU that you see in sysprocesses is the cumulative (total) CPU that the connection used from the point that it connected to now. Killing a sleeping connection won't reduce CPU because a sleeping connection is not currently using any CPU.

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

saidapurs Hall of Fame Points: 3477 More actions · Answer 6

Hi Gali,

Will you advice me what shall i do for this issue. Its getting alerts to customer...

Thanks

Satish

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

Nothing. Sleeping connections are not a generally problem.

If you have high CPU usage, then investigate high CPU usage.

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

saidapurs Hall of Fame Points: 3477 More actions · Answer 8

Hi Gali,

My MSSQL server is using 75% of server utilization...

Thanks

Satish

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

Chapter 3: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Sleeping connections are not the problem. Ignore them

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