SQL Server 2008 Support for 15k Partitions

The newest service packs for both SQL Server 2008 (SP2) and SQL Server 2008 R2 (SP1) now provide support for up to 15,000 partitions in SQL Server. You may find this very helpful if you need to partition very granularly in your data warehouse and were perhaps limited by the previous 999 partition limit. More information is available here and you can even modify existing partitions to accept the new limit. As a side note, I’ve performed several data warehouse management and partitioning sessions for customers recently and have yet to find anyone feeling hampered by the 999 partition limit. But there are definitely cases out there for this requirement. Though not related to any updates in these service packs, you should note that the advances to SQL Server partitioning since 2008 include parallelism & partition-level lock escalation andwhen combined with SQL Server 2008’s ability to maximize Star Join queries with bitmap filters and good old partition elimination, you get better performing queries (when using the partition key) in a data warehouse workload. This makes me think … it may be time for my own take on data warehouse lifecycle management using partition switching, data archiving and sliding window methods. All are very important to understand when you become a DBA responsible for data warehouses.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s