2 databases in sql server 2005 “Production Server”

Question

Can 2 databases decrease the performance in a sql 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 standart 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 30 GB. 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 to use a max of 13 Gb on the 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.

Thank you in advance,

Regards,

Answer 1

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.

Answer 2

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.