Press "Enter" to skip to content

Category: Internals

Fast-Track Optimization and Window Functions

Published 2023-11-24 by Kevin Feasel

Hugo Kornelis digs into a performance improvement the SQL Server optimizer has for window functions:

This is part twenty-four of the plansplaining series. In the previous part, I explained the execution plans for basic window functions, with and without a window frame. Especially the latter group performed quite poorly in the examples. So let’s now look at an optimization that SQL Server can apply to most cases, that prevents this rather bad scaling.

Click through to see what this is, how it works, and when the optimizer is able to use it.

Leave a Comment

Execution Plans for Window Functions

Published 2023-11-16 by Kevin Feasel

Hugo Kornelis digs in:

In his invitation, Steve specifically asks for examples where Window functions provided a neat solution to a real world problem. Well, sorry Steve, but I am not going to do that. But your invitation did inspire to me write about the execution plans for these window functions. And there is, in fact, so much to write about it, that this is just the first part.

So that makes this post not only a T-SQL Tuesday contribution, but also part 23 in my ongoing plansplaining series: blog posts where I take an in depth look at execution plans to explain how exactly they work, and point out often overlooked details. In this post, I will look at the basics of window functions, as they have existed for over 10 years now. I will point out a few interesting performance gotchas. And there are some links to feedback items that you can upvote (if you are so inclined) to pressure Microsoft to make some changes to the optimizer and the execution engine, to get some small but still welcome performance gains.

Read on to get an in-depth understanding of how those window functions actually operate once the database engine gets ahold of your query.

Comments closed

Why Batch Mode Sort Spills are Slow

Published 2023-11-13 by Kevin Feasel

Paul White unravels a mystery:

Batch mode sorting was added to SQL Server in the 2016 release under compatibility level 130. Most of the time, a batch mode sort will be much faster than the row mode equivalent.

This post is about an important exception to this rule, as recently reported by Erik Darling (video).

No doubt you’ll visit both links before reading on, but to summarize, the issue is that batch mode sorts are very slow when they spill—much slower than an equivalent row mode sort.

Read the whole thing. Paul does a great job illuminating us.

Comments closed

Packed/Batched Remote Procedure Calls in SQL Server

Published 2023-10-27 by Kevin Feasel

Bob Dorr digs in:

The SQL Server TDS protocol provides two main paths for query execution (Language and RPC events.)  You can trace these events using the Batch::Starting/Completed (Language) and RPC:Starting/Completed (RPC) XEvents.

Language events are text streams requiring full parsing and may be susceptible to injection attacks.  Language events also require educated guesses.  For example, should select 1 return a smallint, bigint?

Bob goes on to show an example of a simple call being susceptible to SQL injection, explains why Remote Procedure Calls (RPCs) are superior, and what packing (or batching) RPCs does for you.

Comments closed

Fast Key Optimization in SQL Server

Published 2023-10-20 by Kevin Feasel

Paul White explains the performance benefits of fast key optimization and when you can get them:

SQL Server can be called upon to sort a variety of data types. To facilitate this, the sorting code normally calls out to a specific comparator to determine how two compared values should sort: lower, higher, or equal.

Although calling comparator code has low overhead, performing enough of them can cause noticeable performance differences.

To address this, SQL Server has always (since at least version 7) supported a fast key optimization for simple data types. 

Click through to learn which data types support fast key optimization and to gain a feeling of the performance impact.

Comments closed

The Internals of Backup Compression

Published 2023-10-10 by Kevin Feasel

Andy Yun continues a series on how backups work in SQL Server:

Welcome back to Part 4 of my Backup Internals series. Today, I’d like to spend a little time exploring backup compression.

When you take a regular FULL BACKUP, SQL Server is literally taking a byte-for-byte copy of your data files. Don’t believe me? Then go read this, then come back. Additionally, Microsoft architected BACKUP operations such that the resource utilization and impact would be minimal (when using default parameters).

This post taught me a few things about the practical impact of enabling compression. Even after reading this, however, I would almost always enable it for two reasons. First, storage is usually the bottleneck for organizations, so actions which reduce storage utilization can improve overall performance. Second, there are limits to how much we can store, so compressing backups may let me get away with holding more backups on a given LUN or drive.

Comments closed

Lost Updates with RCSI

Published 2023-10-06 by Kevin Feasel

Kendra Little shares a warning:

There are two isolation levels in SQL Server that use optimistic locking for disk-based tables:

  1. Read Committed Snapshot Isolation (RCSI), which changes the implementation of the default Read Committed Isolation level and enables statement-based consistency.
  2. Snapshot Isolation, which provides high consistency for transactions (which often contain multiple statements). Snapshot Isolation also provides support for identifying update conflicts.

Many folks get pretty nervous about RCSI when they learn that certain timing effects can happen with data modifications that don’t happen under Read Committed. The irony is that RCSI does solve many OTHER timing risks in Read Committed, and overall is more consistent, so sticking with the pessimistic implementation of Read Committed is not a great solution, either.

I don’t recall getting any kinds of update errors with RCSI and I’ve used it in some pretty heavy workloads.

Comments closed

Query Execution Concepts and SQL Server

Published 2023-09-22 by Kevin Feasel

Erik Darling answers the question, why is it so hard to figure out why my query sometimes sucks:

Sometimes people will ask me penetrating questions like “why does SQL Server choose a bad execution plan?” or “why is this query sometimes slow?”

Like many things in databases, it’s an endless spiral of multiverses (and turtles) in which many choose your own adventure games are played and, well, sometimes you get eaten by a Grue.

In this post, I’m going to talk at a high level about potential reasons for both.

Read on for a smorgasbord of factors to consider based on the steps SQL Server takes.

Comments closed

CPU Threads in SQL Server Backups

Published 2023-08-25 by Kevin Feasel

Andy Yun dives in:

Welcome back to Part 3 of my SQL Server Backup Internals Series.

In Part 1, I introduced the “parts” of a BACKUP Operation and in Part 2, we delved into Backup Buffers. Today, we’re going to talk about what manipulates those Backup Buffers = CPU Threads. This’ll be a longer blog, so go refill your coffee now.

Andy did an outstanding job explaining what reader and writer threads do and how SQL Server picks the numbers of each.

Comments closed

Backup Buffers in SQL Server

Published 2023-07-27 by Kevin Feasel

Andy Yun continues a series on backup internals:

Welcome to Part 2 of SQL Server Backup Internals. This blog series is a companion piece to my How to Accelerate Your Database Backups for MSSQLTips.com. In Part 1 of this blog series, I introduced the parts of a BACKUP operation.

Now let’s start focusing on performance characteristics. We can impact the performance of a BACKUP operation by making changes to or more of the following:

Click through for that list, as well as an extended analogy on buffer count and max transfer size.

Comments closed