Erik Darling, Author at Darling Data

Common Query Plan Patterns For Windowing Functions: Ranges, Rows, and Spills

Posted on April 6, 2021May 16, 2022 by Erik Darling

What Why

Certain windowing functions allow you to specify more precise windows to perform calculations on, and many of them allow you to specify an empty OVER() clause to get a global aggregate for a result set.

The performance difference between RANGE and ROWS is fairly well-documented, and I’m mostly covering it here to show you the difference in execution plans.

There’s also some different behavior when Batch Mode shows up around memory usage, which I was amused by.

We’re going to be using this index to help our queries move along faster.

CREATE INDEX c
ON dbo.Comments
(
    UserId,
    CreationDate,
    Score
);

RANGEr Zone

If you don’t specify the type of window you want, by default you’ll use RANGE. I’m using it here to be clear about which I’m covering.

WITH Comments AS 
(
    SELECT
        SUM(c.Score) OVER
        (
            PARTITION BY
                c.UserId
            ORDER BY 
                c.CreationDate
            RANGE BETWEEN UNBOUNDED PRECEDING 
                      AND CURRENT ROW
        ) AS n
    FROM dbo.Comments AS c
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n < 0
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'));

Even with an index to help us along, this query runs for a very long time. In fact it’s been running for the entire time I’ve been writing this particular blog post.

The longer it runs, the more space-filler I have to type, and the more post quality suffers. If this post is terrible, you can blame the above query. Or SQL Server.

Whatever.

Three minutes later:

In my experience, this is the query plan pattern that shows up when you use the RANGE/default specification.

The first Segment is for the Partition By elements, and the second Segment is for both Partition By and Order By.

Depending on your ideas about our time on Earth, and the afterlife, this query could be considered a poor use of finite time.

ROW Boat

Switching over to the ROWS specification, performance is much different, and we get a different execution plan.

WITH Comments AS 
(
    SELECT
        SUM(c.Score) OVER
        (
            PARTITION BY
                c.UserId
            ORDER BY 
                c.CreationDate
            ROWS BETWEEN UNBOUNDED PRECEDING 
                     AND CURRENT ROW
        ) AS n
    FROM dbo.Comments AS c
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n < 0
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'));

I only have to wait about 10 seconds for this one, with some efficiency gained by the query going parallel. Even if I force the query to run at DOP 1, it runs in about 25 seconds. Is 15 seconds of duration worth the 7 additional threads? I don’t know.

Again, it depends largely on your belief system. You may find this to be a good way to kill time until you end up somewhere better. You might not.

The relevant difference in the query plan here (again, based on my observations over the years) is that rather than two consecutive Segment operators, we have Segment > Sequence Project > Segment > Window Spool.

In this case, the Segment operators only list UserId in the “Group By” portion of the query plan. The Sequence Project uses ROW_NUMBER to define the frame.

The reason for the performance difference is that the RANGE/default specification uses an on-disk worktable for the Window Spool, and the ROWS specification uses one in-memory. If you were to compare memory grant details for the two query plans, you’d see the RANGE/default query had 0 for all memory grant categories, and the ROWS specification asks for a ~6GB memory grant.

There is a limit here though. If you go beyond 10,000 rows, an on-disk table will be used. This query will run for just about a minute:

WITH Comments AS 
(
    SELECT
        SUM(c.Score) OVER
        (
            PARTITION BY
                c.UserId
            ORDER BY 
                c.CreationDate
            ROWS BETWEEN 9999 PRECEDING 
                     AND CURRENT ROW
        ) AS n
    FROM dbo.Comments AS c
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n < 0
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'));

Unfortunately, details of the Window Spool don’t indicate that any I/O was done. This seems a little bit weird, since Many to Many Merge Joins will show I/O details for the worktable.

Anyway. What about that Batch Mode?

Batcher Up

In Batch Mode, the window specification doesn’t tend to make a lot of difference when it comes to performance.

Both of these queries ask for 1MB of memory, but take ~3 seconds to scan the table single-threaded.

Again, your trade-off here will be deciding whether or not it’s worth scanning the table faster with parallel threads but incurring additional memory grant with the Sort.

Even though you’re Sorting Sorted data.

Go figure.

OVER It

If you use an empty OVER() clause, you can’t specify rows or ranges. You use the default/RANGE specification. That typically means queries using that in Row Mode vs Batch Mode will be much slower.

WITH Comments AS 
(
    SELECT
        COUNT_BIG(*) OVER () AS n
    FROM dbo.Comments AS c
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n < 0
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'));

Also, the query plans are atrocious looking.

Batch Mode does solve a lot of the issues with them, but at the cost of additional memory usage, and the potential for spilling.

Batch Mode Memory Grant Feedback will fix the spill, and the query will run about 2-3 seconds faster.

Thanks, probably.

Some Grants Are Bigger Than Others

The non-spilling memory grant for the COUNT query is about 800MB.

SQL Server Memory Grant — one right here

The non-spilling grant for the SUM query is about 1100MB:

Apparently the reason for the additional memory is because behind the scenes sum also does a count, and returns NULL if it’s zero.

SELECT
    SUM(x.x) AS the_sum,
    COUNT_BIG(x.x) AS the_x_count,
    COUNT_BIG(*) AS the_full_count
FROM 
(
    SELECT CONVERT(int, NULL) AS x
) AS x;

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Common Query Plan Patterns For Windowing Functions: Column Selection Matters

Posted on April 5, 2021May 16, 2022 by Erik Darling

Not A Doctor

All of our previous queries looked about like this:

WITH Comments AS 
(
    SELECT
        ROW_NUMBER() OVER
        (
            PARTITION BY
                c.UserId
            ORDER BY 
                c.CreationDate
        ) AS n
    FROM dbo.Comments AS c 
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n = 0;

The only columns that we were really selecting from the Comments table were UserId and CreationDate, which are an integer and a datetime.

Those are relatively easy columns to deal with, both from the perspective of reading and sorting.

In order to show you how column selection can muck things up, we need to create a more appropriate column store index, add columns to the select list, and use a where clause to restrict the number of rows we’re sorting. Otherwise, we’ll get a 16GB memory grant for every query.

Starting Point

Selecting no additional columns:

WITH Comments AS 
(
    SELECT
        ROW_NUMBER() OVER
        (
            PARTITION BY
                c.UserId
            ORDER BY 
                c.CreationDate
        ) AS n
    FROM dbo.Comments AS c
    WHERE c.CreationDate >= '20131201'
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n = 0;

On a second run of the query, after a memory grant feedback correction, we end up with a plan with these details:

It takes us 3 milliseconds to scan the column store index, and we get a 24MB memory grant. This is good. I like this.

Darn Strings

Our second query looks like this. We’re selecting all the columns from the Comments table.

WITH Comments AS 
(
    SELECT
        c.Id, 
        c.CreationDate, 
        c.PostId, 
        c.Score, 
        c.Text, 
        c.UserId,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                c.UserId
            ORDER BY 
                c.CreationDate
        ) AS n
    FROM dbo.Comments AS c
    WHERE c.CreationDate >= '20131201'
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n = 0;

A first run of the query, before memory grant feedback fixes things for us, asks for a 16GB memory grant. Without this mechanism in place, we’ll keep asking for the same unproductive grant. If you don’t have batch mode and enterprise edition, this is the scenario you’ll face over and over again.

When memory grant correction kicks in, we end up with a 456MB memory grant.

Quite an adjustment, eh?

We also end up taking 125ms to scan the table with parallel threads, up from 3 milliseconds with a single thread. Of course, the issue here is mostly the Text column.

Strings were a mistake.

No Strings Attached

If we select all the columns other than the string, we’ll end up with a very similar set of metrics as the first plan.

If we want to maintain those metrics, but still show the Text column, we’ll need to do something like this:

WITH Comments AS 
(
    SELECT
        c.Id, 
        c.CreationDate, 
        c.PostId, 
        c.Score,  
        c.UserId,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                c.UserId
            ORDER BY 
                c.CreationDate
        ) AS n
    FROM dbo.Comments AS c
    WHERE c.CreationDate >= '20131201'
)
SELECT 
    c.*,
    c2.Text
FROM Comments AS c
JOIN dbo.Comments AS c2
    ON c.Id = c2.Id
WHERE c.n = 0;

Using a self-join, and getting the initial set of columns we care about, then getting the Text column at the end means we avoid some of the the knuckle-headedness of strings in databases.

Deep Drink

This pattern applies to more than just windowing functions, but it’s a performance issue I have to tune pretty often for people using paging queries.

In tomorrow’s post, we’ll look at another rather unfortunate thing that I see people messing up with windowing functions, and how you can spot it looking at query plans.

Thanks for reading!

Going Further

Common Query Plan Patterns For Windowing Functions: Row Store vs Column Store Indexes

Posted on April 2, 2021May 16, 2022 by Erik Darling

Headed Serial

Getting it out of the way, yes, we can create this nonclustered row store index to store our data in oh-so-perfect order for the windowing function:

CREATE INDEX row_store 
ON dbo.Comments
(
    UserId, 
    CreationDate
);

Keeping in mind that sort direction matters in how you write your query and define your index, this particular topic has, in my mind, been done to death.

Look ma, no Sort! Whoopie.

We also get serial plans. For the row store query, it’s about twice as fast, even single-threaded.

For the column store query, it’s about twice as slow.

Headed Parallel

Here are some interesting things. You’re going to want to speak to the manager.

Let’s force these to go parallel. For science.

Not only is the parallel version of the row mode plan a full second slower, but… look at that batch mode plan.

Look at it real close. There’s a sort before the Window Aggregate, despite reading from the same nonclustered index that the row mode plan uses.

But the row mode plan doesn’t have a Sort in it. Why? Because it reads ordered data from the index, and the batch mode plan doesn’t.

This must be hard to do, with all the support added for Batch Mode stuff, to still not be able to do an ordered scan of the data.

For those of you keeping track at home: yes, we are sorting sorted data.

Column-Headed

Let’s try an equivalent column store index:

CREATE COLUMNSTORE INDEX column_store_ordered 
ON dbo.Comments
(
    UserId, 
    CreationDate
)

Both have to sort, but both are fast and parallel. Yes, Sorting is annoying. Unfortunately, we can’t do this Sort in the application.

But hey, look how fast those index scans are. Choo-choo, as a wise man once said.

And of course, since we have to sort anyway, we’d be better off creating a wide nonclustered column store index on the table, so it would be more generally useful to more queries. You only get one per table, so it’s important to choose wisely.

If you have queries using window functions where performance is suffering, it might be wise to considered nonclustered column store indexes as a data source for them. Beyond just tricking the optimizer into using Batch Mode, the data compression really helps.

Elsewise

But there’s something else to consider, here: the plans with Sorts in them require memory.

It’s not much here — about 570MB — but in situations where more columns are needed for the query, they could get much larger.

And you know what can happen then. We’ll look at that in tomorrow’s post.

Thanks for reading!

Going Further

Common Query Plan Patterns For Windowing Functions: Partition By Parallelism

Posted on April 1, 2021May 16, 2022 by Erik Darling

Ramble On

Prior to SQL Server 2012, this blog post would be wrong. If you’re still on a version prior to 2012, ignore this blog post.

In fact, ignore every blog post and go upgrade those servers. Go on. Get out of here. Show’s over.

After SQL Server 2012, AKA the only versions that currently matter, this blog post is largely correct, and leans more towards correctness as you get to closer to SQL Server 2019.

Sliding scale correctness. Get on board.

Say It Slow

When windowing functions don’t have a Partition By, the parallel zone ends much earlier on than it does with one.

That doesn’t mean it’s always slower, though. My general experience is the opposite, unless you have a good supporting index.

But “good supporting index” is for tomorrow. You’re just going to have to deal with that.

WITH Comments AS 
(
    SELECT
        ROW_NUMBER() OVER
        (
            ORDER BY 
                c.CreationDate
        ) AS n
    FROM dbo.Comments AS c
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n = 0
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'));

With just an Order By in the windowing function, our query plan looks about like so:

Immediately after sorting data, our parallel streams are gathered. This is the end of our parallel zone, and it will occur regardless of if you’re filtering on the windowing function or now. I’m filtering on it here because I don’t want to spend any time retuning rows to SSMS.

Here’s an example of when a parallel zone is started again later:

WITH Comments AS 
(
    SELECT
        ROW_NUMBER() OVER
        (
            ORDER BY 
                c.CreationDate
        ) AS n
    FROM dbo.Comments AS c
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n > 100000000
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'));

Note that there’s no Top in this plan prior to the Filter.

You’ll see a Top generally when you filter on the windowing function with an equality or less-than predicate. Greater than seems to most often not end up with a Top in the plan.

Margaret Batcher

If we let Batch Mode run free, things turn out a little bit different.

WITH Comments AS 
(
    SELECT
        ROW_NUMBER() OVER
        (
            ORDER BY 
                c.CreationDate
        ) AS n
    FROM dbo.Comments AS c
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n = 0;

The Window Aggregate operator is within the parallel zone, unlike the Segment and Sequence Project operators in the Row Mode plan.

If we reuse the greater-than query from above while allowing Batch Mode to be used, we get a fully parallel plan.

Paddington

Of course, Partition By adds work, especially in Row Mode, and especially without a supporting index.

The nearly 11 second Row Mode plan compared to the 1.6 second Batch Mode plan doesn’t leave a lot of room for arguing.

It’s also worth noting here that Batch Mode Sorts (at least currently) will always sort on a single thread, unless it’s the child of a Window Aggregate operator, like in the above plan.

Tomorrow, we’ll look at how indexing can improve things, but not just row store indexes!

Everyone knows about those P(artition By) O(rder By) C(overing) indexes, but does that attention to ordering matter as much with column store indexes?

Tune in to find out!

Thanks for reading.

Going Further

Common Query Plan Patterns For Windowing Functions: Row vs Batch Mode

Posted on March 31, 2021May 16, 2022 by Erik Darling

Kickball

To start things off, we’re going to talk about query plan patterns related to windowing functions.

There are several things to consider with windowing function query plans:

Row vs Batch mode
With and Without Partition By
Index Support for Partition and Order By
Column SELECTion
Rows vs Range/Global aggregates

We’ll get to them in separate posts, because there are particulars about them that would make covering them all in a single post unwieldy.

Anyway, the first one is pretty simple, and starting simple is about my speed.

Row Mode

I’m doing all of this work in SQL Server 2019, with the database in compatibility level 150. It makes my life easier.

First, here’s the query we’ll be using. The only difference will be removing the hint to allow for Batch Mode later on.

WITH Comments AS 
(
    SELECT
        ROW_NUMBER() OVER
        (
            PARTITION BY
                c.UserId
            ORDER BY 
                c.CreationDate
        ) AS n
    FROM dbo.Comments AS c
)
SELECT 
    c.*
FROM Comments AS c
WHERE c.n = 0
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'));

Part of the row mode query plan will look like this:

The two operators that generate the numbering are the Segment and Sequence Project.

We’ll talk about the Sort later on. For now, we can see the segment “grouping” by UserId, and the Sequence Project description notes that it works over an ordered set.

You can probably guess why we need the Sort here.

Group is in quotes up there, because technically there’s no grouping. The “Segment1002” column in the Output List of the Segment is a computed column that marks the beginning and ending of each set of values. Likewise, the Sequence Project outputs “Expr1001”, which in this case is the calculated row number.

Batch Mode

In Batch Mode, there are three operators associated with windowing functions that get replaced with a single operator: the Window Aggregate.

The operators that get replaced are two we’ve already seen — Segment and Sequence Project, along with one we’ll see in a future post, the Window Spool.

We still need to Sort data for it without a supporting index. Gosh, those indexes sure are magickal.

The details of the Window Aggregate do still show a sequence generated, but we no longer see the “grouping”.

Baby Steps

Batch Mode kicks the pantalones off of Row Mode when it comes to window functions, but that’s not really the point of the post.

If you’re using a relatively modern version of SQL Server and also windowing functions, you should look at various ways to get Batch Mode processing alongside them.

Unless you’re on Standard Edition, probably.

In the next entry in this series, we’ll look at how the absence and presence of Partition By changes parallelism.

Thanks for reading!

Going Further

Common Query Plan Patterns: Introduction

Posted on March 30, 2021May 16, 2022 by Erik Darling

Muy Bueno

I’m writing this series because as interesting as single operators can be, you rarely run into interesting query plans that are a single operator. I don’t know exactly how many posts this will end up being. I have a list of about 15 things that I’d like to write about.

Anyway, I’d been thinking about something like this for a while, because being able to understand which part of a query generates which part of a query plan can help you focus in on what you need to work on. It’s also helpful to understand how different aspects of your database design and written queries might manifest in query plans.

You know, for performance.

Anyway, I hope you enjoy it. This post will be remarkably empty on first read, but will populate over time.

Thanks for reading!

Going Further

Detecting Memory Cache Pressure In SQL Server With sp_PressureDetector

Posted on March 29, 2021January 21, 2026 by Erik Darling

Cache Rules

Video Summary

In this video, I delve into the intricate relationship between queries and memory usage on SQL Server. Using my novel stored procedure, SP_pressure_detector, I demonstrate how to evaluate memory usage effectively by running a query designed to fill up the buffer pool and another that generates a significant 16 gigabyte memory grant. By analyzing these processes, viewers can understand how different queries compete for memory resources and the impact on overall server performance. I also explore practical scenarios where multiple memory-intensive operations are run simultaneously, illustrating the dynamic changes in memory allocation and the resulting effects on buffer pools and query execution.

Full Transcript

Erik Darling here with Erik Darling Data. And we recently got sponsored by BeerGut Magazine. Thank you for sending some beer so that I can hopefully someday be a centerfold in BeerGut Magazine. Appreciate you, your wonderful publication. Truly doing work that the journalistic world is afraid to do. Anyway, today I want to talk about, you know, queries and memory and how you can use my novel stored procedure, SP pressure detector, SP, was there an underscore? No, there’s an underscore. SP underscore pressure detector to evaluate memory usage on your SQL Server and how queries and different things might be fighting over it. Now, I have one query here that’s designed to just fill up the buffer pool. It hits the POST table, which is big, and cross-joins the VOTES table because it’s a demo and why not, I don’t care. And then I have this query down here, which will generate a 16 gig memory grant. One six. And we’re going to see how these things kind of play together. Now, if we look at how big the POST table is, we can see that it is 120 gigabytes. And that means it is larger than the amount of memory we have assigned to the server, which is just about at the 90, well, max memory, max memory is set to 90 gigs.

The VM itself has 96 gigs. If we go look over, oh, not there. If we go look over here, we can see 96 gigs. That’s 96 times 1024. We do correct math in these videos, by God. But anyway, let’s go look at what’s going on in the server right now, which is absolutely nothing. There is nothing happening. The server is wildly, wildly bored. If we look at some of the details here, we can see that the buffer pool is up around 86 gigs, just about close enough for me to say 86. If you want to argue, I don’t know, do it in the mirror, mirror in the bathroom. We can see that currently there are about three gigs of memory assigned to other things within SQL Server. What are they? I don’t know. Maybe just a whole bunch of little things like this. I don’t know. You’ll have to just deal with that too. But what happens when we run this store procedure.

And we come look over here. We’re never going to really get up to that 90 gigs of max server memory. Like we’re never going to, like the buffer pool is never actually going to hit that number. It’ll always stay just around the 86 gig mark. Sort of interestingly, if I turn lock pages and memory off, this thing will stay just around like 82, 83 gigs.

We do use a little bit more buffer pool when we have, when we have lock pages and memory turned on. I don’t know if that’s like the biggest deal in the world, but you know, it’s like, at least in this situation where it’s just like a few gigs difference. But, you know, it just may be something helpful to know. And if we look at what happens to memory when we run the memory thief, store procedure, and come look at SP pressure detector, we’re going to see immediately that this 16 point something gigs gets reserved.

And memory used is going to slowly, or the buffer pool is going to slowly decrease down to about 76 gigs. We’re also going to see the stolen server memory setting, and that’s going to slowly creep up as the query uses the memory that it’s been granted. We can see that the memory has been granted that 16 whatever gigs, and we can see that the memory that it’s used so far is only about 7 gigs.

But as that number creeps up, now this number, now we’ve used about 15 gigs, we’re going to see the stolen server memory setting, or stolen server memory metric creep up. So, stolen server memory is a snapshot of what is taking memory away from the buffer pool currently.

All right. If we kill this query, murder it, and it’s stopped, and we come look at SP pressure detector, we’re going to see this number disappear.

It goes away immediately. Stolen server memory also goes back down to about 3 gigs, but used memory hasn’t really recovered, right? We see that number is going to stay exactly the same because nothing is asking to get read back into the buffer pool and get things moving there.

All right. So, if we keep running this, nothing really is going to happen. Cool.

That’s good to know. We’ve set up some sort of, like, things in isolation that matter. Okay. Let’s abuse this server a little bit. Close this. We don’t need that open.

And we’re going to run four copies of our memory thief store procedure. And we’re going to run three copies of our buffer pool filling store procedure. And let’s see what happens.

All right. So, we’ve got our memory thieves. And we’ve got our pool fillers. And if we come over here and run this, this has jumped up to about 50 gigs. Stolen server memory is about 12 gigs.

But we’re going to see that the buffer pools slowly decrease as these memory grants start getting used, right? As these numbers start creeping up, these used memory numbers start creeping up, what happens? Buffer pools start shrinking down and down and down.

And stolen server memory creeps up and up and up. All right. So, you can see all that stuff happening there. And if we look at what’s going on with the queries, well, we’re going to see that the queries that need to, read a bunch of pages from disk into memory are, of course, going to be waiting on page IOLatch, SH.

All right. And we’re going to be waiting a long, long time on that. All right.

Reading pages from disk into memory. That’s what we have to do because we don’t have all the pages that we need in memory. And SQL Server is a curmudgeon about that sort of thing. All right. So, we come over here and look. We’re also going to have one query waiting on resource semaphore.

So, one of our queries that is asking for a memory grant is not getting it. It has requested that 16 gig grant, but SQL Server can’t give it out because it has reached the limit of how much it is willing to give out to queries. All right.

If we look over here a little bit, we will see that this thing is sitting in a queue, unable to do anything. And if we look down here, we’ll see that we have granted out all that. And even though we have almost the identical amount of memory that that thing has asked for available to give out, we’re not going to give it out to that query.

This is why we hit those resource semaphore weights. I’ve recorded a whole bunch of videos about that. But notice that what we end up with here is at some point, the buffer pool is shrunk down to 40 gigs.

And we have given out more memory to queries than we have kept in the buffer pool by about 9 gigs. All right. So, that’s fun stuff right there.

That’s really fun, interesting, messed up stuff. And if we keep running SP pressure detector, this situation isn’t really going to change much. If we just keep running this, eventually we’ll end up with more queries waiting on resource semaphore.

We’ll end up with the buffer pool just getting abused and beat up and dropping down to a way lower number. And, you know, things just not being fun for SQL Server. But that’s the new thing that SP pressure detector can show you.

I just added that recently while working with a client. This top result sit here. And as we kill off those queries, we stop things from running. Notice that we still have a very small buffer pool.

We still have not given anything back to the buffer pool. But we have reclaimed all the memory that we could give out to queries. So, that’s fun stuff there.

So, anyway, I think, you know, I’ve said it quite a few times in quite a few ways. But, you know, make sure when you are sizing your SQL servers, when you are tuning your SQL servers, and you’re trying to figure out why your SQL servers are slow and crappy and everything is falling apart.

Why doesn’t SQL Server scale? Well, probably because you didn’t give it enough memory. Because you are bad at guessing memory numbers.

Something. Bad memory fortune teller. But remember, you know, not only is the amount of memory that you need a function of how much data you have stored on the server, but it’s also a function of how much memory your queries are asking for.

If you want to figure out if SQL Server has enough memory, and using my store procedure, SP pressure detector is a great way to do that. Because it will help you determine if queries are waiting on memory.

You have resource semaphore weights, how much memory is generally taken away from the buffer pool. And, you know, you can look at the weight stats for individual queries and figure out, oh, we spend a lot of time waiting on reading pages from disk.

We spend a lot of time with queries waiting on, with the page IO latch weights. We spend a lot of time with queries waiting on resource semaphore weights, waiting to get memory. So there’s a couple of good things you can figure out.

Just with the hit magical F5 button, and you too can learn all sorts of horrible things about why your SQL Server is slow, and everyone is angry at you, and you will never, ever be a centerfold in BeerGut Magazine. Anyway, it’s Friday.

I hope you are too. And, I don’t know, I’m going to go enjoy my BeerGut Magazine subscription. And, I don’t know, I’ll see you in another video, or maybe you’ll just read me in another blog post.

Or, I don’t know, maybe once I become a world-famous model, world-famous centerfold, I’ll just quit all this SQL Server stuff and go live with the glitterati. Anyway, thanks for watching.

See you next time. Where’s that stop button? Where’s that stop button? And, I’ll just limit it to you in that very столpar,

Going Further

Temporary Object Concurrency With In-Memory tempdb Metadata In SQL Server 2019

Posted on March 26, 2021January 21, 2026 by Erik Darling

Moisty

Thanks for watching!

Video Summary

In this video, I delve into an interesting scenario comparing the performance of a stored procedure using a temp table versus one utilizing a table variable under high concurrency in SQL Server 2019. Surprisingly, despite the usual criticisms of table variables, my tests showed that they outperformed the temp table by nearly 30 seconds. However, I also explore the new feature introduced in SQL Server 2019—tempDB metadata being stored in memory—which was supposed to enhance performance further. Unfortunately, this feature didn’t provide a significant boost for either method during my tests. The video concludes with a hopeful note, suggesting that once everyone upgrades to SQL Server 2019, the use of temp tables could become more widespread and efficient, potentially leading to an end to my current dead-end job at Erik Darling Data. Who knows, maybe I’ll find myself sipping cocktails on a beach in Bora Bora instead!

Full Transcript

Erik Darling here, still stuck in this dead-end job at Darling Data, or Erik Darling Data, whatever that idiot named the company. And I think, was it a week ago? Maybe it was a week ago. It could very well have been a week ago. I recorded a video showing a race between a store procedure that uses a temp table and a store procedure that uses a table variable under very high concurrency, and found, interestingly, that the much maligned table variable did indeed get off to, did indeed cross the finish line much, much faster. And it’s full 30 seconds faster. But of course, you know, table variables do have a lot of downsides. And I covered them in that video. But you know, there’s stuff that, there’s stuff about them that can be unattractive at times. And SQL Server 2019 offers us this lovely new feature feature, where we can put some tempDB metadata in memory. By in memory, I mean using the Hecatonish in memory file group stuff for some of the views in tempDB. There’s lots of information about which ones are in there. And I have a feeling that more are going to end up there. So I’m not going to list them all now because that list will quickly be inaccurate. And I don’t want I don’t I don’t need any more inaccuracies in my in my video.

So I’m just going to leave that alone. You can you can read whatever current documentation or whatever documentation is current when you watch this, because you are going to watch this because you have no choice because you are also stuck in your dead end job. Just not a darling, Eric, darling, darling, Eric data. Dead. So just to recap a little bit what we do here, we have our first store procedure up here that uses a temp table, we create a temp table, we insert some stuff into the temp table, and then that’s it. We just create it jumping in. That’s all. And then one down here, where we declare a table variable, and insert into the table variable. And then that’s it. That’s that’s all we do.

It’s the end of the road. To wrap those up nice and neat and make calling them from an outside application easier. I have this store procedure that wraps both of them in up and gets us a number to pass into them. And then executes one or the other based on whatever we pass in here. So we’re going to So if we do that, we do that. If we do that, we do that. Good. So what I have SQL Server 2019 is of course, this lovely, lovely new setting. Tim DB metadata memory optimized, our value in use is one that means it’s been enabled. And now let’s see how things turn out this time around.

This time, though, I want to run the table variable test first, because I want to see if we can do any better. So remember, remember last time, this finished in about eight seconds. So let’s see if the in memory stuff helps the temp table variable. So I’m going to hit that. I’m going to go over to this window when SP who is active and just kind of watch stuff go through in the weight info column is still going to be remarkably empty. And we are done. How long did that take? That took 7.599. So I don’t know what is that a 400 millisecond or so difference. I don’t know. I don’t think that that was very helpful.

So it looks like table variables are still still fast, but the new feature doesn’t really help them spin any faster. Now let’s go back and let’s test temp tables because remember last time temp tables took about 38 seconds, nearly 40 seconds to run. So let’s get who is active ready. Let’s clear that screen. So we have no bias or judgment going on there.

And we’ll kick that off and let’s see what SP who is active tells us. Ooh, the weight info column here is no, we are not seeing all those page latch EX weights. And we’re going, we’re going, and we’re done. And that was not 38 seconds, was it?

That was 11.7 seconds. So pretty cool that under real high concurrency, we can get much better. Tempt table performance from SQL Server 2019 with our fancy in memory, temp DB metadata feature. So that’s nice. I like that. I enjoy that.

And I look forward to, you know, being able to use temp tables all willy nilly as soon as everyone just goes ahead and goes, goes ahead and upgrades 2019 or all your problems are solved. And you probably won’t even, probably won’t even need me anymore.

I can go, go pursue my dreams of getting out of this dead end job. And I don’t know, maybe I’ll, maybe I’ll open a bar on the beach in Bora Bora. And we’ll look into that.

And we’ll look into that. we’ll look into that. So,

Going Further

Updates To sp_PressureDetector

Posted on March 25, 2021May 16, 2022 by Erik Darling

These Colors Don’t Gum

If you head to my GitHub repo, there’s an update to sp_PressureDetector, a free script I publish to help you figure out when your server is under CPU or memory pressure.

While working with a client recently, I found it useful to illustrate how much of their memory was used by different memory consumers.

Buffer pool
Total other
Top 5 other

It helps put in perspective just how many different things are asking for a piece of your precious RAM.

This is a bad example from my personal ~~server~~ laptop, but you get the idea.

Anyway, that’s that. clink over to my GitHub repo to download it.

Thanks for reading!

Going Further

Creating An Agent Job To Update Statistics Using Ola Hallengren’s Scripts

Posted on March 24, 2021May 16, 2022 by Erik Darling

Hubba Hubba

Over in my GitHub repo, I’ve added a file that will create an Agent Job to update statistics using Ola Hallengren’s IndexOptimize script.

It’s something I hand out enough that I figured people might be interested in it. Currently, it’s not a default offering from Ola, it’s uh… custom code.

There are lots of people who should be using this, too.

Everyone

Because index maintenance scripts don’t measure a generally useful KPI, and one of the main benefits of index rebuilds is the statistics update.

Mindful

Some thing to keep in mind here:

You need to be using a relatively new version of Ola’s scripts
This script utilizes the @StatisticsModificationLevel parameter, added 2018-06-16
That parameter is currently set to 5, and you may need to change that depending on your environement

There are some things you’ll need to change in the script, if you’re doing anything really custom:

It targets the master database
It’s owned by the sa account
It’s set to run at midnight
It has no failure emails or alerts set up

This is a very vanilla starting place. It’s up to you to make it yours.

To report any issues with Ola’s scripts, head over to this GitHub repo.

To get the Agent Job script, head over to my GitHub repo

Thanks for reading!