C# .NET - High CPU usage of sqlservr.exe process

Asked By Kanagasabapathy T on 07-Jan-10 07:22 AM

Hi,

Can you please provide me the troubleshooting steps for high usage of sqlservr.exe process. It reaches 100 % CPU sometimes and even twice or thrice a day degrading the database performance.

In one of our SQl server , there are two sqlservr.exe process are running for two different sql database instance. which instance (say instance name:  X and Y )is the owner of these two sqlservr.exe process ?


Thanks,

Kanagasabapathy.

re

Kalit Sikka replied to Kanagasabapathy T on 07-Jan-10 07:29 AM
Are you sure that its only SQL Server's Process exe that is becoming slow or the other services?

If it is the other services, then you need to restrict the amount of memory
that SQL Server uses by using the aforementioned MAX memory.

High CPU usage of sqlservr.exe process

Sagar P replied to Kanagasabapathy T on 07-Jan-10 07:46 AM

Once you check Processes tab in Task Manager, you will find sql server process and there you can find out the User Name. And easily find out with which user its belong to.

But as you say your CPU usage goes upto 100%, it can be because of some queries in your sql server which are fetching lot of data or firing query against large data table.

So I will suggest you to find out that first and to do that you can go for SQL Server Profiler, which is a easy and perfect tool to know what is happening in our database.

Goto

Microsoft Sql Server -> Performance Tool -> SQL Server Profiler

In that click on File -> New Trace -> Connect to perticular database.

And you will see window where you can see all details about query which is currectly running and using how much CPU usage.

You will find columns like CPU, Reads, Write from which you can check, because of which query its getting problem.

We have faced same problem before some days and we solved it like this only.

Try it and try to change query according to your requirement.

High CPU usage of sqlservr.exe process

Kanagasabapathy T replied to Sagar P on 07-Jan-10 09:02 AM

Hi Sujit,

Thanks for the info.

But the two sqlservr.exe processes are runing on the same username.How to relate a particular sqlservr.exe between the two instances.

Eg: processes

               Image name         user name   CPU

               sqlservr.exe           abc              90 %    ( Instance X or Y)

               sqlservr.exe           abc              40 %     ( Instance X or Y ) 

Can you please provide the maximum critical limit for CPU , Read, Write and I/O of a query or any other activity in the database beyond which an extra attention is required. Is it possible to trace a query which used maximum CPU two days back.

Eg: On Jan 4th,2010 at 6:00 AM - CPU usage  of sqlservr.exe reached 100 %.

Again On Jan 4th, 10:00 AM - it reached 95 %

See this
Sagar P replied to Kanagasabapathy T on 07-Jan-10 09:13 AM

The default installation will have TWO instances of MSDE which show up as sqlservr.exe in the task manager.  If you looked at your "services" msc you would see them there:  MSSQL$SBSMONITORING and MSSQL$SHAREPOINT.

You may have multiple instances of SQL running on SBS,

Look at this URL to help identify them,

http://msmvps.com/blogs/bradley/archive/2005/02/04/34984.aspx

see
Sagar P replied to Kanagasabapathy T on 07-Jan-10 09:15 AM

I think its not possible to trace a query which used maximum CPU two days back.

So you need to trace current query when your CPU usage become large. and you have to use SQL Server Profiler for same.

`High CPU usage
mv ark replied to Kanagasabapathy T on 08-Jan-10 03:34 AM
To troubleshoot the high CPU usage issue, run a trace (http://blogs.techrepublic.com.com/datacenter/?p=156) & then use a tool like ClearTrace (http://www.scalesql.com/cleartrace/default.aspx ) to analyze the trace data, as suggested here - http://weblogs.sqlteam.com/billg/archive/2006/06/19/10271.aspx

Have you installed the latest service pack? Sometimes using the latest one could resolve the issue -
http://bytes.com/topic/sql-server/answers/455083-100-cpu-usage-part-2-a
http://support.microsoft.com/kb/915308


Jonathan VH replied to Kanagasabapathy T on 08-Jan-10 12:30 PM
If a SQL Server instance "reaches 100% CPU sometimes and even twice or thrice a day," there's probably nothing to worry about. If the process stays at 100% for long periods (i.e. minutes, not seconds) and cannot be explained by some optimized but still lengthy and processor-intensive SQL activity, then you might want to optimize the code and/or upgrade the server.