Can 2 databases decrease the performance in a SQL Server 2005 production server?

Server specs:

Dell Power edge 2950, 2 Quad core Xeon, 16 GB of RAM, 3x73 GB SATA hard drive hot plug, Windows Server 2003 R2 Standard Edition 64 bits and SQL Server 2005.

Our accounting/service software company asked me to make a second database "test", basically is a copy of the live database which is 30Gb. I don't have enough disk space on my server. The live database is on a second partition and 42.2 free space. I have setup SQL Server to use a max of 13Gb RAM and lock pages in memory, leave the rest of memory ram to the OS. I'm concern that to have a copy of the live database in the same server will have a high degradation in performance and to start dumping memory.

  • This is off-topic, but the mere existence of another database won't hurt anything. Did you know there are already at least 3 other databases on your server than yours as it is? Jan 31, 2013 at 15:21

2 Answers 2


Having a test database on a production instance sounds like a very bad idea to me, even without taking performance into account.

However, a second database on the instance does not consume resources per-se. It's the user or application activity that drives load to the instance. Obviously, you will need storage to accomodate the data and log files and that could degrade the NTFS performance if the free space on the volumes goes lower than 20%.

Other than that, buffer consumption and CPU/disk/network time depend on activity, not on the database existance alone.


Within reason (I mean "less than thousands", and you are no where near that) the number of databases should not affect the performance of the server. I have seen people claim to run thousands of databases on a single instance of SQL Server, and the performance of the user databases hasn't been a problem. (On the other hand, looking at those databases with SSMS is a problem, as is trying to manually manage all of them.)

Very roughly speaking the performance of the server is governed by the amount of data that is accessed in those databases and how much memory the server has. Assuming that you have enough disk space to store both databases and that you never access the "test" database, the production database performance should be unaffected. If you "sometimes" access the test database, the performance of the server could be affected at those times. You can mitigate that by testing when the server isn't busy, like in the evenings or on weekends.

All of the data in a database might not be regularly accessed. It is very common to have old records, "archives" and other sorts of stuff which remains in the production database by isn't regularly returned by queries on a day-to-day basis. In many databases, this "cold" data might be much larger than the amount of "hot" data that is regularly accessed. If the amount of hot data is very small, you might not notice any performance problems when using the "testing" database even when the production database is being used full-blast.

To make things a bit more complex, poorly designed queries and database tables might be inefficient, and return data that really should be "cold", or cause table scans.

In short, if the server can hold all of the hot data (from production and testing) in RAM, then you will probably be ok, or at least not much worse off than you are with only the production database in use.

Back to your direct question: Will adding more load be a problem? The first thing to do would be to look at the performance of the server now. Do you have performance problems? Are those problems caused by a lack of RAM, a lack of disk I/O capacity or a lack of cpu power? There are many, many guides on the internet and StackExchange to help diagnose where your currently bottlenecks are.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.