2

Is it possible to specify a min/max amount of system resources (Disk IO, Memory, CPU cycles/s) available to each database instance inside a SQL Server 2005 server?

This would be the analogue of QoS settings for SQL Servers.

4 Answers 4

3

No. They all use the sqlservr.exe process which is the SQL Server Instance.

If you need to manage database level resources, then you need a separate instance

Generally, I've found that multiple databases on the same instance usually balances out. Each database has it's own usage pattern, load pattern, number of users etc, and SQL Server manages buffer pool, plan caches, memory etc based on this load very well.

If you have a database with some poor code or hogs resources, then I'd suggest it's own hardware or at least instance.

3

Ricardo - your question is a little misleading. There's two ways to read it.

If you mean databases inside a SQL Server instance, then you can't do that with SQL Server 2005. SQL Server 2008 introduced the Resource Governor, which sort of achieves what you're after, but not quite. It can throttle memory and CPU, but not disk IO.

If you mean different SQL Server instances on the same computer, then you can kinda do it. Each instance has its own min and max memory settings. In SQL Server Management Studio, right-click on the instance name in Object Explorer and click Properties. From there you can set the min and max memory that each instance will take. You can also set CPU affinity masks, which will let you pick which CPUs an instance can use. If you had a 2-socket, quad-core server (8 total cores) you could assign one instance to two particular cores, and another instance to six cores. Unfortunately, if one of the instances is idle, the other instance won't be able to take advantage of those idle cores.

Segregating disk IO between instances gets even more complicated. You can use storage multipathing to separate traffic between the instances if you're willing to put a lot of time into storage design and pathing. You can assign one instance's data and log files to a particular array, and then only set up paths to that array through specific host bus adapters (HBAs). If that doesn't make sense to you, then get your storage administrator involved, and if you don't have a SAN admin, then it's more work (and money) than you want to tackle.

2

With Named instances of SQL Server (i.e. more then one sqlservr.exe process) you can restrict CPU by using processor affinity to bind the SQL Server scheduler for an instance to specific CPU's. There is also a max server memory setting that is also specified per SQL Server instance.

I don't think there are any server-wide setting that can be set with disks, but at least you can create separate LUN's for your databases to reside on & partition the load that way.

In SQL Server 2008 there's resource governor which allows you to set very sophisticated rules for workloads and the amount of resources they can consume that can be per database. Maybe an upgrade is in order?

4
  • CPU and memeory for the SQL Server Instance: not the database.
    – gbn
    May 31, 2009 at 12:41
  • I thought Ricardo meant named instances of SQL Server? i.e. the default MSSQLSERVER instance & then named instanced May 31, 2009 at 12:45
  • I assumed "to each database instance inside a SQL Server 2005 server" means databases on the instance.
    – gbn
    May 31, 2009 at 12:47
  • I think you might be right :( Ricardo, can you please clarify? May 31, 2009 at 12:56
0

You can potentially do some of this by using VMs instead of instances. Put a separate instance in each VM as the hypervisors tend to have better tools for managing resources.

The Resource Governor is a good start, but it's a gross limitation so far. It's a good start, as Brent mentioned, but it doesn't cover I/O. Given the read-ahead algorithms, I'm not quite sure how you would make this easy for someone to manage.

You must log in to answer this question.

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