SQL Server Performance

Number of Databases (schemas) in one sql server instance. Is there a sweet spot or any normal limit?

Discussion in 'SQL Server 2005 General DBA Questions' started by zekmoe, Jul 16, 2008.

  1. zekmoe New Member

    Coming from an Oracle background, this may seem like an illogical question, but is there any limit or sweet spot in the number of databases, or schemas as they're refered to in Oracle, on a typlical sql server install? Not taking into account connected users or query activity, is it ok to have dozens, and possibly hundreds of separated object collections within a single running sql server environment? Any issue with number of objects, data files or log files? Any point where the catalog gets cluttered? We're talking a relatively low use and smaller DB (probably under 1TB log and data total) and many, many small systems with a few in the 100g size. Given a reasonable server (4 newer faster CPU engine and 16g memory), would there be any considerations in management, backup and the general stuff that's done with a many db in one instance SQL Server install? Is there a point where multiple machines would be recommended? Again, not based on user access or general load, but in object management and general server management.
    Not a downstroke, fistpicker.
  2. madhuottapalam New Member

    In record, the maximum number of database one SQLServer instance can support is 32,767 and i don't think anyone has ever reached this limit. Check this link for maximum capacity specification : http://technet.microsoft.com/en-us/library/ms143432.aspx
    There are few scenario where you may go for separate instances in sql server. If you keep your OLTP and OLAP databases on the same server, you can not control the resource allocation in sql server (prior to sql server 2008 . In sql server 2008 resource governor solves this issue). If you want to allocate more resource to OLTP then its not possible in SQL Server 2005 and earlier version and this may be one of the reason to keep different instances. Another reason coming to my mind is Tempdb Contention. If you have many databases which uses tempdb extensively and you keep all the databases in one instance then you will have tempdb contention since tempdb is common for all the databases . So to avoid tempdb contention you may go for different instance.
    One more reason coming to my mind is more political than technical. In sql server SA (system administrator) is the supper user and there may be political reason where you can not share this password hence you go for different instances.Madhu
    SQL Server Blog
    SQL Server 2008 Blog
  3. zekmoe New Member

    If there's tempdb contention (again, oracle guy here trying to help out) can a second temp area be created for each installation, or is only one per instance allowed? Say I have 100 DB's within the SQL server instance, but one or 2 in particular are the lion share users. Can I create a temp area designated for them to use? Disk space not an issues and all SAN
  4. madhuottapalam New Member

Share This Page