Sql-server – SQL large table design

database-designpartitioningsql-server-2008

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.

Best Answer

"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:

http://www.brentozar.com/archive/2008/06/sql-server-partitioning-not-the-answer-to-everything/