A Fine Slice Of SQL Server
Jared Westover explains a phenomenon:
Did you know that the Cost Threshold for Parallelism (CTFP) affects SQL Server’s choice of a trivial execution plan? One area that can suffer from this setting is the optimizer’s ability to suggest index recommendations. When SQL Server picks a trivial plan, it skips suggesting any missing indexes. So, if you’ve set a high CTFP and run simple queries, you might never get those handy index recommendations.
Click through to learn more.
Comments closedKevin Wilkie gets down to tuning:
In our last post, we talked about some of my favorite queries I use in Snowflake to see various items of interest – such as finding the worst-performing queries. For today’s post, though, I want to talk about performance tuning.
Yes, you read that right. We’re going to use query history to do some fun performance tuning.
Click through for two queries that can help you find what you may need to tune.
Comments closedAndrei Lepikhov walks through an interesting scenario:
What was the impetus to begin this work? It was caused by many real cases that may be demonstrated clearly by the Join Order Benchmark. How much performance do you think Postgres loses if you change its preference of employing parallel workers from one to zero? Two times regression? What about 10 or 100 times slower?
The black line in the graph below shows the change in execution time of each query between two cases: with parallel workers disabled and with a single parallel worker per gather allowed. For details, see the test script and EXPLAINs, with and without parallel workers.
Click through for an overview of what Andrei wrote, including architectural notes. But stick around until the end to see just how difficult the challenge is to re-optimize without making performance worse in the end.
Comments closedKendra Little warns us not to let a person named General Failure run your military:
This post demonstrates two related bugs with plan forcing in Query Store which increase the likelihood of slower query execution and application timeouts in SQL Server environments.
These bugs are most likely to impact you if:
- You use the Automatic Plan Correction feature in SQL Server, which automatically forces query plans.
- Anyone manually forces query plans with Query Store.
- You have slow storage, which can increase your likelihood of having longer compilation times.
The General Purpose tier of Azure SQL Managed Instance and Azure SQL Database feature both slow storage and Automatic Plan Correction enabled by default. So, weirdly enough, your risks of suffering from this problem are high if you are an Azure SQL customer.
In the words of the great John Madden, that’s a heckuva bug.
Comments closedDeepak Mahto shares a tool with us:
Tuning SQL is an integral part of most developer’s or DBA’s primary responsibilities. Understanding and dissecting problematic execution plans are critical steps in the overall tuning process.
One key ingredient of SQL performance tuning, along with the runtime plan, is object statistics. Looking only at the execution plan is often insufficient for making solid recommendations; we need a fair understanding of current object statistics.As the saying goes,
Behind every optimized SQL query lies a deep understanding of the underlying object stats.
Click through to see examples of the StatsViaExplainAnalyze tool and how you can get a copy of it from GitHub.
Comments closedJared Westover talks performance:
Often, developers use local variables when writing ad hoc queries or stored procedures for many reasons. You might hear “never repeat code” or “avoid using magic numbers.” While writing a lengthy stored procedure, I might include a few. However, did you know that local variables can hurt the performance of your queries? How can you keep local variables from negatively affecting performance? Keep reading to find those answers and more.
This is the kind of performance issue that you can easily forget about. Jared includes two methods for resolving the issue if you run into performance problems on a specific query or stored procedure.
Comments closedRod Edwards does a bit of analysis:
Again, this is another one of those little bits of functionality that has been present since year dot. Unfortunately, it seems to be forgotten by many in the great game of query investigation.
Being able to see the IO patterns of a query that we’re running to help us see where the heavy lifting is occurring is really useful in allowing us to get the bottom of problems quickly, or see the benefits of our tuning efforts (or otherwise!).
One neat thing I did not know about before was a product called Statistics Reporter, an extension for SQL Server Management Studio that parses the results from time and I/O statistics results. I like Richie Rump’s Statistics Parser website, but the idea of having something built-in is pretty nice.
Comments closedLaurenz Albe digs into keyset pagination:
Keyset pagination is the most performant way to retrieve a large result set page by page. However, the neat trick with composite type comparison doesn’t always work. This article explains why and how you can work around that shortcoming.
All of the examples are for Postgres, though in my read-through of the post, I’d say it applies almost as well to SQL Server.
Comments closedAndrei Lepikhov compares two physical join operators:
Today’s post is sparked by a puzzling observation: users, especially those who use an abstraction layer like REST or ORM library to interact with databases, frequently disable the MergeJoin option across the entire database instance. They justify this action by citing numerous instances of performance degradation.
Considering how many interesting execution paths MergeJoin adds to the system elaborating IncrementalSort or sort orderings derived from underlying IndexScan, it looks strange: one more bug of skewed cost balance inside the PostgreSQL cost model?
This is an interesting peek into how complex the query optimizers in database engines are, as well as how small amounts of information (via statistics or indexes) can matter to a query.
Comments closedI showed the missing index suggestion (and let’s remember, they’re just suggestions) just for completion and to show I’m not hiding anything. Potentially, adding an index could speed up the query. However, that doesn’t affect what we’re going to talk about within this plan.
Logically, what’s happening here?
Click through for this overview, as well as a very helpful comment from Hugo Kornelis on what the Segment operator does.
Comments closed