HELP!!! SQL 2008 R2 CPU Utilization Problem

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.
 
polaris101Asked:
Who is Participating?
 
Ryan McCauleyData and Analytics ManagerCommented:
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
 
Robert Sutton JrSenior Network ManagerCommented:
Have you reviewed the logs and or running processes to see what is using alot of memory or resources?
0
 
Anthony PerkinsCommented:
Is SQL Server the only application installed on the server?
What queries are running when the application freezes up?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
polaris101Author Commented:
@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
 
polaris101Author Commented:
@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
 
Ryan McCauleyData and Analytics ManagerCommented:
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
 
polaris101Author Commented:
@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
 
Ryan McCauleyData and Analytics ManagerCommented:
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
 
polaris101Author Commented:
@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
 
David ToddSenior DBACommented:
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
 
polaris101Author Commented:
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
 
Ryan McCauleyData and Analytics ManagerCommented:
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
 
polaris101Author Commented:
UPDATE (cont) : Also, last night I uninstalled a MS System Center Op Mgr agent, and disabled an Ahsay backup service.  
0
 
polaris101Author Commented:
@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
 
polaris101Author Commented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.