Our company currently hosts about 400 databases per SQL Server 2005 instance. 500-600 databases and we see performance start to deteriorate.
Database size ranges from 50mb to 40-60gb in the extreme, with probably an average of maybe a few GB.
What are some good metrics to collect and how would we get these to determine our bottleneck?
Is it a performance limitation of SQL Server to have too many databases? Is it all of the cached procedure plans for hundreds of stored procedures for hundreds of databases? Is it inefficient queries? Or a combination?
What is likely to help us increase the number of databases per server the most? All of the schemas are identical and the databases separate each customer's data. Maybe refactoring everything so we can combine multiple customers into one db and filter by customer, or optimizing our queries to the extreme? Or maybe its just a SQL Server limitation?