Lots of awaiting command with status sleeping in SQL Server 2008R2

  • Hi All,

    In my SQL Server 2008R2; if i run SP_WHO2 i see lots of SPID(Awaiting Command) with sleeping status. I checked and found these are consuming lots of CPU time and in program name column details are - OSQL-32, Micorsoft SQL Server Mangement Studio, Microstoft SQL Server Management Studio-Query Analyzer and some users applicaiton name. And CPU usage is shooting between 40-50% for SQLServer.exe all time since there is no active transaction too. How to troubleshoot this?

    Thanks in advance.

  • First, stop using sp_who2. It gives you some information, but not enough. Instead, start using the Dynamic Management Objects (DMO). Start with sys.dm_exec_requests. That will basically show the same thing as sp_who2, but, in addition it will show you the current wait type and the last wait type so that you can begin to understand what these processes are waiting on. Once you know what they are waiting on you can begin to further troubleshoot the queries. Most of the time, it's the T-SQL code itself, so be ready to get into the code and look at the execution plans.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

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