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)
- Michelle Ufford:
- Kimberly Tripp:
- Craig Freedman (SQL Server Query Processing Team)
- Paul White
- SQL CAT Team
- 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
- “Features Supported by the Editions of SQL Server 2008 R2”
- “Table and Index Organization”
- “Partitioned Table and Index Concepts”
- “Designing Partitions to Improve Query Performance”
- “Query Processing Enhancements on Partitioned Tables and Indexes” (Note: If the first part seems overwhelming, still scroll down to see the best practices.)
- “Transferring Data Efficiently by Using Partition Switching”
- “Replicating Partitioned Tables and Indexes”
- “Programmability Enhancements (Database Engine)” for SQL 2008 R2
- “How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005“
- Sql Server MVP Deep Dives: https://www.manning.com/nielsen/
- Ron Talmage: “Some practical issues in table partitioning”
- Dan Guzman: “Partitioning for manageability (and maybe performance)”
1 Comment.
[…] Brentozar https://www.brentozar.com/sql/table-partitioning-resources/ […]