Press "Enter" to skip to content

Category: Query Tuning

DATE_BUCKET and DATETRUNC in SQL Server 2022

Published 2022-10-19 by Kevin Feasel

Itzik Ben-Gan shows a good use of a pair of new T-SQL functions:

Time-based grouping and aggregation are common in analyzing data using T-SQL—for example, grouping sales orders by year or by week and computing order counts per group. When you apply time-based grouping, you often group the data by expressions that manipulate date and time columns with functions such as YEAR, MONTH, and DATEPART. Such manipulation typically inhibits the optimizer’s ability to rely on index order. Before SQL Server 2022, there was a workaround that enabled relying on index order, but besides being quite ugly, it had its cost, and the tradeoff wasn’t always acceptable.

Comments closed

Rewriting Tricky Functions in SQL Server

Published 2022-10-19 by Kevin Feasel

Erik Darling fights dragons:

Far and away, some of the trickiest situations I run into when helping clients is rewriting scalar functions that have WHILE loops in them.

This sort of procedural code is often difficult, but not impossible, to replace with set-based logic.

Erik improves a function in this post, though often, the best way to improve a function is not to play the game at all.

Comments closed

Checking All Metrics when Query Tuning

Published 2022-10-18 by Kevin Feasel

Grant Fritchey has some query tuning advice:

Recently, a person asked about the costs differences in an execution plan, referencing them as if they were performance measures. The key to understanding performance is to check every metric. When it comes to execution plans, I’m sure I’ve said this before, so please allow me to repeat myself.

The cost numbers shown in an execution plan, which, barring a recompile, will be the same for an execution plan or an execution plan with runtime metrics (aka, estimated and actual plans), are not measures of performance. They do not represent actual metrics. Instead, they are calculations of a theoretical actual performance measurement. So, you can’t look at two plans, with two costs, and say, “this plan will perform better.” Instead, you can say, “this plan has a lower estimated cost.” To really see performance metrics, you must measure performance.

Read on for the full set of advice.

Comments closed

Date Ranges and Merge Interval

Published 2022-10-17 by Kevin Feasel

Daniel Hutmacher notes a performance killer:

In my last post, I found that DATEDIFF, DATEADD and the other date functions in SQL Server are not as datatype agnostic as the documentation would have you believe. Those functions would perform an implicit datatype conversion to either datetimeoffset or datetime (!), which would noticeably affect the CPU time of a query.

Well, today I was building a query on an indexed date range, and the execution plan contained a Merge Interval operator. Turns out, this operator brings a few unexpected surprises to your query performance. The good news is, it’s a relatively simple fix.

Click through for an example and some information on a fix. Hugo Kornelis also adds some good insights in the comments.

Comments closed

Another Problem with Nullable Columns

Published 2022-10-14 by Kevin Feasel

Erik Darling tells the unpleasant truth about NULL:

Table definitions have a similar effect on developers. In today’s post, I’m going to use temp tables as an example, but the same thing can happen with regular tables, too.

The issue isn’t with NULL values themselves, of course. The table definition  we’re going to use will allow NULLs, but no NULLs will be present in the data.

The issue is with how you query NULLable columns, even when no NULLs are present.

As a card-carrying member of the League of No-Null Workers, I am happy to tell you all about how much I hate NULL in databases. And this isn’t even the big reason.

Comments closed

Azure Stream Analytics Job Diagram Simulator

Published 2022-10-13 by Kevin Feasel

Alex Lin points out some functionality to understand Azure Stream Analytics jobs:

Azure Stream Analytics (ASA) uses a SQL query language that has been augmented with powerful temporal operators to analyze data streams. One way to optimize a Stream Analytics job’s performance is to leverage parallelism in query. A parallel job divides the workload of queries into multiple streaming nodes and reduces the overall execution time.  

We strongly recommend that you use the Azure Stream Analytics Tools extension in Visual Studio Code (VS Code) for testing and debugging queries locally. For a job to be parallel, you need to align partition keys between all inputs, query steps, and outputs. The Job Diagram Simulator in VS Code allows you to simulate a job running topology with different streaming units (SUs) and provides suggestions for improving your query parallelism. 

Click through to see how it works and the kinds of information you can get from it.

Comments closed

Implicit Conversion of DATEDIFF

Published 2022-10-11 by Kevin Feasel

Daniel Hutmacher noticed a problem:

As I was performance tuning a query, I found that a number of date calculation functions in SQL Server appear to be forcing a conversion of their date parameters to a specific datatype, adding computational work to a query that uses them. In programming terms, it seems that these functions do not have “overloads”, i.e. different code paths depending on the incoming datatype.

So let’s take a closer look at how this manifests itself.

Some of these results are pretty annoying, especially because I like to use DATETIME2(0) for the large majority of development work

Comments closed

Query Plans from Incomplete Executions

Published 2022-10-10 by Kevin Feasel

Erik Darling needs information:

One thing that comes up pretty often when you ask for an actual execution plan, is that the query “never finishes” and one can’t be captured.

Good news! There are ways for you to get execution plans in progress with some of the actual query plan elements inside.

You don’t have to let the query run to completion, but generally if you give it a few minutes you can capture where things are going wrong.

Read on to see how, as well as the limitations of each technique.

Comments closed

Eager Index Spooling

Published 2022-09-20 by Kevin Feasel

Chad Callihan has a great analogy for eager spools:

Think of a time when you went to someone for help on a problem. Rather than walk you through how to solve the problem, that someone just groans, speeds through solving it for you, and sends you on your way without any explanation.

Did you learn anything? No.

Will you need help again next time that same problem comes up? You bet.

I also like Erik Darling’s explanation that an eager spool is SQL Server’s passive-aggressive way of telling you that you need an index but no, you’re just too busy to create one so I’ll just keep working here all day and do you even call your poor mother anymore?

Comments closed