Partitioning Archives - Brent Ozar Unlimited®

Partition Level Locking: Explanations From Outer Space

November 30, 2017

Development, Locking, Blocking, and Isolation Levels, Partitioning

dba, development, performance tuning, sql server

It’s not that I don’t like partitioning It’s just that most of my time talking about it is convincing people not to use it. They always wanna use it for the wrong reasons, and I can sort of understand why. Microsoft says you can partition for performance. Partitioning large tables or indexes can have the…

Initial Thoughts on 2016 SP1

November 21, 2016

Erik Darling

In-Memory OLTP (Hekaton), Licensing, Partitioning, SQL Server

A really odd choice I’ve talked a bit about my annoyance with hardware limits for Standard Edition here and here. If you’re a bunch of SQL-savvy developers who understand the Enterprise Edition features that are now available in 2016 SP1, great. You’re a very small minority of shops. I’m talking about places like StackOverflow and…

Partitioned Views: A How-To Guide

September 22, 2016

Erik Darling

Indexing, Partitioning, SQL Server

dba, development, performance tuning

This is not about table partitioning If you want to learn about that, there’s a whole great list of links here, and the Best Blogger Alive has a tremendous post on why table partitioning won’t make your queries any faster over here. With that out of the way, let’s talk about partitioned views, and then…

Temporal Tables, Partitioning, and ColumnStore Indexes

May 24, 2016

Erik Darling

Indexing, Partitioning, SQL Server

This post is mostly a thought experiment The thought was something along the lines of: I have a table I want to keep temporal history of. I don’t plan on keeping a lot of data in that table, but the history table can accumulate quite a bit of data. I want to partition the history…

Why is This Partitioned Query Slower?

September 1, 2015

Kendra Little

Execution Plans, Partitioning

It’s tempting to think that table partitioning will improve query performance. After all, it’s an Enterprise Edition feature– it must have a lot of magic, right? Table partitioning does have magic for the right situations. It shines when you want to add a large amount of data to a table or remove a large amount…

Filtered Indexes vs. Table Partitioning

November 26, 2013

Jes Schultz Borland

Indexing, Partitioning, SQL Server

It was a dark and stormy… Oh, wrong story. It was actually a warm, sunny afternoon in Charlotte, NC. I was presenting “Index Methods You’re Not Using” at PASS Summit. In this talk, I discussed how indexed views, filtered indexes, and compressed indexes can improve your query performance by reducing I/O. From stage right, an…

Best Practices + Table Partitioning: Merging Boundary Points

January 31, 2013

Kendra Little

Partitioning, SQL Server

One of the many best practices for SQL Server’s table partitioning feature is to create “extra” empty partitions around your data. This is explained in SQL Server Books Online in the page on altering a Partition Function: Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before…

SQL Server Table Partitioning Tutorial: Videos and Scripts

January 28, 2013

Kendra Little

Partitioning, SQL Server, Videos

partition switching, sql server, table partitioning

There’s a secret to learning about SQL Server’s table partitioning feature: you need to get your hands on some code and really play with it in a test environment. In this tutorial, Kendra will walk you through test scripts that set up a sample table partitioning environment. These scripts demonstrate gotchas to look out for…

Potential Problems with Partitioning

August 23, 2012

Jeremiah Peschka

Partitioning, SQL Server

Getting started with table partitioning is difficult; there are many decisions to make after you’ve decided to partition data. Correctly configuring partitioning is critical to the long term performance and stability of the database from both a querying and data modification standpoint. There are three key considerations that database architects should devote time to before…

How To Decide if You Should Use Table Partitioning

March 6, 2012

Kendra Little

Partitioning, SQL Server

132

Great volumes have been written about table partitioning. It’s a complex feature and you can read for days to just understand how you might apply it. But will it improve performance for you? Table partitioning produces great benefits for some applications, but causes giant headaches for others. How do you know if you should invest your…

SQL Server Partitioning: Not the Best Practices for Everything

June 12, 2008

Brent Ozar

Partitioning, SQL Server

When SQL 2005 was in beta, I clapped my hands and jumped for joy the first time I played with partitioning. I’ve done a lot of data warehouse work, and partitioning lets the DBA break down massive tables into smaller, easy-to-manage chunks. We can put heavily accessed parts of the table onto fast storage, and…