how many db's on one server

Question

how many db's on one server

SQL_NuB

Hall of Fame

Points: 3666
More actions
January 15, 2009 at 11:32 am

#200119

This is more of a performance type question. On one SQL 2005 Server, how many db's should one have on the server before I start seeing performance issues?
Meaning, is there a recommended number of db's on one server?
My server is configured as such:
OS: Windows 2003 server
CPU: 4 Quad 2.8 ghz dual core
MB: 4 gig
HD: 400 gig (non OS drive)
There are at times that the SQLserv.exe is using up to 1 gig of memory. The db server currently has
29 databases, in which 10 are sharepoint databases.
How many db's should reside on one server before another server/instance should be used or even load balancing be used so that there is no possible performance issues?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

It's not really a question of quantity of databases. It's a question of traffic and load vs server capability.

There's a huge difference between one database being hit by 10-thousand users all day every day, and twelve databases that have 20 users total.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ravikanth. Hall of Fame Points: 3472 More actions · Answer 2

a sql server 2005 can have 1 default instance and 50 named instances.There is no such limit for db`s as far as i know.

Now the question is size of the db,users,hits/sec(traffic)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

I agree with Gus... it doesn't matter how many databases you have on a given instance of SQL Server... it's resource usage that matters. How many users are logged in? What are they doing to SQL Sever while they're logged in. Is replication running? Are scheduled jobs running? What are they doing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 4

Agree with the others the limiting factor is the load the databases put on the server. So you need to baseline your performance stats and every time you add a new database re-baseline so you know when you've reached a point where just one more database will kill the server(including just plain old disk space). Wafer thin mint anyone? 🙂

Also consider some databases just don't fit together because they support different business areas which have different support requirements in terms of SLAs versions etc.

and lastly when you consolidate every little SQL upgrade becomes a major event with a potentially high impact.

---------------------------------------------------------------------

Ross McMicken SSCarpal Tunnel Points: 4436 More actions · Answer 5

We have servers with 100+ databases that are low volume, and never have any performance issues. We have other servers with 2 databases for one app (vendor product thsat is configured that way), and they still get hammered on occasion. Number of databases is not too relevant. Usage is.

hema kumar-479337 SSC Veteran Points: 279 More actions · Answer 6

We dont have any limitation on number of databases a SQL Server should have. We need to analyse carefully identify the offender. Is the performance is same across all the time. are facing performance issues at specific time ?

It is specific time then checkout what the jobs running and schedule it in offpeak hours.

Another observation i have found with sharepoint is, with too many "content" databases having in same server is always causing some performance issue due internal logic written in front end sharepoint application.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

As others have indicated, the limiting factor is the load the applications put on the server. For example, I support a SQL Server 2000 that has over 6,500 databases for the Community Reinvestment Act Wizard application http://www.pciwiz.com/solutions/crahmda.asp. There is very little load and no significant performance problems.

This SQL Server has encounter three problems:

Database maintenance can fail due to insufficient virtual address space with the workaround documented at http://support.microsoft.com/kb/316749

With a new database backup file being created each day and old backup files deleted, the NTFS file system becomes corrupted and must be reformatted. Historically, this needs to be done about once per year.

Each database backup is recorded in the msdb database and as the sp_delete_database_backuphistory stored procedure to purge old information is not very efficient (contains a cursor), the database size can easily grow to 50Gb. The solution was to write a more efficient procedure.

SQL = Scarcely Qualifies as a Language

ashepard SSC Rookie Points: 37 More actions · Answer 8

Like everyone else has written - it depends.

Look at CPU usage - are you at or over 80%?

Look at DISK time - is it at 80% or higher? 80% is used because disk performance drop off quickly as you approach 90 to 100%.

Also with disk - what is your write queue length and average write queue length? Are they over five and if so how often? Good sign that SQL can handle more but the disk drives can not.

Look at Memory - do have have at lease 30% free memory?

We have put multiple databases on one server our only problem has been tracking who is hogging CPU and Memory once things go bad. Disk usage is not too hard as files can be moved to other drives. We have not had a bandwidth issue with the network card.

Hope this helps.