setting CPU usage for certain databases?

  • I have a SQL server 2005 instance running on an HP server with quad core processors and 8 gb's of memory.

    I've set SQL server maximum memory allocation to be 4gb, so that it doesn't get overloaded. However, some jobs that take a long time can peg the CPU's to 100% and it times out other queries that people try to execute.

    In SQL server, it shows 7 CPU's. Is there a way to specify how much CPU usage can be dedicated to certain tasks or for certain databases? In other words, I have 3 db's on this server, and 2 are important production applications, and one is not. Can I dedicate 2 CPU's to each production database, and then 1 CPU to the less important one?

    How can I ensure that my users can run reports at all times while developers running clean-up jobs are limited in the resources they can use?

    Any help is greatly appreciated.

  • In SQL 2008 there is a resource governor to allow you to handle hardware resource allocation at a more grandular level. This is not available in SQL 2005.

    In SQL 2005, you could move the offending databases to a new instance and set processor affinity for the instances.

    I would recommend upgrading to SQL 2008.

  • The only way you can do that on SQL 2005 is to install a second instance, put some of the DBs on the second instance and change the CPU affinity.

    2008's resource governor allows for restricting CPU usage, but there's nothing similar in 2005.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • You can take a look at Maxdop to limit the number of CPUs used for the Jobs.

    Are you saying that the Developers access your production DB where your clients and users are running reports and doing transaction?

    That is definetly a bad idea.

    -Roy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply