Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 CPU usage

Author  Topic 

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2010-09-29 : 11:22:47
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

38200 Posts

Posted - 2010-09-29 : 12:55:31
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

22859 Posts

Posted - 2010-09-29 : 14:28:16
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

55 Posts

Posted - 2010-09-29 : 14:33:40
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

38200 Posts

Posted - 2010-09-29 : 14:37:19
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

22859 Posts

Posted - 2010-09-29 : 14:50:23
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

38200 Posts

Posted - 2010-09-29 : 14:56:55
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

55 Posts

Posted - 2010-09-29 : 15:48:41
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

55 Posts

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-29 : 16:24:49
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

22859 Posts

Posted - 2010-09-30 : 04:11:07
"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

55 Posts

Posted - 2010-09-30 : 07:15:07
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

22859 Posts

Posted - 2010-09-30 : 11:29:58
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

55 Posts

Posted - 2010-09-30 : 11:37:28
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
   

- Advertisement -