?
Solved

HELP!!! SQL 2008 R2 CPU Utilization Problem

Posted on 2011-05-05
15
Medium Priority
?
780 Views
Last Modified: 2012-05-11
Hi All,

I have a MS Access 2007 application using SQL Server 2008 R2 as a backend.  This is pretty much a brand new install of a recently revamped MS Access application.  Many times throughout the day the application will freeze up - When I check the SQL server during that time, the CPU is pretty much maxed out by the SQLServer process.  I can barely remote in to the server during this time because there are close to no resources left.  When this occurs it can last up to about 5-10 minutes.

The SQL Server is in a virtual enverionment (using VMWare ESXi).  4 Cores and 6GB of memory are allocated to this server.

Of course it could be poor DB design, missing indexes, etc contributing to this problem...but considering the length of time the CPU was maxed out, I'm thinking there could be a larger issue.  The issue also 'appears' to be intermittent.  DB integrity checks, index rebuilds occur nightly.

I want to rule out SQL Server instance itself as the issue.  Where should I begin troubleshooting the SQL side?  Also, SQL installatioin was mainly defaults.
 
0
Comment
Question by:polaris101
15 Comments
 
LVL 15

Expert Comment

by:Robert Sutton Jr
ID: 35702830
Have you reviewed the logs and or running processes to see what is using alot of memory or resources?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35702837
Is SQL Server the only application installed on the server?
What queries are running when the application freezes up?
0
 

Author Comment

by:polaris101
ID: 35702901
@The Warlock - SQL logs do not show anything.  Windows Log shows a DistributedCOM (10016) Error every half hour.

The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID
{46063B1E-BE4A-4014-8755-5B377CD462FC}
 and APPID
{FAAFC69C-F4ED-4CCA-8849-7B882279EDBE}
 to the user DOMAIN\SQLServerAgent SID (S-1-5-21-1578215393-2173118585-3370318866-1152) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:polaris101
ID: 35702909
@acperkins - Yes SQL is the only application on this server.  To be honest, I'm not sure what query is running when this happens.  I have SQL profiler up, but when it happens everything freezes since the CPU is tax'd.  If I run the profiler from another machine when it's happening the profiler times out.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35703223
You can quickly identify the queries on the server that are the heaviest users of CPU using the built-in reports.

From SQL Server Management Studio, right click on your server and select "Reports" -> "Standard Reports", and then selecting "Performance - Top Queries by Average CPU Time".

This will show you the top 10 queries run on the server based on CPU usage per instance. Ideally, you'll see one that stands out and be able to work to clean it up or reschedule it. If you don't see a clear standout here, then maybe you can run the "Performance - Top Queries by Total CPU Time" report, which will show you the queries that monopolize the CPU over time, so if one query is run a million times and uses a moderate amount of CPU each time, it will show up on that report.

Good luck!
0
 

Author Comment

by:polaris101
ID: 35703260
@ryanmccauley - Thanks.  Do you absolutely think that this is being cause by a query?  In your experience is there anything else besides a query that could tax out a CPU like this?  
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35703293
I can't be absolutely sure without the server in front of me, so it's only a guess ;) There are a number of things that could cause CPU usage like that, all the way down to a bad install or some problem with SQL Server itself.

It's likely caused by a query though - that's my first guess.
0
 

Author Comment

by:polaris101
ID: 35703342
@ryanmccauley - Even running some of the standard reports crushes the CPU to 75% or so to the point where I can't even navigate in windows explorer while it's running.  Is that normal>?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 35704394
Hi,

Do check the windows logs for the times that the CPU is maxed out. There could be other things that are causing it.

Check the SQL processes using sp_who (sp_who2 is more detailed)

Check the SQL locks on sp_lock.

HTH
  David
0
 

Author Comment

by:polaris101
ID: 35706638
UPDATE: - I haven't seen any issues today.  I rebooted the server last night.  Is it possible that rebooting is actually clearing something out that would cause this?
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35707175
Had you previously rebooted the server since this issue started to occur? though it's still odd behavior, it's definitely possible that a reboot could have solved whatever problem was occurring. I was actually going to ask if you'd restarted SQL Server, because those Standard Reports usually come up after only a few seconds of crunching for me - pegging your CPU and locking up the interface is definitely not supposed to be what happens when you attempt to view them.

In any case, glad it appears to be resolved.
0
 

Author Comment

by:polaris101
ID: 35707176
UPDATE (cont) : Also, last night I uninstalled a MS System Center Op Mgr agent, and disabled an Ahsay backup service.  
0
 

Author Comment

by:polaris101
ID: 35707183
@ryanmccauley - Well, I wouldnt say it's resolved yet - I rebooted the server a couple days ago, and all was good for about 24 hours.
0
 

Author Comment

by:polaris101
ID: 35722221
@ryanmccauley - what are your recommendations regarding max memory configuration?  It seems like this is playing a role into the issues.  SQL is using most of the system memory for cache - and task manager shows less than half a GB free.  Could this cause issues if SQL doesn't free up the memory for other processes?
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 2000 total points
ID: 35724001
SQL Server will definitely seize all the available memory on the server for itself until it's limited with that setting - it assumes it's the only thing running isn't isn't kind about sharing the memory with other processes.

I usually end up setting this value somewhere around 3/4 of the total memory in my server, but it will vary based on the situation - a good rule of thumb would probably be 2GB for the OS and other background processes, and the rest for SQL Server. If you have other things running on the server, like an active IIS tier or other server processes, you'll need to adjust it accordingly, but that's somewhere to start.

It's possible that it could be related to your CPU usage, though I'd expect more disk thrashing if you're seeing that problem, not necessarily CPU-usage. But in any case, you might try limiting the memory usage and see if it has an impact. Also, that's a hot change, so no restart of the service is needed after you adjust those values - they'll take effect immediately.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question