Press "Enter" to skip to content

Category: Query Tuning

Strongly Type those Parameters

Published 2021-12-14 by Kevin Feasel

Erik Darling has a recommendation:

When working with ORMs, care has to be taken to strongly type your parameters to match the data type, length, precision, and scale of the columns those parameters will be compared to. Time and time again, I see the same patterns with string parameters:

– They’re unnecessarily typed as Unicode/nvarchar

– They’re not defined with an appropriate length

– They’re used as catch-all parameters for temporal types (dates, etc.)

Spoiler: these aren’t benefits.

Comments closed

FAST_FORWARD and Cursors

Published 2021-11-29 by Kevin Feasel

Joe Obbish skips past the commercials:

If you’re like me, you started your database journey by defining cursors with the default options. This went on until a senior developer or DBA kindly pointed out that you can get better performance by using the FAST_FORWARD option. Or maybe you were a real go-getter and found Aaron Bertrand’s performance benchmarking blog post on different cursor options. I admit that for many years I didn’t care to know why FAST_FORWARD sometimes made my queries faster. It had “FAST” in the name and that was good enough for me.

Recently I saw a production issue where using the right cursor options led to a 1000X performance improvement. I decided that ten years of ignorance was enough and finally did some research on different cursor options. This post contains a reproduction and discussion of the production issue.

I thought everybody knew how this works: the database streams the data tape from the supply pully to the play shaft by using a sprocket to rotate the gear in the cassette at a fixed speed. The FAST_FORWARD cursor option engages the fast forward idler in the VCR database and causes rotation to occur more rapidly than normal.

Comments closed

Saying No to NOLOCK

Published 2021-11-24 by Kevin Feasel

Brent Ozar just says no:

When you put NOLOCK in your query, SQL Server will:

– Read rows twice

– Skip rows altogether

– Show you changes that never actually got committed

– Let your query fail with an error

This is not a bug. This is by design. 

There are reasons why you might want to use NOLOCK, but start with no and you’ll be in better shape.

Also, remember that NOLOCK really means “No, lock!”

Comments closed

Sargability and Dates

Published 2021-11-23 by Kevin Feasel

Chad Callihan makes me want to change the title to “Getting Sarge a Date”:

We’ll use the StackOverflow2013 database for this example. Let’s say we want to return the users created in 2013. One way to return this data is to use the YEAR() function to pull out the desired year for our query:

For the reference, check out Chad’s prior post. My expectation is that about 90% of people in the US who are aware of the term pronounce it “Sarge-able” instead of “Sar-guhble” and therefore immediately think of Sergeants.

1 Comment

Data Types Matter, Even in the Serverless SQL Pool

Published 2021-11-22 by Kevin Feasel

Jovan Popovic has a public service announcement for us:

The serverless SQL pool is a distributed computing system that executes concurrent queries on a set of distributed compute nodes. Multiple compute nodes are running the parts of a distributed query plan that read the underlying files, join the data sets, group, and aggregate results. Different queries might try to use the same compute nodes to execute the parts of the queries.

The oversized column types like VARCHAR(MAX) might trick the compute node to allocate more resources than is needed. However, the allocation is based on the estimate, but these over-allocated resources will not be used in actual execution because they are not needed. If a compute node needs 100MB to sort the results it will use these 100MB although the query optimizer allocated 4GB of memory for the task on the compute node.

Read the whole thing.

Comments closed

LOBs and OPTION(RECOMPILE)

Published 2021-11-18 by Kevin Feasel

Paul White has a warning for us:

All that is fairly well-known. The point of this short post is to draw your attention to another side-effect of adding OPTION (RECOMPILE) — the parameter embedding optimization (PEO).

When PEO is used, SQL Server takes the value of any variables and parameters and embeds the runtime values in the query text, pretty much as if you had entered them by hand before compiling. This is often very useful for plan quality, but there is a potential drawback when large object types (LOBs) are in play.

Click through for the explanation and a simple demo.

Comments closed

When MAX becomes TOP

Published 2021-11-16 by Kevin Feasel

Forrest McDaniel tries out a few aggregations:

I can’t tell you how tempted I was to call this MAXTOP. Thinking about it still causes stray giggles to bubble up, as I gleefully envision sharing this info with eager young DBAs at a SQL Saturday, then ushering them off to a session on parallelism. Thankfully I’m not that evil.

The real conclusion is that SQL Server is programmed to be very clever. Despite the cleverness though, details matter, and the cleverness often falls short. What’s that warm glow I feel inside? Ah yes, job security.

Definitely worth a read, and Forrest also shares the repro scripts.

Comments closed

Pokey Performance with EXISTS

Published 2021-11-15 by Kevin Feasel

Erik Darling reminds us that even good things can go bad:

Look, I really like EXISTS and NOT EXISTS. I do. They solve a lot of problems.

This post isn’t a criticism of them at all, nor do I want you to stop using them. I would encourage you to use them more, probably.

But there’s some stuff you need to be aware of when you use them, whether it’s in control-flow logic, or in queries.

But do read on to see a specific type of issue you can run into with a left semi join.

Comments closed

Troubleshooting with sp_HumanEvents

Published 2021-11-04 by Kevin Feasel

Erik Darling shows off sp_HumanEvents:

With shorter procs you can probably just collect actual execution plans and slam F5 like a tall glass of gin at 6am.

But you don’t wanna do that with the larger procs, for a few practical reasons:

– Lots of little queries run quickly, and we don’t care about those

– Navigating through lots of plans in SSMS is tedious

– There’s no differentiation when other procedures, etc. are invoked

– You introduce a lot of overhead retrieving and rendering all those plans

– The full query text might not be captured, which is a limitation in many places

Let’s save the day with sp_HumanEvents, my stored procedure that makes using Extended Events really easy.

Read on to see how this all works.

Comments closed