Press "Enter" to skip to content

Category: Query Tuning

Fixing a Parallelism Problem Together

Published 2023-11-13 by Kevin Feasel

Reitse Eskens phones a friend:

In my previous blogpost (Click here to read) I wrote about a query that just wouldn’t go parallel. This sparked some discussion and interest from a few people who were very kind and helpful with their suggestions and even deep dives into the query plans, execution statistics etc.

To make one thing very clear, this blog is 99% their work, only the typing and rephrasing is mine. This also means that mistakes are mine as I’m trying to join the different inputs together into a logical story. So let me introduce you to the heroes, order by first name ascending.

Read on to see what people suggested and the effect that had.

Comments closed

Why Batch Mode Sort Spills are Slow

Published 2023-11-13 by Kevin Feasel

Paul White unravels a mystery:

Batch mode sorting was added to SQL Server in the 2016 release under compatibility level 130. Most of the time, a batch mode sort will be much faster than the row mode equivalent.

This post is about an important exception to this rule, as recently reported by Erik Darling (video).

No doubt you’ll visit both links before reading on, but to summarize, the issue is that batch mode sorts are very slow when they spill—much slower than an equivalent row mode sort.

Read the whole thing. Paul does a great job illuminating us.

Comments closed

The Query that Wouldn’t Go Parallel

Published 2023-11-07 by Kevin Feasel

Reitse Eskens was living in a black-and-white world, smoking at a dilapidated desk in a run-down office in a beat-up city, when she came through the door:

So what’s up this time. Our client has moved to Azure in classic lift and shift scenario. Well, almost. They’ve deployed new VM’s and installed SQL Server 2019 Standard in nice DTAP setting. The VM’s are standard E16-4as-v4 SKU. 4 vcpus and 128 GB memory. The disks are Premium SSD LRS ones with 2300 Max IOPS.

Their on-premises environment was a SQL Server 2016 standard edition running on a virtualisation layer with 128 GB of memory and 8 cores.

In both cases there are 2 numa nodes dividing the amount of cores between them.

Read on to learn more about the problem and what Reitse & co did to resolve it. Also check out the comments—Daniel Hutmacher, in particular, I think has the reason nailed.

Comments closed

Trying out Batch Mode on Rowstore

Published 2023-10-27 by Kevin Feasel

Etienne Lopes has some fun with a feature:

Before 2012, creating analytical queries (that usually scan many rows and have lots of aggregations) from big OLTP databases to feed real-time based reports used in decision making processes, could be quite challenging. Then ColumnStore Indexes arrived and they’ve been enhanced overtime, offering amazing gains both in performance and storage.

Unfortunately, regarding pure OLTP databases, there are many situations in which ColumnStore Indexes can’t (or won’t) be used. There are some great performance enhancements present in columnstore that’s for sure and today I’m going to speak about one that became automatically available since SQL Server 2019 for “traditional” RowStore tables. It’s called “Batch Mode on Rowstore” and it can really boost some of our analytical queries over the “traditional tables” without any effort from our side!

There are a series of specific rules you need to hit but if you hit them, I’ve noticed about a 3x performance gain with you doing nothing at all.

Comments closed

Understanding Postgres Explain Plans

Published 2023-10-26 by Kevin Feasel

Muhammad Ali explains explain plans:

In a previous post titled Exploring Postgres Performance: A Deep Dive into pg_stat_statements, we discussed the utility of pg_stat_statements as a tool for identifying resource-intensive queries in terms of memory, CPU, or I/O usage.

Now, let’s suppose you’ve pinpointed certain queries you’d like to investigate further. The EXPLAIN command is used to generate execution plans. It includes:

Read on to see what it includes, how you can generate an explain plan, and some of the optional settings.

Comments closed

Minimizing Callback Counts in SUMX()

Published 2023-10-24 by Kevin Feasel

Marco Russo and Alberto Ferrari speed things up a bit:

Pushing calculations down to the VertiPaq storage engine is always a good practice. Sometimes this is not feasible. However, carefully analyzing the aggregated expression can lead to optimization ideas that produce excellent query plans.

DAX developers should not be scared of iterators. Their performance is great as long as the expression computed during the iteration can be pushed down to the VertiPaq storage engine. 

Read on to understand what they mean by callback and the enormous performance cost you’ll want to avoid.

Comments closed

Fast Key Optimization in SQL Server

Published 2023-10-20 by Kevin Feasel

Paul White explains the performance benefits of fast key optimization and when you can get them:

SQL Server can be called upon to sort a variety of data types. To facilitate this, the sorting code normally calls out to a specific comparator to determine how two compared values should sort: lower, higher, or equal.

Although calling comparator code has low overhead, performing enough of them can cause noticeable performance differences.

To address this, SQL Server has always (since at least version 7) supported a fast key optimization for simple data types. 

Click through to learn which data types support fast key optimization and to gain a feeling of the performance impact.

Comments closed

Query Compilation Time Matters

Published 2023-10-19 by Kevin Feasel

David Klee lays out an argument:

SQL Server query developers, listen up! Query execution time is not everything you should be worried about. You need to examine the parse and compilation time for each of your queries too.

Read on for the brunt of David’s argument. There are things you can do about query compilation time, starting with database design (normalize tables and include key constraints, include appropriate indexes, etc.) and continuing with query design (keep queries simple, limit use of functions, limit use of nested views, break complicated queries into multiple steps and use temp tables as intermediaries, etc.). One thing about compilation time, however, is that it doesn’t matter as much if you retain that plan for a while and reuse it a lot.

Comments closed

Apache Spark Execution Plan Analysis

Published 2023-10-12 by Kevin Feasel

Karthik Penikalapati digs into Spark SQL explain plans:

In this blog post, we will explore how the Explain Plan can be your secret weapon for debugging and optimizing Spark applications. We’ll dive into the basics and provide clear examples in Spark Scala to help you understand how to leverage this valuable tool.

All I’m saying is, if some company wants to create SQL Sentry Plan Explorer for Apache Spark, I’d be down with it. That loss of an intuitive and powerful graphical interface for execution plans is definitely a point of friction when working with Apache Spark and Spark SQL.

Comments closed

Reasons Your SQL Server Query Performance Fluctuates

Published 2023-10-12 by Kevin Feasel

Aaron Bertrand starts the count:

Query performance can fluctuate over time, and it is not necessarily due to a change to the query itself (or to the application code that calls it). Users often ask why a query suddenly got slower even though they haven’t published any changes to the application and the underlying data hasn’t changed drastically. This article points out some other reasons – and there are many – that a query might be slower today than it was 10 minutes ago, two weeks ago, or last summer.

Read on for a bulleted list of reasons. Of course, it would be extremely challenging to create a comprehensive list—for example, in the Same Plan section, in addition to there being more data, changes in the statistical distribution of data can cause performance profiles to change over time. But this is a really good starting point.

Comments closed