SQL Server Table Partitioning: Resources

There is a mountain of information out there on partitioning.

Although there were significant performance improvements introduced in SQL Server 2008, it is still worthwhile to read some of the documentation from SQL Server 2005 first to understand the basic principles. Your SQL Server version is important: read through the KB section to see what’s been fixed!

This is not an exhaustive list and it is not ranked. I put comprehensive and introductory material first, then try to organize the details farther down. I periodically update this list with the latest interesting blog posts I find. Let me know if this is helpful!

Content on BrentOzar.com

Here’s material that we’ve authored on table partitioning here on BrentOzar.com.

• SQL Server Table Partitioning Tutorial: Videos and Scripts (Video / Demos of creating partitioned objects, switching in, switching out, and basic best practices)
• How to Decide if You Should Use Table Partitioning (Blog)
• Best Practices + Table Partitioning: Merging Boundary Points (Blog)
• Brent Ozar: “SQL Server Partitioning: Not the Best Practice for Everything”  (Blog)
• Hash Partitioning, SQL Server, and Scaling Writes (Blog)
• Potential Problems with Partitioning (Blog)

Great Starter Material

If you’re new to table partitioning, this section contains a mix of whitepapers and blogs which are a great place to get started.

• Gail Shaw: “Partitioned Tables and Query Performance” (Blog – very thorough and helps you get a grip on the question “What’s really the point of table partitioning?”)
• Kimberly Tripp: “Partitioned Tables and Indexes in SQL Server 2005” (Whitepaper)

Videos

• Microsoft Certified Masters Readiness Video by Kimberly Tripp: “Partitioning”
• “SQL 2008 Partitioned Table Parallelism” demo by Erik Hanson

Whitepapers

• Ron Talmage: “Partitioned Table and Index Strategies Using SQL Server 2008”  (Note: “Best Practices” summary list begins on page 53.)
• Sanjay Mishra: “Loading Bulk Data into a Partitioned Table”
• Pooja Harjani, Susan Price: “Support for 15,000 Partitions in SQL Server 2008”
• Gandhi Swaminathan: “Strategies for Partitioning Relational Data Warehouses in Microsoft SQL Server”
• Erik Hanson: “Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0”  (Note: To see the relevance of partitioning, see the “Loading Data” section.)

Recent Hotfixes / Connect Items

• Workaround: Decreased performance for SQL Server when you run a TOP, MAX or MIN aggregating clause on columns other than the partitioning column – KB 2965553  (This is also a Connect Item.)
• FIX: SQL Server 2008 R2 or SQL Server 2012 takes a long time to generate scripts for a partitioned table that uses the data compression feature. KB 2714634
• FIX: Insufficient system memory error occurs when you try to create an index in a char, varchar, or nvarchar type column in SQL Server 2012 – KB 2897454  (HT Martin Catherall)
• FIX: Query that you run against a partitioned table returns incorrect results in SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012 (descending non-unique NC index, note that a trace flag is required to make the fix take effect) – KB 2892741
• FIX: You receive an incorrect result when you run a query against a partitioned table in SQL Server 2008 R2 or in SQL Server 2012 (maxdop > 6) – KB 2770258
• FIX: Slow performance when an AFTER trigger runs on a partitioned table in SQL Server 2008 R2 or in SQL Server 2012 – KB 2606883
• Possible slow performance on CheckDB in tables with many partitions on SQL Server 2012 – Connect Item

Blogs

• • Thomas Kejser (post-CAT team)
    • Table Pattern: Rotating Log Ring Buffer
  • Michelle Ufford:
    • “Configuring Replication for Partitioned Tables using T-SQL”
    • “Are You Approaching Your Partition Range Limits?“
  • Kimberly Tripp:
    • “How about Filtered Indexes Instead of Partitioning?”
    • “Partitioned tables vs Partitioned Views– Why Are They Even Still Around?“
    • “Did SQL Server Eliminate Any Partitions?“
  • Craig Freedman (SQL Server Query Processing Team)
    • “Introduction to Partitioned Tables”
    • “Partitioned Tables in SQL Server 2008”
    • “Partitioned Indexes in SQL Server 2008”
    • “Dynamic Partition Elmination Performance”
  • Paul White
    • “Two Partitioning Peculiarities“
    • “Why Doesn’t Partition Elimination Work?“

• SQL CAT Team
  • Stuart Ozer “Top 10 Best Practices for Building a Large Scale Relational Data Warehouse”
  • Thomas Kejser: “Enabling Partition Level Locking in SQL Server 2008”
  • Tom Davidson: “Partitioned Tables, Parallelism & Performance Considerations”
• Sunil Agarwal: “Performance improvement by orders of magnitude when merging partitions in SQL Server 2008R2”
• Solomon Rutzky: “Improving Performance of Cross-Partition Queries”
• Lubor Kollar: “Partition Elmination in SQL Server 2005”

SQL Server Books Online – Partitioning Topics