Understand SQL Server Table Partitioning Now and Forever

Margarita Naumova

We implement table partitioning to deal with the biggest tables because we must keep the performance even when data grows forever. But partitioning is not a silver bullet. We need to understand how it works from the inside. There are many important things we need to consider before implementing it. In this demo-rich session we will cover all these key points. We will start from the beginning of introducing the concept and will dive very fast into mastering it. We cover partition elimination, aligned and non-aligned indexes. We will pay special attention to choosing the partition key, reading query plans of partitioned tables, and troubleshooting performance problems. At the end of this deep dive session, you will get a very good understanding of how the partitioning works and how it helps for better data management and maintenance. Then you will know how to get improvement in the performance, and when you should not choose partitioning but some other optimization techniques instead. You will have many ideas how to approach more complex cases of historical data maintenance, when you have many ever-growing tables by combining partitioning, indexing, even system versioned tables.