Press "Enter" to skip to content

Category: Query Tuning

A GETDATE() Workaround when Rewriting Scalar UDFs

Published 2021-06-28 by Kevin Feasel

Erik Darling finds a workaround for one scalar UDF inlining limitation:

SQL Server 2019 has been a bit of a roller coaster ride. In particular, UDF inlining started as I think the most interesting addition to the product. Big brain stuff, for sure.

It has been nerfed quite a bit, with seemingly more and more restrictions added to every cumulative update. Hopefully some of these can be lifted at the feature matures, but I understand how difficult all this is.

People program absolute bloodbaths into functions.

Today, I want to look at one restriction that has a fairly simple workaround: Calling GETDATE().

Click through to see how you can replace calls to GETDATE() without too much hassle.

Comments closed

Understanding Query Execution Time Statistics

Published 2021-06-15 by Kevin Feasel

Esat Erkec takes us through SET STATISTICS TIME ON:

The SET STATISTICS TIME ON statement returns a text report and this report includes how long it is taken by the query compilation and execution time of a query. To enable this option for any query we need to execute the SET STATISTICS TIME ON command before the execution of the query so that the execution time report will appear in the message of the query result panel until we turn off this option. All values of the report ​​are shown in milliseconds type and its syntax like as below:

Read on to see how you can use it, as well as things to keep in mind as you do.

Comments closed

When Expressions Beat Local Variables

Published 2021-06-03 by Kevin Feasel

Erik Darling talks about a pet peeve of mine:

I see people writing stored procedures that use local variables either because they read that all functions in a where clause are dangerous, or because they need to perform a calculation on one of the parameters that get passed in.

In nearly all cases, you’re better off… not doing that.

Here are a couple examples of why.

I think a lot of this stems from advice to avoid non-SARGable predicates, but miss the idea that expressions of the sort Erik shows are perfectly cromulent.

Comments closed

Dealing with Key Lookups

Published 2021-06-01 by Kevin Feasel

Jared Poche shows a good technique for removing key lookups:

A key lookup is an operation that occurs when a query has used a nonclustered index on a given table, but needs to access more columns to complete the query. It may need to check columns not in that index for additional filters, or it may just need to return that column as part of its result set.

In the simple query above, we’re retrieving 100 rows from the seek against a nonclustered index, then performing a key lookup against the clustered index. There is a nested loops operator between the two and understanding how that operates is important; for each row we receive from the first table, we perform the second operation once. So, in this query we are seeking 100 rows from the nonclustered index, then performing the key lookup 100 times. We go through the index once for each row we return, and you can see the cost of the key lookup operator is 99% of the query.

Read on for more information, including how to eliminate key lookups.

Comments closed

Percentages in Execution Plans

Published 2021-05-25 by Kevin Feasel

Hugo Kornelis shares some information with us:

A lot of information you see when looking at execution plan is shown as a percentage. And those percentages are often the first thing that draws our attention. So it’s really important to know what those values are. And yet, I had forgotten to include coverage for percentages in my training videos. And you’ll also not find those percentages mentioned in the Execution Plan Reference. Why? Simple. They don’t exist.

It is here that I imagine a short silence. Followed by a huge outburst. “What? Of course they exist! I look at them every day!”

Read on to see what Hugo means, and also check out the video training course.

Comments closed

Temporal Table Performance Scenarios

Published 2021-05-21 by Kevin Feasel

Hugo Kornelis continues a series on temporal table performance:

Welcome to part eighteen of the plansplaining series. Like the previous posts, this one too focuses on temporal tables and their effect on the execution plan. After looking at data modifications in temporal tables and at querying with a most basic temporal form of temporal query, let’s look at the more advanced variations for temporal querying.

We’re still looking at getting data from a single query only in this post. We’ll look at joins in the next post.

Click through for these scenarios.

Comments closed

Tracking Query Compile Time

Published 2021-05-12 by Kevin Feasel

Grant Fritchey doesn’t have time to wait:

A question that came up recently was how to track the query compile time. It’s actually a pretty interesting question because, there aren’t that many ways to tell how long it took to compile the query, and they don’t necessarily agree. For most of us, most of the time, compile time for a given query doesn’t matter. However, I love telling the story of the query I had on an old system that could run in 90ms, but took 5 minutes to compile. In short, sometimes compile time matters.

Read on to learn several ways to determine how long it took that query to compile.

Comments closed