Press "Enter" to skip to content

Category: Query Tuning

Indexes and Sorts

Chad Callihan reminds us that sort order can matter for indexes:

When you’re working on an index for a query ordering by one column in ascending order and another column in descending order, do you have your index created to match? Did you know you can specify ASC or DESC in an index? Let’s go through a scenario where ordering in an index makes a noticeable difference.

This is particularly important for window functions—the optimizer can sometimes be smart enough to recognize that a value is in reverse order and not need to use a sort operator, but as soon as you drop that OVER() clause in, if things aren’t in the exact order they need, you get a sort operator thrown in for free. Or, well, the “your query is now a little bit slower” version of free.

Comments closed

The Costs and Benefits of Dirty Reads

Chad Callihan explains what a dirty read is and does a cost-benefit analysis on it:

When you are not careful with your transaction isolation levels or you get sneaky with the NOLOCK hint, one problem you can encounter is a dirty read. Let’s look at a short example to demonstrate a dirty read.

In a vacuum, I’m not necessarily opposed to the idea of dirty reads because you can find legitimate cases in which they can be useful. In practice, I’m generally very much in opposition because of two reasons: first, Read Committed Snapshot Isolation eliminates the majority of those reasons; and second, because the misuse is almost always in the direction of over-use of NOLOCK hints.

Comments closed

The Downside Risk of Index Hints

Chad Callihan explains why you should be careful before deploying code which uses index hints:

This might be good enough…for now. The potential issues with index hints can be more about the future than the present. You might come along later on and think “why not use an index to cover the whole query?” We can add the index:

But if our query is still written to include the index hint (in a stored procedure for example) the new index is not going to matter. The old index is still forced to be used. Even if something better comes along, you’re going to need to modify the query in addition to adding the better index. If an index was added for a completely separate query but would also be an improvement for the query in question, it’s also not going to get by the index hint.

Click through for additional problems which can crop up as you use index hints. This isn’t a big argument against using them at all, but rather understanding (and remembering!) where you do use them and making sure that’s communicated well to the entire team, including future you.

Comments closed

Queues and Watermarks

Forrest McDaniel wants a zippier queue in SQL Server:

I recently had the pleasure of investigating a procedure that pulled from a queue. Normally it was fast, but occasionally runtime would spike. The spooky thing was the query was using an ordered index scan that should only read one row, but during the spikes it was reading thousands.

Surely there’s a rational explanation…

Spoilers: there was. And Forrest a’int afraid of no ghosts.

(sotto voce – I’m so glad that Forrest didn’t sneak in any Ghostbusters references so that I could do that here and be original.)

Comments closed

Finding Eager Index Spools

Erik Darling hunts the most dangerous prey of all:

I’ve written a bunch about Eager Index Spools, and how to fix them, but I’ve always sort of left the “finding” part up to you, or pointed curious tuners to tools like sp_BlitzCache.

Recently though, I worked with a client who had Eager Index Spools so frequently that we needed to track them down specifically.

This is the plan cache query that I used to do it — they didn’t have Query Store enabled — and I wanted to share it.

Click through for the query.

Comments closed

A GETDATE() Workaround when Rewriting Scalar UDFs

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

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