CPU usage and troubleshoot (sp_who2, profiling)

(I have only little knowledge in mssql)
Hello,
We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
sticked at 100% during few hours. The dbs are used by customers, we
didn't designed them, we just manage the system until middleware level
(SQL Server)
We would like to trouble this CPU problem. I use some tricks found on
the net, as the sp_who2, which gave me some details on the commands
run. I have a lot of sleeping process with AWAITING COMMAND; the
sp_who2 reported me also CPU and IO usage but I don't know which
units.
The output is at http://rafb.net/p/DWkMQO35.html
Can I have some pointers on how use th output of sp_who2 to track
performance problems
Thanks for your help
And if you are using SQL2005 SP2, you would find the newly released dashboard
reports rather useful in finding queries that may be consuming excessive CPU.
Linchi
"tabarnak@.gmail.com" wrote:

> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>
|||On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Are you on SQL 2000.
> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
> approach I take is normally, to analyse these figures , ascertain whether
> it's an IO or network problem . Then drill down to the db level
> --
> Jack Vamvas
(being away for some days)
Hi Jack,
I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
CXPACKET, LATCH_EX and PAGEIOLATCH_SH
Am I right, could you give some explanatations and hints.
is it possible to clear these stats, in order to have them on a short
sessions, along-side of the profiler for instance.
Thanks
Regards