SQL Server Table Partitioning: Resources

There is a mountain of information out there on partitioning.

SQL Server Table Partitioning Poster

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. Afterward, check out posts on changes made in 2008 to understand the fine print.

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!

–Kendra

Content on BrentOzar.com

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

• Kendra Little:
  • SQL Server Table Partitioning Tutorial: Videos and Scripts (Video / Demos of creating partitioned objects, switching in, switching out, and basic best practices)
  • SQL Server Table Partitioning Poster – Legal Paper Size (Poster)
  • 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)
• Jeremiah Peschka:
  • 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)
  • “Clarifying LEFT and RIGHT in the definition of a PARTITION FUNCTION in SQL Server 2005”
• Michelle Ufford (Blog)
  • “Partitioning 101”
  • “Indexing for Partitioned Tables”
  • “Partitioning Tricks”
  • “Partitioning Example”  (sample code!)

SQLServerPedia Wiki on Partitioning

• “Partitioning”  main page, with video by Brent Ozar
• “Partitioned Table Rules and Gotchas”
• “Scenarios for Partitioning”

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.)

Blogs

• 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
  • “Why Doesn’t Partition Elimination Work?“
• Paul Randal
  • “How having too many filegroups can affect recovery time”
  • “SQL Server 2008: Parallelism improvements for partitioning”
  • “Indexes from Every Angle: What indexes are necessary when using SWITCH PARTITION?”
  • “SQL Server 2008: Partition-level lock escalation details and examples”
• 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