September 16, 2013 at 10:43 pm
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.
September 17, 2013 at 4:04 am
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