Performance Tuning – Curated SQL

Foreign Keys and Updating the Parent

Published 2020-12-21 by Kevin Feasel

Hugo Kornelis conclues a mini-series on foreign key constraints:

Welcome to part fourteen of the plansplaining series, where I wrap up the mini-series on how simple foreign keys have huge effects on execution plans for data modifications.
We already looked at inserting data in the referencing (child) table, and at deleting data from the referenced (parent) table as well as updates in the child table. We did not and will not look at deleting from the child table or inserting in the parent table: those operations can by default never violate the foreign key constraint, so no additional logic is needed.
So that means there is only one thing left to explore: updating the parent. Perhaps surprisingly, this is actually quite complex, so it warrants an entire post of its own.

Read on to see why.

Comments closed

Create the Fastest Number Series Generator

Published 2020-12-09 by Kevin Feasel

Itzik Ben-Gan has a challenge for us:

I always used my solution with a base table value constructor cardinality of 2, but Marcos’ comment made me think. This tool is so useful that we as a community should join forces to try and create the fastest version that we possibly can. Testing different base table cardinalities is just one dimension to try. There could be many others. I’ll present the performance tests that I’ve done with my solution. I mainly experimented with different table value constructor cardinalities, with serial versus parallel processing, and with row mode versus batch mode processing. However, it could be that an entirely different solution is even faster than my best version. So, the challenge is on! I’m calling all jedi, padawan, wizard and apprentice alike. What’s the best performing solution that you can conjure? Do you have it within you to beat the fastest solution posted thus far? If so, share yours as a comment to this article, and feel free to improve any solution posted by others.

Give it a try.

Comments closed

The Filter Operator

Published 2020-11-20 by Kevin Feasel

Hugo Cornelis continues a dive into execution plan operators:

The Filter operator is used to remove rows from a result set that do not satisfy a specified condition.
Its most common usage is to implement a WHERE clause, although many elements of a WHERE clause get implemented in other ways (e.g. by pushing them down into an Index Seek or Index Scan operator), and Filter operators can also be used when the optimizer transforms other query elements into any type of filtering.
The Filter operator can only verify conditions based on the data in the current row; if data from different rows needs to be compared for the filter, the optimizer generates an execution plan that first adds the data to be compared to the row, or it uses different operators.

Click through for Hugo’s normal in-depth look.

Comments closed

Improving Performance Counters with Powershell

Published 2020-11-19 by Kevin Feasel

Jeffrey Hicks has an improvement to Get-Counter in Powershell:

I wanted to tell you about another addition to the latest release of the PSScriptTools module. This is something I’ve written about before but I decided to add the function to the module. I hope you find it a much easier way to work with performance counters. And it works in Windows PowerShell and PowerShell 7.x.

Click through to see what has changed.

Comments closed

The Default Cardinality Estimator and Ascending Keys

Published 2020-11-19 by Kevin Feasel

Erik Darling compares cardinality estimators:

Look, I’m not saying there’s only one thing that the “Default” cardinality estimator does better than the “Legacy” cardinality estimator. All I’m saying is that this is one thing that I think it does better.
What’s that one thing? Ascending keys. In particular, when queries search for values that haven’t quite made it to the histogram yet because a stats update hasn’t occurred since they landed in the mix.

Read the whole thing.

Comments closed

PASS Summit Q&A: Intelligent Query Processing

Published 2020-11-17 by Kevin Feasel

Kathi Kellenberger has a follow-up of some questions after a PASS Virtual Summit session:

Last week, I presented a session on Intelligent Query Processing for the first ever Virtual PASS Summit. This summit had a mix of live and pre-recorded session. During the pre-recorded sessions, the speaker could hang out with the attendees in the discussion room and join a virtual chat room at the end. My session was live, so I answered questions a few questions during the session. There were a couple of questions that I couldn’t answer fully during the session, but all the questions were interesting, so I’ll go through them here.

Click through for the questions and answers.

Comments closed

Parallelism and Nested Loops Joins

Published 2020-11-02 by Kevin Feasel

Erik Darling talks about the intersection of two performance tuning topics:

Yesterday we saw a case where the Gather Streams operator was costed quite highly, and it prevented a parallel plan from being chosen, despite the parallel plan in this case being much faster.
It’s important to note that costing for plans is not a direct reflection of actual time or effort, nor is it accurate to your local configuration.
They’re estimates used to come up with a plan. When you get an actual plan, there are no added-in “Actual Cost” metrics.

Read on to see how you can monkey’s paw your way through this problem by introducing exciting, new problems.

Comments closed

Tips for Optimizing Dedicated SQL Pools in Synpase Analytics

Published 2020-10-09 by Kevin Feasel

Tsuyoshi Matsuzaki shares some tips for improving query performance when using Dedicated SQL Pools in Azure Synapse Analytics:

By above BROADCAST_MOVE operation, the rows in dimension_City table are all copied in a temporary table (called TEMP_ID_3) on all distributed database. (See below.)
Since the size of dimension_City is small, then all rows in this table is duplicated in all database before joining. This time, we join only 2 tables, however, if a lot of tables are needed to join, this data movement will become large overhead for query execution.

The short version is, replicate smaller dimensions and align distribution keys for large tables which get joined together. Both of these minimize the changes of the engine needing to shuffle data between nodes. These sorts of things can make a huge difference when working with Dedicated SQL Pools, cutting query time down by an order of magnitude in some extreme cases.

Comments closed

Monitoring Storage Metrics

Published 2020-10-07 by Kevin Feasel

Robert Sheldon continues a series on storage concepts for the DBA:

When monitoring storage systems, engineers should track a variety of metrics to ensure the systems continue to meet application requirements. Three of the most important and commonly cited metrics are latency, I/O operations per second (IOPS), and throughput. In addition to these three, queue length and I/O splitting can also provide valuable insights into storage performance.
In this article, I discuss all five of these metrics and demonstrate them in action. Despite my focus on these five, they’re not the only important metrics to monitor. For example, engineers should also track storage capacity, device cache usage, controller operations, and storage networks. Even seemingly unrelated components can be a factor, such as low CPU utilization, which can indicate that the processor is waiting on storage to complete requests from the application.

Robert also shows off the new perfmon, which may or may not be better than Perfmon Classic.

Comments closed

Working with Active Power BI Sessions

Published 2020-09-30 by Kevin Feasel

Marc Lelijveld provides us insights:

Getting more insights in the telemetry of your Power BI dataset is always interesting. Especially if you share your Power BI dataset for self-service purposes to build new reports on top of your managed dataset, you might want to know who is actually using in and what queries are they executing against your datasets.
Besides that, there might be a whole lot of other valid use cases where you want to have more insights in currently running sessions on your dataset. An example where I recently ran into, was canceling a Power BI Dataset refresh. As there is no button in the Power BI Service to do this, I had to find a different way to do this.

Read on to see how, using DAX Studio.

Comments closed

Category: Performance Tuning