Thanks for taking this course!

Transcript

As we’ve covered in this course, table partitioning doesn’t just make every query faster in SQL Server.

We saw problems in queries where sometimes super efficient aggregate operations got replaced by other cool aggregate operations, but which were blocking operators. We saw the SQL Server optimizer have some problems with some min/max/top style queries, and we saw that implicit conversions can cause SQL Server to not be able to eliminate partitions sometimes if you’re not careful about the data types that you’re using in your queries.

When you’re attacking slow queries using partitioned tables, non-aligned indexes are an option, but remember not to jump to those too fast. Do some research on whether a T-SQL rewrite, or perhaps a different kind of partitioned index like a columnstore index might help speed up your queries.

Columnstore indexes are super cool, especially if you’ve got a reporting style or aggregate workload that wants to scan a lot of rows and count them up and do aggregates, those columnstore indexes can really help your query performance against your partitioned tables. In general, if you’re partitioning a table, it likely has enough rows that potentially a columnstore index could be a benefit, depending on your query patterns.

As we saw, we can now use columnstore indexes as of SQL Server 2016 Service Pack 1– like partitioning we can use that in lower editions. But the columnstore indexes do have limits on the amount of cores you can use for batch mode queries, the amount of memory that can be dedicated to those columnstore indexes and you don’t get all of the bells and whistles in terms of where it can push down some of its very fancy optimizations. But it’s still really cool that we can use these tools in lower editions of SQL Server.

My tips for tuning partitioned queries are: Execution and plans are really helpful. If you’re able to use a tool like Query Store to tell SQL Server: “Hey, I want you to track my execution plans,” then later if you’ve got to go chasing after “Why did this query get slower?”, having watched your performance with Query Store gives you a lot more to reference and it’s easier to find the execution plans that you need, along with information on how many logical reads were used.

Whenever I can get an actual execution plan, I’m thrilled, because I get lots more information about how many partitions were actually accessed, but if I can’t get the actual plan for whatever reason, getting the cached plan, or estimated plan, and then looking at runtime statistics either from the plan cache in SQL Server or from Query Store saying: “How many reads did you really do?” I can make a guess of how many partitions were accessed.

I would beware using missing index recommendations with partitioned tables. Sometimes SQL server will ask for an index against a partitioned table that you already have a partitioned version of that index. Sometimes it’s a little goofy on what it asks for. Don’t just look at the missing index recommendation and say, “Oh, that’ll help.” It might be misleading you in the wrong direction.

Thank you so much for joining me in this course and tuning partitioned queries with me. I would love for you to leave a review to help others find it and let me know what you thought. Thanks so much for joining me. I’m Kendra Little and I’ll see you again in another course soon.