5

I have the following challange:

I have a staging database which holds a few big tables (on SSD) but I start to run out of space on the SSDs. I use SQL server 2014 BI-Edition (= Standard Edition for the relational engine) so table partitioning out of the box is not available. My approach would be the splitting of the data into separate tables and joining them together in a view.

The data itself is (mostly) written once and then only read from.

What would be a better solution of the two suggested ones below? Why?

Solution A: Use Filegroups to move data to different disks:

  • create a two new filegroup on a separate disks (non-SSD, RAID5)
  • create several tables (e.g. one per Quarter Per Year) and move the old data to those tables on some rules (e.g. move data older thant 6 months)
  • one filegroup is readonly and the other one is read write. More current data resides in the read write filegroup and the older data is on the readonly filegroup
  • create the same index (same columns) for all tables
  • use a view to have transparent access to all data

Solution B:Use a database to hold the historic data

  • Implemented in more or less the same way but addng a database (in the same instance) as an additional layer

Thanks for sharing your ideas and advice.

4

Your problem is apparently all about storage space.

Your SSD array is not large enough to hold all your history on SSD.

Creating another filegroup is a good way to separate the use of storage space. That way you control what goes onto the historic data disk array. (When creating objects on a filegroup other than default, you must be sure to specify the filegroup you are using of course. )

Depending on how massive the historic data is, you may not need to split the data by quarters, etc.

For example, you were running well on the SSDs until space became a problem. So, remembering that space is the problem, you could ask yourself questions like:

  1. Are my indexes well-designed for this workload?
  2. Do my queries require scanning all of the historic data? (Apparently not, according to your comments above.)
  3. Is the overhead of maintaining quarterly databases going to buy you significant query improvements? (Especially if you will need data from more than one database.)

Making things simpler and well-designed should be a first goal. But, if that is not sufficient then you should add layers as they are needed. Keeping performance statistics will help you measure the benefits.

There are plenty of web sites with help. Here is one set of queries by Glenn Berry: http://www.sqlskills.com/blogs/glenn/category/dmv-queries/

  • Thanks for sharing your experience. Exactly my problem. Maintaining another database was purely from a space and backup perspective but I do not expect a query degration as it is the same instance (compared to using another filegroup) – dim Jun 3 '16 at 19:46
  • @dim A new filegroup is still part of the same database. – RLF Jun 4 '16 at 4:29
2

I've tried both approaches for my data warehouse, but I've gone with option B, creating quarterly databases to hold historical data. In my situation, my source messages are written once, read once for ETL purposes and archived for legal purposes. By archiving them into quarterly DBs, I've been able to 1) deploy them to inexpensive SATA storage, 2) flag them as read-only for data protection and performance since it cuts the locking required, and 3) improve my RTO as I can restore a smaller 'current' DB faster than having to wait for all my historical DB to restore as well before recovery. Instead of the view using UNIONs, you may want to use some application-based logic to SELECT from the correct DB depending on the criteria.

  • Thanks for sharing your experience. How did you flag the database read-only? Setting the database read-only or setting individual filegroups read-only? – dim Jun 3 '16 at 19:55
  • @dim, you can either issue a ALTER DATABASE...SET READ_ONLY command or change the settings under Database Properties and go to the Options pane (look for Database Read-Only near the bottom) – MattyZDBA Jun 3 '16 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.