Why Table Partitioning Doesn’t Speed Up Query Performance (video)

table-partitioning-speed-rectangular

Learn why SQL Server’s table partitioning feature doesn’t make your queries faster– and may even make them slower.

In this 20 minute video, I’ll show you my favorite articles, bugs, and whitepapers online to explain where table partitioning shines and why you might want to implement it, even though it won’t solve your query performance problems.

Articles discussed are by Gail Shaw, Remus Rusanu, and the SQL Customer Advisory Team (SQLCAT). Scroll down below the video for direct links to each resource.

Ready to Dig In? Here are the Links Discussed in the Video

Gail Shaw’s SQL Server Howlers – “I Partitioned my Table, but My Queries Aren’t Faster.

Remus Rusanu’s Stack Overflow answer – “Partitioning is Never Done for Query Performance.”

SQL CAT Team – Diagnosing and Resolving Latch Contention

Connect Bug – Partition Table Using Min/Max Functions and Top N – Index Selection and Performance

Kendra Little on the Brent Ozar Unlimited blog – “Why is this Partitioned Query Slower?”

Books Online – Using Clustered Columnstore Indexes

Previous Post
Required Testing for Installing SQL Server Cumulative Updates and Service Packs
Next Post
How to Script Out Indexes from SQL Server

Related Posts

17 Comments. Leave new

  • JAMES YOUKHANIS
    May 3, 2016 9:18 am

    Hello Kendra that was a great video. You simplify complex subjects and make them easy to understand. Would Data compression make sense to improve query performance on a large table? If so, would you recommended it. Hint Hint maybe your next video on Data Compression 🙂

    Reply
  • This video is very timely. We are implementing table partitioning as we speak to increase concurrency and throughput for one queries on one super-busy table.

    We partitioned a particular table into 10 arbitrary buckets. The blocking has dropped and allowed us to go from an upper limit of 900 tx/second to 7000 tx/second (it’s now CPU bound). It’s fantastic. Thanks for the confirmation of our strategy.

    Michael

    Reply
  • […] Kendra Little has a video on table partitioning: […]

    Reply
  • I know that clustered columnstore indexes can be partitioned, and that they have their own way of eliminating data from query results (segments/rowgroups).

    Assuming you can’t use the workaround in the Connect item, or if it just doesn’t work for you – I was wondering if you (or anyone you know of) have confirmed that if a partitioned columnstore index is in the mix, that this bug is still a show stopper.

    Ned

    Reply
    • I was going to do a quick demo of this with some partitioned table code I had handy and a clustered columnstore index, then I realized that the code has a computed column in it which makes that no worky. Whoops.

      For SQL Server 2016 clustered columnstore, you have the option to have nonclustered B tree indexes on them, and I believe you have the option to make them non-aligned. I just can’t think of whether I’ve actually tested that, hence wanting to run some sample code. Non-aligned indexes get around that min/max/top problem, you just have to disable them to do any switching (and then rebuild them).

      It is also quite possible that the performance you’d get from the columnstore index for such a query would be acceptable to you, and you wouldn’t care. If min/max/top queries will be needed, make sure they get tested in any prototyping you’re doing to make sure.

      I don’t think that the min/max/top bug is usually a show stopper itself. It’s just one of the more common examples of the type of query that can be an unpleasant surprise and need special care after partitioning. When the data management features of partitioning are really worth it for people, they usually work it out and it doesn’t block them.

      Reply
  • Nelson Muller
    May 9, 2016 12:19 pm

    This is a great video Kendra and very insightful. I have an issue with performance where millions of records are written to database every 30 minutes in bulk and the data needs to be archived for several years. Back in SQL Server 2005 days I mimicked partitions by having multiple tables (I had a bad experience with partitioned views). I would use a ToC table where I would keep the name of the table I had to access and other parameters. When I need to access the table, I query the ToC table based on the date or some other parameter to retrieve the table name I needed to query from.
    That worked great but maintenance was a nightmare, especially when there were changes in the requirements that forced me to make table structure changes.

    In recent version of SQL Server I created partitioned tables, partitioned by an integer ID that I reference through the ToC table. The ToC doesn’t store the table name, but a partition ID. When I need to access the table, I query the ToC table based on the date or some other parameter to retrieve the partition ID; then use the partition ID to query the partitioned table. The query performance, however, leaves a lot to be desired.

    I thought that non-clustered partitioned index on the partitioned table would first limit my search to an individual partition, which is how I query the table. This would give me the benefit of indexing partitions individually, much like a tables I had during the SQL Server 2005 days. Apparently that is not so. I still want to use partitioned tables, but I’m coming to the conclusion that my other options are a) to create a hybrid of staging table(s) and partitioned tables to get the best performance for recent data, or b) use filetables like a no-sql database, and retrieve the data through CLRs. While the filetable/CLR provides the fastest performance it doesn’t give me the flexibility I can get with SQL tables.

    Any comments are appreciated

    Reply
    • Hey Nelson,

      It’s really hard to give any advice without knowing more of the details than we can really cover in blog comments.

      I can tell you that the general design for data that has a billions of rows/ large volume of data size stored, AND that is suited to partitioning by date, is a combination of partitioned tables AND partitioned views. Using partitioned views helps solve the problem of schema changes (not all member tables are required to have the same schema) and allowing member tables to have different indexes. The partitioned view is frequently used for reads, and writes may be handled by instead of triggers or procedures with logic that direct the writes the appropriate table. Table partitioning on the member tables helps with management at the data level (switching data in/out, partition level rebuilds, filegroup level backup/dbcc).

      But I’m just speaking in general. For architecture help, you really want a consultant to spend a couple of days with you, really getting to know the SQL Server’s patterns, bottlenecks, and the code’s pain points before working with you to come up with a design– the performance issues could be something quite different than what it seems at first.

      I’m not saying that because I sell consulting, either. I actually don’t right now. 🙂 I’ve just seen a ton of cases where what looked like a table design problem turned out to be something else once I got into the details.

      Reply
  • […] I’m not a fan of partitioning for everything — I did a video earlier this year on Why Table Partitioning Doesn’t Speed Up Query Performance. […]

    Reply
  • […] Disclaimer: I still very much claim that table partitioning is NOT a feature for query performance – it’s a tool for managing data. Watch a free video on the details here. […]

    Reply
  • Reply
  • Just discovered this and will be digging in. One of our main tables has 136 million records. It is not partitioned. There is a nonaligned unique non-clustered index associated with a partitioning scheme/function. This has not been updated in years. The four partitions cover the client ID of of four of our 4,000 clients. At the time these were the largest four clients. Now we have 117 million of that tables records falling into the “fifth” partition in the Primary file group.

    I see that unique NC index in some execution plans but really don’t know how to assess whether it’s hurting us. I suspect we should probably back out the partitioning altogether since one of the four original clients in there is now deactivated.

    Reply
  • […] you really wanna learn about it, you should talk to Kendra — after all, this post is where I usually send folks who don’t believe me about the performance […]

    Reply
  • Flávio Teixeira Sales
    October 13, 2019 9:14 pm
    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu