I have a general question about SQL Server 2008 table(s) design. We currently have a table that is over 600GB and grows at about 3GB a day. This table has the appropriate indecies but is becoming a major hangup when running queries and just because of its size. The question is should I split the table into multiple tables by year and month (this would fit how other departments split their large data sets up) or should we leverage the partitioning that is built into SQL Server. It appears that using the partitioning would require less code changes. From what I read when partitioning you still just query one table and the server handles how to get the data. If we went the multiple table route, we would have to handle pulling data from multiple tables.
migrated from serverfault.com Oct 11 '11 at 12:18
This question came from our site for professional system and network administrators.
"This table has the appropriate indecies but is becoming a major hangup when running queries"
Partitioning alone doesn't help query performance unless SQL Server is able to eliminate partitions when running a query. Your WHERE clause needs to line up with the way you partition. We only get one field to use as a partitioning field, so if that field isn't included in your WHERE clause, you're still likely to scan the entire table despite having partitions.
"and just because of its size."
Partitioning can make certain maintenance operations easier, but there's still things we can't do on a partition-by-partition basis. If index maintenance and stats updates are causing you problems, you're better off splitting the design into an archive table and a live-updated table. When you need to periodically move data from the live table to the archive table, you do that, rebuild the indexes with 100% fill factor, update stats with full scan, and then set its filegroup to read-only. Partitioning can help with archive table loads - but partitioning the live table may not. (I'm tossing out several advanced concepts here as if it's quick and simple, but I'm just sketching out some background here.)
"It appears that using the partitioning would require less code changes."
Sorta kinda - it looks that way at first glance, but the more you get into it, you've got options like partitioned views. You can rename the existing table, put in a view in its place, and then you can make your own changes to the underlying tables (and add multiple tables) without changing your app.
I've written more about the pitfalls of partitioning here:
Partitioning in isolation may be sufficient but you may get better results by combining with partitioned views and multiple tables. It very much depends on the pattern of querying and growth.
The current limitation with partitioning is that column statistics are only maintained at a table, rather than partition level. If you have a pattern of querying that would benefit from more accurate statistics, combining table partitioning with partitioned views could yield significant performance benefits.
Where the nature of your data is varying from month to month, year to year, partitioned views can also help. Imagine a retailer which changed its product lines continually, such that there is little consistency in Product.ProductId ranges in use from year to year. With a single order/orderdetail table and therefore a single statistics histogram, the stats will offer little to the query optimiser. A table per year (Order_2010, Order_2011, OrderLine_2010, OrderLine_2011) partitioned by month and combined with partitioned views (Order, OrderLine) will provide more granular and potentially useful statistics to the optimiser.
You can introduce table partitioning with comparitively little effort so start there, measure the impact and later evaluate whether partitioned views would be worth the additional effort.
Kimberly Tripp has published lots of guidance and white papers on partitioning that are generally considered required reading on the topic. Kendra Little also has some good material and a useful reference list of other articles
Performance is usually the number 1 reason people look to partitioning. Personally, I view the improvements in recovery time to be an equal or greater benefit with a VLDB. Take some time to understand partial availability and piecemeal restore before you get started as it may influence the approach you take.
If you have the not-ideal but not-uncommon process of sending backups across the network, you might be looking at a 3 hour restore time for your current 600GB. In a year when you've breached 1.5TB, you've got a problem.
As you said, you have two options here:
With 1, you can create a VIEW that unions all of those tables together, and just update it to include newly created tables. I consider this to really be a way to emulate partitioning. The pros of this method include not requiring Enterprise Edition of SQL Server.
With 2, you can align your indexes to your partitions, and align your partitions to different storage. After you set up your partition function and partition scheme, this is done for you when you split or merge partitions. The pros of this method include not being required to manually move records to a new table. Since the partition function and partition scheme handle this for you. Furthermore, as you said, there is little to no code change needed to access the data.
If you have Enterprise Edition, I would definitely give partitioning a look. Despite how complex it looks, it really is not that bad. If not, partitioning is not even an option for you.
Hope this helps,
From your question, you seem to be storing historical data (logs) and your limitation seem to come from query speed, not storage room issues. For me partition will not help.
When you say you have proper indexes, does it include an index on the date field? I had good results using index on trunc(timestamp, day) with Postgres. You then must ensure all queries do select on day before anything other manipulation. Be careful, a timestamp with timezone field is not indexable (because it "moves" depending on the timezone) so you need a "fixed" timestamp to be indexed.