Maximum Recommended Databases

I've read MSDN that the theoretical maximum number of databases per
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:

>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data fo
r each customer (or
whatever differentiate these databases) obviously need to be in separate tab
les, per customer. So,
data for one customer is on a separate set of pages compared to some other c
ustomer meaning data
retrieved for one customer cannot be used when querying from some other cust
omer. But the same thing
would happen if the same set of table were in one database (except for very
small tables where mixed
extents come into play). But if this is re-modeled and all customers share t
he same table, we have a
different situation...

> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many datab
ases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file han
dles). And when a
database is auto closed, data in cache is removed (I know for sure this appl
ies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed
and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each d
atabase. Even if we
don't use stored procedures, we won't be able to re-use cached plans across
databases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.
4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!

>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model
after coalescing into
one database.

> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, t
hen I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of
one per database. If
procedures are not used, then chances are that we mostly have exact text mat
ching of the query
string in cache, and re-use would be minimal even if all is in the same data
base.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.
4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>