Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is a follow-up to an earlier question. I have a SQL Server 2008 R2 Standard server, that holds a single database, which itself has almost nothing except a large table.

The table is 100+ million rows (35 columns) and growing at around 250,000 rows per day. We need all the data to be "online", and most of the columns need to be searchable in some fashion. The vast majority of activity on the table is reading; apart from the new data being INSERTed during the day, there's no need to change anything.

Users perform a range of queries on the table, ranging from simple look-up-a-record requests to pulling tens of thousands of rows based on a range of criteria. We only have limited control on the queries that are run, and performance is starting to suffer, even with indexing.

A big part of the problem is disk I/O, which we're addressing by retrofitting a SSD-based array. As all database files will be on this new array, the consensus is that having multiple database files won't make any difference, but that splitting the table up into separate tables might be the way to go.

I'm now puzzling over what would be the best approach to this. Two ideas that I'm debating with myself:

  1. Split the table into "tiers"

    • A table containing the last week's data, which is the one being
      INSERTed into each day
    • Next table containing from last week back to 3 months previous
    • Next table containing from 3 months to 6 months
    • Next table containing anything older than 6 months

    I'd then "shuffle" the data down the tiers overnight (the database is only accessed 8am-10pm, so I have a window overnight to process data).

  2. Create tables for data ranges

    • Create a table for a data range - say per quarter. I'd then have the data INSERTing into the table 2Q2013, and then trip over to 3Q2013, 4Q2014 etc ...

    I could use filegroups to make older tables "read only" if this would improve performance.

Option 1 is the easiest for me to implement, but I'm not sure if this is a completely mad idea. Option 2 is a more work to implement and maintain, but if it's "best practice" for this kind of problem that it's the way I'll go.

Any and all advice or alternative ideas would be gratefully received - I'm away that these kinds of problems are best solved at design-time.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

I personally would go with your first option. If you use a `DELETE dbo.p1 OUTPUT INTO dbo.p2' pattern to move the data there is not a lot that can fail. Moving 250K x 3 rows that way in the timeframe you have should not be a problem either if you do it in batches with around 10K rows.

The advantage I see over the more common calendar based partitioning is that your "partitions" stay the same size. With the amount of data you are dealing with a calendar base partitioning approach would work very well in the beginning of the month and potentially significantly slower at the end of the month.

I have written an article together with Kalen Delany about the advantages of the "moving data from partition to partition as it ages" approach a while back in sqlmag: http://sqlmag.com/database-administration/using-table-partitions-archive-old-data-oltp-environments

That article is using the enterprise-only build-in partitioning feature, but you can implement it using handmade multi-table-partitioning too.

I would try to place the different tables (partitions) on separate drives. Because your data in the older partitions changes only during downtime, you could still mark those filegroups readonly during the day. Alternatively you can use TRANSACTION ISOLATION READ UNCOMMITTED or READ COMMITTED SNAPSHOTisolation. The later should speed things up on the "current" partition too, assuming you really do not ever update data. But even with updates it might help. Either way, make sure you test the performance in your environment. (In any event, do not use UNCOMMITTED in the active table/partition as you read queries might end up seeing half-written rows, depending on the data types you are using.)

share|improve this answer
add comment

My first question to your situation would be if the Standard Edition is a given or if you had the possibility to upgrade to Enterprise.

Within the Enterprise Edition of SQL Server 2008 R2 you would have the possibility to use Partitioned Tables.

Essentially, they do what you described as a manual process in the background.

Quote from 1:

Partitioning makes large tables or indexes more manageable, because partitioning enables you to manage and access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. By using partitioning, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes in earlier versions of SQL Server. Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required, instead of the whole table.

EDIT as op stated that Enterprise is not an option:

When using Standard Edition, you can fall back to using Partitioned Views.

The main difference from the usage side is that you have to maintain the partitioned tables for yourself and you also have to make sure that every new member tables gets added into the view.

The idea builds on your scenario 2 but you don't have to care about where to insert the data, as this is determined by the Partitioned View based on CHECK-Constraints in the member tables.

For a good example, see 2

As for all partitioning scenarios you have to make sure that the partitioning columns you choose fits your query-and-insert behaviour.

share|improve this answer
    
Unfortunately the (ridiculous, imho) cost of Enterprise means it's not an option :( –  KenD Aug 15 '13 at 9:41
    
Interesting idea about using Partitioned Views, but as you say it requires maintaining the tables and view ourselves. I guess my thinking is that scenario 1 is the easier option from a "set it and forget it" stance - but are there any downsides? –  KenD Aug 15 '13 at 11:33
    
Personally, I would avoid shuffling data around every night, because there are just too many things that could go wrong as a gut feeling. Depending on how granular you wanted to partition your data, you could also created tables for the next months, year, decades... upfront so you wouldn't have to worry about it for a certain timespan. I'm not sure if you could define a member table that would act as a "overflow" if no CHECK constraint matches the data, that would make things easier. –  Oliver Rahner Aug 15 '13 at 11:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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