4

We will be modifying a SQL Deployment in our Hosted Environment. We have a solution which is currently configured to use one Application (and not SQL) System Database (which holds Security and System Level Configurations for our Application) and numerous independent Company Databases, each used by different users (originating from independent Organizations hosted in our Environment). We will be changing this deployment, after which each Organization will have its own System Database (instead of using a unique one for all Organizations). Thus, each Company Database will now have an associated Application specific System Database.

To summarize, before the deployment let us suppose that a Server had 100 Company Databases and one System Database, it will now have 100 Company Databases, plus 100 System Databases. Thus, the number of Databases will have doubled. Meanwhile, the number of transactions on the SQL Server will be identical. As an example, upon logging into a Company Database, a user will log into its associated System Database first in order to verify its Application specific Security Role for instance, instead of accessing the shared System Database as before. Hence, the Number of transactions launched against the System Databases will remain the same (as when there were only one of them), but the number of Databases will double.

Considering that the Number of Transactions will remain the same, but that the Number of Databases on the SQL Server Instance will double, how should we determine how much more Memory should we be adding (if any) to the Server?

Thank you.

JF0001
  • 163
  • 5

1 Answers1

2

"Should I add more memory to the server" is a tough question to answer. Your box might be coping "just fine" with what it has, or you may find that tuning the system in other ways (expensive queries, for example) would be more effective. There are a number of resources that you should consider, including Jon Kehayias' How much memory does my SQL Server actually need, as suggested by Aaron in the comments.

Your system may have other bottlenecks apart from memory, particularly if there are a lot more databases, such as disk reads/writes. Writing to log files, reading from data files - it all adds up. But as far as memory is concerned, if you think your current system could cope with less RAM, then it should be able to cope with more databases and the same RAM.

Rob Farley
  • 16,324
  • 2
  • 39
  • 61