SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 CPU usage
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nickfinity
Yak Posting Veteran

USA
53 Posts

Posted - 09/29/2010 :  11:22:47  Show Profile  Reply with Quote
Hello,

Don't know if this is the right place or not, but here goes. I occasionally help a guy with his site and a few months ago he started seeing SQL Server (2000 at the time) was using up all of the CPU (it's a quad core machine). He ended up upgrading to 2008 and everything has been working fine until yesterday. Have the same issue again. SQL Server is maxing out the CPU.

It's incredibly slow to try and look in the management studio at anything. I'm fairly inexperienced with profiler and other debugging tools. When I stop IIS the usage goes way down, but I don't know how to figure out what queries are being run or what SQL Server is doing to help me track down the problem in the code. What's my best bet for tracking this down?

Thanks for any help, I sincerely appreciate it.

Nick

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 09/29/2010 :  12:55:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
High CPU utilization is typically due to index/table scans as a result of missing indexes or out-of-date statistics.

You can use SQL Profiler to see what queries are being run. You'd be interested in long running queries (that are not normally long running) and high reads.

But I'd start with the missing indexes report (I use the second query): http://sqlserverpedia.com/wiki/Find_Missing_Indexes

Be very careful with the output. Only consider those indexes with an impact of greater than 1 million. That report has a tendency to recommend wide indexes and duplicates or near duplicates, so it takes a trained eye to figure out what ones to try.

Also, how often are statistics being updated?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/29/2010 :  14:28:16  Show Profile  Reply with Quote
If the machine is not dedicated to SQL Server it might be that SQL Server has grabbed all the memory, and not allowed enough for other processes ... and then the O/S will start paging the memory out.

SQL Server will not grab all the memory initially, so it will take some time (depending on size of DBs and how busy the server is) before it does take it all - which might account for it taking a while before you hit trouble. If you restart SQL Service and everything is fine for "some time" then that may be the cause.

In any case you should stop SQL taking all memory by leaving 2GB for the O/S on a dedicated server - thats the minimum twiddling you should do (actual algorithm needs to take into account number of CPU sockets, and other stuff ... but 2GB should do for starters)

If its not a dedicated server reduce the amount SQL helps itself to even further.

If Indexes are not being rebuild regularly, and ditto Statistics, that's a likely candidate too as Tara has said.

I am less sure about a poorly optimised query - I would expect that to show itself as soon as the query is first run, but of course if an abnormal load of "poorly optimised" queries hits the server that will cause meltdown.

One of the issues with IIS (assuming a reasonable number of connected users) is that if the server becomes slow then queries will time out, or users will get impatient, and then they will all hit REFRESH - which will seriously exacerbate the problem . On our Web-hosted databases we use CPU activity monitoring to lock out all new user sessions when SQL CPU activity goes over a preset threshold.
Go to Top of Page

nickfinity
Yak Posting Veteran

USA
53 Posts

Posted - 09/29/2010 :  14:33:40  Show Profile  Reply with Quote
Thanks for your help Tara. I ran the second query and I the max impact of the four listed is just under 3000.

I have no idea about the statistics. If I had to guess I'd say they are never being updated (at least as part of anything that I've ever seen). Is there an easy way to find out?

Thanks,
Nick
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 09/29/2010 :  14:37:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
You don't have any missing indexes if the impact is that low.

I suspect you've got a bad execution plan then. See if running DBCC FREEPROCCACHE clears the problem when CPU is high. If it does, then let us know as we'll need to work on that.

I update statistics daily or weekly via a SQL Agent job.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/29/2010 :  14:50:23  Show Profile  Reply with Quote
I think that

EXEC dbo.sp_updatestats

will update statistics on all tables.

Index Rebuild, on all tables' indexes, would be better (that will also update all the statistics on those indexes), but it will interfere with online users - so best to run during a quiet period. If you are using Full Recovery Model make sure that the Transaction Log is backed up frequently during the Index Rebuild (every 2 - 5 minutes would be a good interval) - otherwise you run the risk of bloating your LDF file (not the end of the world unless you are tight on disk space, so if this is all Greek to you, and you have plenty of disk space, then just concentrate on getting the indexes rebuilt)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 09/29/2010 :  14:56:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
Here's how my production systems are setup: http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

I remove the @resample part on large databases.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

nickfinity
Yak Posting Veteran

USA
53 Posts

Posted - 09/29/2010 :  15:48:41  Show Profile  Reply with Quote
Thanks to both of you for your help. I ran DBCC FREEPROCCACHE and no change in the CPU usage. I did also update the statistics. I did look through the profiler and I'm seeing a query being run repeatedly looking for an invalid item (and that query is taking quite a while). Now I have to figure out the source of that query.
Go to Top of Page

nickfinity
Yak Posting Veteran

USA
53 Posts

Posted - 09/29/2010 :  15:54:25  Show Profile  Reply with Quote
Thank you for sharing how your production systems are setup. That is very helpful.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 09/29/2010 :  16:24:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome.

You can add additional columns to your Profiler trace to help track it down. I always add the hostname column when troubleshooting things like this. Check out what other columns are available in the trace to help you with this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/30/2010 :  04:11:07  Show Profile  Reply with Quote
"I have to figure out the source of that query."

If its is running an Sproc you could change the Sproc to ERROR if it encounters that particular parameter / scenario - assuming the user would then Shout!

You should be able to select columns in Profiler to show the "user", but if its a website then probably every query will be from the same "user" so Profile won't help much.
Go to Top of Page

nickfinity
Yak Posting Veteran

USA
53 Posts

Posted - 09/30/2010 :  07:15:07  Show Profile  Reply with Quote
After doing some more digging I'm not sure that query is the source of the problem or not. It shouldn't be running, but it's taking the same amount of time as other queries (which is quite a bit since I assume it can't get at the processors to execute).
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/30/2010 :  11:29:58  Show Profile  Reply with Quote
Profiler has a template for Long Duration queries. That might help identify a culprit, but as you say: when the CPU is overloaded everything runs slow so its hard to see wood-for-trees. A fast query which is running very frequently (perhaps far MORE frequently than normal) would be a problem too.

It would help if you had base line Stats from Profiler - frequency and duration of the most commonly occurring queries, then it would be easier to see if something was running slower-than-normal, or much-more-often. Not sure how practicable that is though?
Go to Top of Page

nickfinity
Yak Posting Veteran

USA
53 Posts

Posted - 09/30/2010 :  11:37:28  Show Profile  Reply with Quote
Unfortunately I don't have a baseline. There are a lot of queries running, but I don't know if it's normal or not. One thing that sticks out to me though is that when I first open (actually I had it paused, cleared it, and then restarted) Profiler I get 670 "ExistingConnection" records. They are almost all using a login that is used exclusively on the website.

Thanks again for all of your help, I sincerely appreciate it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000