6

We've got a Windows 2008 R2 server running SQL Server 2008. All of a sudden, the SQLServer process is refusing to go above 20% CPU usage. As of last week, when running a heavy query against the db it would rise to 100% usage as I would expect. We've had this server for a while and it seems strange that it would just suddenly have this limit. This limit is causing our queries to take a lot longer than they normally would. No one has (knowingly at least) made any changes to the server configuration.

After a bit of investigation, I discovered the sys.dm_os_sys_memory view. This shows 'available physical memory is high' bu at the same time the available physical memory is 339552kb where as the total is 4193848kb. It is worth noting that this is a virtual server running on vmware.

Is there a setting somewhere with in SQL Server that sets the maximum CPU usage? I've found the settings in resource governor, although this is currently off as it always has been.

We have recently started using Spotlight for SQL Server by Quest Software. It's playback database was located on this server for a short time this morning, I first noticed the problem shortly afterwards, although I hadn't been doing any queries prior to this so I don't know if this is the point at which the problem began, however the database was working as expected on Friday afternoon. The Windows log shows that the following settings were applied to the SpotlightPlaybackDatabase when it was created.

  • 02/21/2011 08:45:02,spid60,Unknown,Setting database option TORN_PAGE_DETECTION to ON for database SpotlightPlaybackDatabase.
  • 02/21/2011 08:45:02,spid60,Unknown,Setting database option MULTI_USER to ON for database SpotlightPlaybackDatabase.
  • 02/21/2011 08:45:02,spid60,Unknown,Setting database option READ_WRITE to ON for database SpotlightPlaybackDatabase.
  • 02/21/2011 08:45:02,spid60,Unknown,Setting database option AUTO_UPDATE_STATISTICS to ON for database SpotlightPlaybackDatabase.
  • 02/21/2011 08:45:02,spid60,Unknown,Setting database option AUTO_CREATE_STATISTICS to ON for database SpotlightPlaybackDatabase.
  • 02/21/2011 08:45:02,spid60,Unknown,Setting database option ANSI_WARNINGS to OFF for database SpotlightPlaybackDatabase.
  • 02/21/2011 08:45:02,spid60,Unknown,Setting database option CONCAT_NULL_YIELDS_NULL to ON for database SpotlightPlaybackDatabase.
  • 02/21/2011 08:45:02,spid60,Unknown,Setting database option RECOVERY to SIMPLE for database SpotlightPlaybackDatabase.
  • 02/21/2011 08:45:02,spid60,Unknown,Setting database option QUOTED_IDENTIFIER to OFF for database SpotlightPlaybackDatabase.
  • 02/21/2011 08:45:02,spid60,Unknown,Setting database option AUTO_CLOSE to OFF for database SpotlightPlaybackDatabase.

Could any of these settings changes modified the settings applied to the whole server?

Edit #1: Managed to fix this problem by restarting sql server, not sure what the problem was in the first place. Despite the problem being solved, I still have some io issues to sort out that I wasn't previously aware of.

Edit #2: The problem re-occurred. Solution was to turn off Trace Analysis in Spotlight on SQL Server, this was what was dragging everything down.

2
  • How many CPUs does this server have? In management studio, if you right-click the server and go to properties, can you post what you have under Memory, Processors, and Advanced?
    – Paul Kroon
    Feb 21, 2011 at 18:35
  • Resource Governor limits only kick in when there is contention for the resource. I guess that's moot since it's not turned on.
    – Jon Seigel
    Feb 21, 2011 at 19:48

5 Answers 5

1

Check the sys.dm_os_waiting_tasks and see what the wait resources are. Basically look at the wait_type and see what's in there. Run this query and post the results back.

select wait_type, sum(wait_duration_ms) sum_wait_duration_ms, avg(wait_duration_ms) avg_wait_duration_ms, count(*) waits
from sys.dm_os_waiting_tasks
group by wait_type

You might be suffering from a similar problem to what I talked about this morning on my blog.

2
  • This problem re-occurred today, turns out you were right. Soon as I turned off SQL Analysis in Spotlight, all our problems went away. Have changed you to answer. Irritating how a tool that is supposed to help you monitor your servers ends up breaking them!
    – hermiod
    Feb 24, 2011 at 13:20
  • That trace can be a killer. Most people probably don't need that feature turned on all the time, and fortunately it is easy enough to disable when you don't need it enabled.
    – mrdenny
    Feb 24, 2011 at 17:46
3

You can't manage CPU usage but you can manage CPU Affinity. That is, has someone restricted SQL Server to using a single CPU?

In the same vein, has someone changed the global maxdop setting? This limits all query to one CPU but any single query will run on one of the available CPUs

3

Assuming there hasn't been a configuration change to CPU affinity or MAXDOP as mentioned by gbn, there are a couple of possibilites.

The first is that the query plan for your query has changed because the distribution of the indexes or underlying table data have changed significantly. Try to optimize or rebuild indexes on the underlying tables.

Secondly, you might now be I/O-bound, either reading data from your main database file or working in tempdb (where SQL will store intermediate parts of the query if it is too big for RAM). Use perfmon, and monitor avg. disk queue length. It should average less than your number of physical disk spindles in the server. If it shoots up during your "heavy query" while CPU remains low, the CPU is simply waiting for disk IO, and therefore can't run at 100% doing useful work. If this is the case, you have a few options: more RAM (to reduce the need to use disk), faster disk (SSD?), or optimize queries, indexes, and schema to reduce disk IO. The last option can have by far the greatest impact (literally improving things by a factor of 100 or more). But it can also be the most difficult, depending on your data structure and queries. Read up on SQL execution plans; buy some books.

2
  • I'll reiterate your excellent point that if disks are overloaded, one obvious and usual fix is to stuff the box with as much RAM as possible... no amount of tweaking the IO system can replace this (SSDs lack capacity unfortunately)
    – gbn
    Feb 21, 2011 at 19:09
  • I managed to solve the issue by restarting sql server service. However using spotlight has revealed that there are certainly io issues with this server, your comments and advice will help me greatly in fixing this.
    – hermiod
    Feb 23, 2011 at 9:53
1

One thing you can do is to see exactly what is happening to the process running the query. If you keep Monitoring the spids activity and see what is its most common wait type. You will probably find that there is a resource such as disk io that the spid is waiting on meaning the cpu is idling for the query until disk read/ writes completing.

0

This problem was solved by restarting sql server although I dont know what caused it in the first place. Thanks all for your replies.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .