2

Our current legacy system was built using multiple distinct databases (about ten of them). These are all part of the same discreet system, and a large number of SPs and functionalty span multiple databases. There are also key relationships that span (for example, a header table may be in database A with history, etc. in database B).

When deploying multiple copies of our app to the same server therefore, we have to use multiple instances (because the database names are coded into so many sprocs).

We're evaluating the idea of taking these ten databases (about 30gb total with individual sizes ranging from 100mb to 10gb) and merging them into a single database.

Currently, we have our databases spread accross multiple spindles for better IO. The question I have is whether or not there is any performance loss or benefit of having 10 different databases vs. 10 different database files?

i.e. rather than having three databases (A, B, and C)

Disk D:  A.mdf (1gb)
Disk E:  B.mdf (4gb)
Disk F:  C.mdf (10gb)
Disk G:  A_Log.ldf, B_Log.ldf, C_Log.ldf

have one database (X)

Disk D:  X1.mdf (5gb)
Disk E:  X2.mdf (5gb)
Disk F:  X3.mdf (5gb)
Disk G:  X1_log.ldf,X2_log.ldf,X3_log.ldf

Thanks! -Bob

gbn
  • 6,009
  • 1
  • 17
  • 21
Bob Palmer
  • 202
  • 2
  • 6

1 Answers1

2

Unless the load over the databases is very even you are likely to see better performance if you use multiple files in a file group and a single database over multiple databases spread out at various disks. This is due to the load distribution code in ms sql, which typically performs much better than trying to manually choose disks for each db.

A short introduction can be found at http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/17/948.aspx, but there is a lot more to it.

You should also have a look at SQL Server 2005 / 2008 - multiple files / filegroups - how many? Why?, which has a good discussion about the usage of multiple file groups.

Oh, having all your log files on the same disk is likely to give you an IO bottleneck, unless that disk is blazing fast and you write load is low. Be careful there.

In the end, check your IO queues. If you don't get stuck waiting for IO there is little point in worrying about your disk performance. The change may be possible to motivate simply because the db design is much cleaner.

Community
  • 1
pehrs
  • 8,749
  • 29
  • 46