Splitting a database into multiple databases

Question

I have a database which contains large amounts of measurements (20+ instruments, each recording 60 measurements per second, each measurement containing 10+ complex values). All measurements are stored in a single table, normalized, and indexes are used to allow filtering by instrument, time and other columns.

Needless to say, every 6 months, the database gets so large that scheduled index maintenance tasks take more than a day to complete. So far, I've had to turn off the db twice, for almost two days, to run scripts which would backup and then delete old data in order to "refresh" it.

My colleagues suggest a (logical, IMHO) modification to physically separate the database into multiple databases, by instrument and time. Furthermore, different clients will access different instruments, so keeping each instrument separated will simplify multi-tenant scenarios (providing backup to customers, migrating to other machines, etc.).

In other words, I would go from this:

  Big database
     - AllMeasurements table

to this:

  Instrument001 database
     - Measurements-2012-01 table
     - Measurements-2012-02 table
     - Measurements-2012-03 table

  Instrument002 database
     - Measurements-2012-01 table
     - Measurements-2012-02 table
     - Measurements-2012-03 table

Since my DAL is abstracted through the repository pattern, it would be easy to swap the underlying implementation to switch databases and tables on demand, so coding this shouldn't a problem.

But I am now wondering if this is really the best way?

Does it make sense?

[Edit] I'm using SQL Server 2008, if that's relevant.

Answer 1

If the problem is this single table getting huge, you should consider partitioning it (by date, since your data seems to be time-based).

This way the SQL engine query planner may be able to ignore some partitions (if they contain data too old to appear in your query results) when querying and thus perform better.

Additionally, partitionning will be handled transparently by your database engine, so you have nothing to change in your code.

Another advantage would be that you can imagine to move the 'old' partitions, containing data that is rarely accessed, to slower (and cheaper) storage.

You can also set up your indexes to be partitioned so you can only rebuild a part of an index: http://msdn.microsoft.com/en-us/library/ms187526.aspx

Index partitioning is a different feature from table partitioning, it can avoid you to have to rebuild an entire index if it's not needed.

Answer 2

If the tables are continiously growing it is best practice to seperate/partition them. I would however not implement a database for each instrument, rather a new table for each instrument which stores the measurements. You could also incorporate a MeasurementDates table that references the Instrument, which could reduce the size significantly rather than having a repeating Measurements-2012-NN table for an individual instrument.

http://msdn.microsoft.com/en-us/library/ms188730.aspx

http://msdn.microsoft.com/en-us/library/ms188730(v=sql.105).aspx

Answer 3

I have worked with simular database structure. (Big databases). As I understand the data you fill in is static. First of all. you must be sure that your indexes is at an absolut minimum. And if you are not already doing it, then try out different fill factors for your indexes (fill factor=additional space in the index to "grow/work").

One way to handle the re-indexing problem in big databases, is not to re-index it, but to create another same index, and delete the old one. (unless your database handles re-indexing without locking the index?). And dont use clustered index unless data is ascending values.

I dont know exactly how you access your data, but making more databases (one per month) could be a solution. When gathering data, you could gather the static dataresult in another database. Let say you are adding up results per day in another database. And stats pulling queryes from that database.

(Sorry, english is not my main language)

Answer 4

At that rate, you're adding roughly 500 million rows per month, right? 6 billion rows per year. To my way of thinking, this puts you firmly in very-large database (VLDB) territory.

VLDB is really a special case. dba.stackexchange.com doesn't even have a tag for it. (I'll try to add one. You'll become our expert.) You have to make lots of really sound decisions to get great performance.

When I hear about VLDB problems these days, my first question is always about hardware. Is this beast running on hardware that's appropriate for a database of that size? A lot of companies kind of drift into VLDBs from a modest start. A lot of companies. The biggest jump in performance might come from new decisions about hardware.