CPU Usage of sqlservr.exe

Question

Post reply

CPU Usage of sqlservr.exe

lena_d

Old Hand

Points: 326
More actions
April 14, 2004 at 7:03 am

#84799

We have 1000 small (10-100MB) databases on SQL2000 SP3. Sometimes CPU usage of sqlservr.exe service jumps up to 100% and after some hours returns to normal values 7-20%. Can anybody reccomend me tools to find reason of such behaviour?

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

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Check to see what is running on the server during those times.

Create a profiler trace and track back to activity during those periods.

Shamless self promotion - read my blog http://sirsql.net

mramir74 Ten Centuries Points: 1071 More actions · Answer 2

I would run sp_who2 to check what processes are using up CPU and if any blocking is happening.

If you have the money, I would recommend using NetIQ's DiagnosticManager. It monitors real time stats on your server and it also has a feature callew Worst Performing Queries/Stored Procedures/Batches. It actually tells you the queries that are performing badly.

BTW, is it happening regularly during the day?

Thanks.

lena_d Old Hand Points: 326 More actions · Answer 3

No, is it happening not regulary. Is it possible because of different collates of tempdb and customer databases? Or query which is gathering data from two databases with different collates?