Sql-server – throttle individual databases instances on one SQL Server 2005 box

performanceperformance-tuningsql serversql-server-2005

I am running a very old server that has a database that is being permanently retired within 14 months. My company will not invest anymore in hardware or software for this system as we are moving to a whole new system that is hosted within 6 months. I have only found reference to this Exchange Entry, but that is about servers and potentially using Virtual Servers.

My server services about 500 users, of which roughly 100-125 are concurrent during any given time of the work day. There are four 1.5 hr periods of very heavy activity during the day, and as the DBA and developer assigned to work with this server, I have about 1 hour in the morning and overnight jobs to do anything that could seriously impact performance for the machine. There are 2 active production databases on it that will be used until the change over occurs in 3-6 months, and a development instance that I use for testing/fixing issues, since this is the only SQL server left in the organization. (For those DBA's cringing at this point; All your DBA concerns about safe practice, let alone best practice, backups and DR, are valid and I have raised them, but they REALLY will not spend anymore on this system or the infrastructure. So a new box is Moot.)

So, I am left with the desire to throttle (if possible) the two production databases to use no less than 80% of the resources, and give me a 0-20% usage so that if I run something like a DBCC command on the DEV area, it does not chew up the resources and give me the rash of 50 phone calls from the production users 5 minutes later? I am looking for the ability to throttle so that I can do the data preparation and extraction testing as we migrate to the new system with out messing too much with the day to day business activity.

Best Answer

You could use the Windows System Resource Manager (WSRM) feature for this. It is not present in Windows starting from 2012 R2, but since this is an old SQL 2005, I would expect the OS to be old-ish as well.

I've never used it myself, but, though a bit complicated, it might do the trick for you.