Press "Enter" to skip to content

Category: Query Tuning

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

Using EvaluateAndLog to Diagnose DAX Performance Problems

Published 2022-09-19 by Kevin Feasel

Chris Webb does a bit of sleuthing:

The Switch function is often a cause of performance problems in DAX. Last year a number of optimisations were made that fixed the most common issues (and as a result you should ignore older blog posts that you may find on this subject) but some scenarios still remain where performance can be bad; Marco and Alberto wrote a great post recently outlining some of them that I encourage you to read. How do you even know whether your measure is slow because of your use of the Switch function though? Trying to read query plans, as Marco and Alberto do in their post, isn’t usually practical and commenting out branches in a Switch to see if there is a performance change can be very time-consuming. Luckily, the new EvaluateAndLog DAX function can help you diagnose Switch-related performance issues.

Click through to see how.

Comments closed

Date Calculation (and Calendar Tables) in SQL Server

Published 2022-09-15 by Kevin Feasel

Aaron Bertrand makes the case for calendar tables:

In a previous tip, Simplify Date Period Calculations in SQL Server, I described how to easily calculate certain dates, like the first day of a given month, quarter, or year. Another common scenario is trying to find the last weekday of the month, or the nth Monday or Wednesday. In this tip, I will show ways to simplify these calculations, both with and without a calendar table.

Click through for Aaron’s approach to the problem. I have a blunter approach in creating an expansive calendar table and using it. You do the heavy lifting one time and are good for life on that server.

Comments closed

SQL Server 2022 Query Store Hints

Published 2022-09-09 by Kevin Feasel

David Pless takes us through some new query hints:

Query Store hints provide a direct method for developers and DBAs to shape query plans without changing application code.  

Query Store hints are a new feature that extends the power of Query Store—but this means that Query Store hints does require the Query Store feature to be enabled and that your query and query plan are captured in the Query Store.

Just like plan guides, Query Store hints are persisted and will survive restarts, but Query Store hints are much easier to use than plan guides.

Read on to see which options are available and how they work.

Comments closed