sql temp table Archives | Page 50 of 138

Compiles! Recompiles! Stored Procedures! Temp Tables! Extended Events! In SQL Server

Posted on May 21, 2024January 22, 2026 by Erik Darling

Compiles! Recompiles! Stored Procedures! Temp Tables! Extended Events! In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the intricacies of stored procedures, temp tables, compiles, recompiles, and query hints—essentially packing as many fascinating topics as a human hand can hold. Using `spHumanEvents`, an extended events wrapper I developed to simplify tracking for SQL Server users, we explore how to monitor and manage these processes effectively. By running a series of queries with different hints in a loop, we uncover the nuances between recompiles and compiles, highlighting the potential benefits of using the `KEEP PLAN` hint to stabilize query plans across multiple executions. This exploration not only sheds light on common issues but also offers practical insights for optimizing stored procedures that rely heavily on temp tables.

Full Transcript

Alright, Erik Darling here with Darling Data and this happy, friendly, amazingly kind video, this community spirited video, we’re going to talk about a cacophony of fascinating things. Namely, stored procedures, temp tables, compiles, recompiles, and query hints. It’s as many things as I can fit on a hand. If I were blessed by any of the gods with additional fingers, we would talk about more things today, but I’m all out of fingers, so you get what you get. In order to track compiles and recompiles, we’re actually going to do, we’re going to look at this three different ways. One of those ways is not going to be query store because query store is somewhat uninteresting in this way. We’re going to look at, we’re going to use spHumanEvents in two different ways. We have one event up here to track compiles. We have one event up here to track recompiles. Alright, and then we have this query plan clash, plan cache query that we’re going to use to track the same thing in the plan cache. Cool, great, wonderful. What are we tracking exactly? Well, it’s really better if I start this loop off and then we talk through exactly which specific things we’re dealing with here.

So what I need to do is in very quick succession. Not too quick. I gave myself a little room to wiggle. I do like having room to wiggle. I’m going to start this off here. spHumanEvents, that’s my store procedure that I use to make extended events a little bit easier on the average SQL Server user because Lord knows Microsoft didn’t make it easy. And as you all know, I love and care about you far more than Microsoft ever will. They’re a nameless, faceless, conglomerate bunch of people who say no. Me, I say yes. Darling Data, we say yes to you.

So I’ve got a store procedure and this is the very store procedure that’s running in the loop that’s executing right now, if you would believe that. And what this store procedure does is it takes some parameters, obviously. That’s what store procedures typically do. They take a parameter. It’s like we here at Darling Data will take a drink. Store procedures will take a parameter. We create a table called filtered posts. We insert into that table the results of this query where our wonderful parameters are used, where SQL Server drinks greedily from our parameters. And then we run the same parameters. And then we run the same query, essentially, in four different ways. So the first way that we run this query is with no hints whatsoever, just looking for where the score column in our temp table is greater than zero.

And notice that I’m doing the greater than select zero thing here. The reason that I’m doing that is to avoid simple parameterization, not because it actually interferes with this demo, but just because a lot of people see it and say, oh, that looks weird. What is that? And then everything gets derailed, hopelessly derailed. We have another iteration of the same query, except this one has a recompile hint on it. So this one we’re explicitly saying, SQL Server, please recompile this plan. Tear down that old plan. And then we have two more queries that we run. One of them uses the keep plan hint right here. And I wanted to use this to see if keep plan would get us anything in this situation.

Not to jump too far ahead, but keep plan really seems to work better for like actual tables and like an actual schema, not like 10 tables. And then we have keep fixed plan down here. All right. Wondrous, wonderful, amazing. You believe that I do this for free for your benefit. You believe that the things that I do for you, all the typing that I do for you, brothers and sisters. And so, well, I mean, there’s our loop, right? Our loop is finished. And if we go get the results from this thing back and we look at what happened, we can see this loop ran 21 times with post type ID one.

And then another 21 times with post type ID two for a total of 42 trips to that store procedure, right? What this loop does is what as long as post type is less than three, and that’s not a weird heart. I don’t play those games. I’m married to the data. I can’t go making hearts at every single thing in SSMS.

And then while end date is less than this, we do this and we increment our dates by three months at a go. And then when we finally reach our limit here, then we reset our dates and we set post type ID one higher. And only on that trip around do we raise our loop counter.

So 21 times for post type ID one, 21 times for post type ID two, which is a total of 42 trips to that store procedure. I did not have 42 drinks. You are watching me. There’s no way I could have had 42 drinks during the execution of that store procedure.

Though Lord knows sometimes I wish I could. If we come over here and we look at the compiles, we are going to see a whole bunch of statistics gathering in tempDB. And if we expand this a bit, we’ll see that this is all coming from the filtered post table in our store procedure.

All right. This is all compiling filtered post statistics. And we have 35 rows of that, I guess. And we have most of these just have one compile, but this one at the top has eight compiles.

I’m not sure why this one thinks it’s so special that it can go and compile eight times in total, but it went and did it anyway. And if we look at the results of the recompile extended event that we had in here, look at what we’re going to see. This is actually interesting to me.

I found this part fascinating. Utterly fascinating. You might be wondering, Eric, why did you find this utterly fascinating? Well, let’s look at what we have some recompile causes in here, right?

For two of them, we have statistics changed. For one of them, you can probably guess which one this is. We have option recompile requested, right?

Which is great. Love the capitalization there. Capital O option, all lowercase recompile. And for the two down here, we have deferred compile. All right.

All right. Cool. Those all happened in Stack Overflow 2013, which is exactly where we executed our store procedure. They were all from the store procedure that we’re talking about. But if we come over here just a wee little bit, and I have no idea why one of them has that little extra less space in it.

A little strange. And we’ve got to do a little bit of work to get this expanded out to where we want it. Because what we want to see are which compile, which query text with which hints or no lack of hints, ended up with all these compiles and all this stuff going on.

And now let’s slide over this way. Since we’ve got this all framed up nicely here, we have our first query, which is no hints, which had 42 total recompiles and almost 10 seconds of recompile CPU, on average taking 232 milliseconds to recompile this query.

All right. That’s a fairly crazy thing, right? And remember, the recompile cause, and this is statistics changed.

Down here, where we have our keep plan hint, we still ended up with 42 total recompiles, but we just used far less recompile CPU on that. Almost nothing there.

This is pretty wild that just like a regular query requested that required, not requested, required. Didn’t just say, can I have like 10 seconds of recompile CPU? And SQL Server was like, yeah, I guess.

It was just like, no, I need to do this. If I don’t get this, I’m going to go crazy. And down here at the very bottom, we have our two other queries, which only recompiled once, which probably means they just kind of compiled once or something, I guess.

I’m not entirely clear on all of the inner workings of how Extended Events tracks these things. Nor do I have the moxie to care too much about all of that. So, you know, that’s fun.

Sorry if you don’t like it. But there’s only so much time in a day, and there’s only so much free I can give. So these two here, the keep fixed plan did like no work, and the insert select into the temp table did absolutely no work. Now, if we come over here and we run our plan cache query, we’re going to see nearly the same thing, except it’s going to be a little misleading.

I mean, sure, these three up here have crazy high plan generation numbers on them, probably much higher than they should. Well, I’m not going to say much higher than they should be. Just like 129.

We only executed the thing 42 times. I don’t know. I don’t know. Pretty wacky stuff.

Pretty wacky stuff. But what’s misleading is that these queries all look like they only executed once. So that’s because of all the compile, recompile, stats change, go get stats, go make a new plan, kick out the old plan, we don’t need it anymore, get the new plan in here. And all that stuff, which is, I don’t know, can be aggravating.

To be perfectly honest with you, that stuff can be quite aggravating. And the two queries down at the bottom, this is just kind of to show you that the plan cache does agree mostly with the extended event recompile stuff that we saw, where these queries both have 42 executions, right?

And they did very little on the recompile tip. So the moral of the story here kind of is, if you have stored procedures, and those stored procedures feed data into temp tables, and you’re having trouble with, like, high recompilations, and you want to, and you, like, you’re, like, confident and comfortable in the query plan that you’re getting across all those executions, which can be, granted, tough to figure out if you’re constantly recompiling.

But if you’re getting the same plan over and over again, right, and you want to reduce all that compile overhead, right? You have stored procedures that execute a ton, they use temp tables, you put, like, a relatively stable amount of data in those temp tables, and you’re just, like, SQL Server, just use a decent plan, just reuse it, I don’t care, then the keep fixed plan hint can be pretty useful to tamp down the recompiles and potentially take a lot of the stress and strain out of all the many, many executions of that stored procedure.

Will that be perfect in every case? Of course not. But we live in a complex, data-driven database, data face, data world, and it’s up to us to decide when it’s appropriate to do these things. It’s up to us to figure out when these things are causing problems and come up with a reasonable fix for them.

In this case, I would recommend for the query in that stored procedure that we stick with the keep fixed plan hint because that would tamp down on the recompilations, and it would give us a nice stable execution plan that not only would we be able to reuse, right, but we would be able to track exactly how that plan is performing over time.

If we find that that plan is not performing so well, we could, you know, recompile the stored procedure and let SQL Server come up with a new plan the next time, and maybe that one will be better. We always, we pray that SQL Server will make good choices, don’t we?

Lord knows we don’t. It’s up to SQL Server to make these good choices. So, this is just kind of a fun little demo that I put up with while investigating something else entirely, but I thought it was worth sharing.

If you’re not familiar with SP Human Events, the link to my GitHub repo where it lives will be in the show notes, and you’ll be able to go and install it in all of your servers and run it in all its glory and be able to track down these interesting problems the same way I do, this professional stored procedure-y way.

So, anyway, that’s good for today, I think. I hope you enjoyed learning about compiles, recompiles, temp tables, stored procedures, and extended events. I don’t know if that was the same five from before, but again, we’ve hit the magic number, and this is also how I wave goodbye.

So, if you enjoyed this video, I do like thumbs-ups and… Family-friendly thumbs-ups. Do not stick our thumbs where they don’t belong.

I like comments, and I like subscribers. So, if you like this and you like me, then subscribe to the channel and you get more of me and more of this. And it’s kind of a win-win for everybody, because then I get what I like, you get what you like, and we’re all the happier for it.

We’re all better off, aren’t we? So, as usual, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. And I will see you in another video, I guess probably tomorrow, when I’ve thought of something else to talk about, which amazingly I always do.

So, thank you for watching.

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.

A Mild Annoyance With MERGE Statements And Triggers

Posted on May 20, 2024May 20, 2024 by Erik Darling

I’m No Expert

I will leave the finer points of the problems with MERGE statements to the Michaels and Aarons of the SQL Server world.

This is just a… But why? post about them, because I’m honestly a bit puzzled by this missing implementation detail.

To get us to the point, I’m going to use a code snippet (with embellishments) from Aaron’s post here.

We’ll be starting with this table and trigger from the linked post, with a couple small tweaks to satisfy my OCD:

CREATE TABLE 
    dbo.MyTable
(
    id integer
);

INSERT 
    dbo.MyTable 
VALUES
    (1),
    (4);

CREATE OR ALTER TRIGGER 
    dbo.MyTable_All
ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    IF ROWCOUNT_BIG() = 0 RETURN;
    IF TRIGGER_NESTLEVEL() > 1 RETURN;
  
    PRINT 'Executing trigger. Rows affected: ' + RTRIM(@@ROWCOUNT);
    
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
      PRINT '  I am an insert...';
    END;
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
      PRINT '  I am an update...';
    END;
    IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
      PRINT '  I am a delete...';
    END;
END;

After all, one of the best ways to make sure you get code right is to copy and paste it from the internet.

Quiet On The Set

As much as we all love to dunk on MERGE, like cursors, heaps, and UDFs of various types, they did give you some neat options with the OUTPUT clause, like the $action column, and the ability to get columns from other tables involved in the query. You can’t do that with a normal insert, update, or delete when using the OUTPUT clause, though I think it would be cool if we could.

Working a bit with the code linked above, here’s an expansion on it showing the additional OUTPUT capability, but this is also where my annoyance begins.

BEGIN TRANSACTION
    DECLARE
        @t table
    (
        action varchar(6),
        i_id integer,
        d_id integer,
        s_word varchar(5)
    );

    SELECT
        mt.*
    FROM dbo.MyTable AS mt;

    MERGE 
        dbo.MyTable WITH (HOLDLOCK) AS Target
    USING 
        (
            VALUES
                (1, 'one'),
                (2, 'two'),
                (3, 'three')
        ) AS Source (id, word)
    ON Target.id = Source.id
    WHEN MATCHED 
    THEN UPDATE 
           SET Target.id = Source.id
    WHEN NOT MATCHED 
    THEN INSERT
           (id) 
         VALUES
           (Source.id)
    WHEN NOT MATCHED BY SOURCE 
    THEN DELETE
    OUTPUT
        $action,
        Inserted.id,
        Deleted.id,
        Source.word
    INTO @t 
    ( 
        action, 
        i_id, 
        d_id,
        s_word
    );
    
    SELECT
        t.*
    FROM @t AS t;

    SELECT
        mt.*
    FROM dbo.MyTable AS mt;
ROLLBACK TRANSACTION;

You likely can’t guess what I’m sore about just looking at this, because this isn’t what annoys me.

This is all fine, and rather a nice showing of capabilities for an oft-maligned (by myself included) bit of syntax.

The problem is really in the trigger.

What’s Wrong With Triggers?

If you work with trigger code enough, you’ll get used to seeing:

Catch all triggers that do a lot of gymnastics to differentiate insert from update from delete
A few separate triggers to catch each modification type separately, and still do some checking to verify

The problem is that in any trigger, the $action column is not directly exposed for use to determine the action of a MERGE statement.

Sure, you can add a column to a table to track it, or some other hacky workaround, but I consider it a quite poor design choice to not have the $action column as a part of the Inserted and Deleted virtual tables.

Having it there would also benefit generic modifications that are captured by triggers in some manner to make the absolute type of modification quite clear to query writers.

Thanks for reading!

Going Further

Join @bobwardms At SQL Saturday Boston For The Azure Workshop for SQL Professionals (@NESQLServer)

Posted on May 17, 2024May 16, 2024 by Erik Darling

All’s Well That Friends Well

The nice folks at the New England SQL Server User Group (w|t) had me in town a week ago to present my workshop, The Foundations Of SQL Server Performance.

We had about 40 folks show up — which ain’t bad for a random Friday in May — including one attendee from Nigeria.

Not just like, originated in Nigeria. Like, flew from Nigeria for the workshop. That’s probably a new record for me, aside from PASS Precons where folks are already headed in from all corners.

Speaking of PASS Precons — me and Kendra are double teaming your Monday and Tuesday — have I mentioned that lately?

As a favor to some dear friends, here’s a promo code to get $125 off Bob’s Friday Precon, The Azure Workshop for SQL Professionals.

The precon takes places on October 4th, and the SQL Saturday is October 5th. You can register separately for the SQL Saturday only here.

If you want to catch Bob’s Friday workshop, use the QR code below, or register here and use the code PPP50 to get your discount.

Thanks for reading!

Going Further

Two Ways To Tune A Slow Query In SQL Server

Posted on May 16, 2024May 21, 2024 by Erik Darling

Like All Assumptions

You and Me might feel like the lower back end of a thing if we’re tuning a query that has other problems. Perhaps it’s running on one of those serverless servers with half a hyper-threaded core and 8kb of RAM, as an example.

When I’m working with clients, I often get put into odd situations that limit what I’m allowed to do to fix query performance. Sometimes code comes from an ORM or vendor binaries that can’t be changed, sometimes adding an index on a sizable table on standard edition in the middle of the day is just an impossibility, and of course other times things are just a spectacle du derrière that I’m allowed to do whatever I want. You can probably guess which one I like best.

This post is about the two other ones, where you’re stuck between derrière and ânesse.

For the duration of reading this, make the wild leap of faith that it takes to embrace the mindset that not everyone who works with SQL Server knows how to write good queries or design good indexes.

I know, I know. Leap with me, friends.

The Query And Execution Plan

Here’s what we’re starting with:

SELECT TOP (10)
    DisplayName =
       (
           SELECT
               u.DisplayName
           FROM dbo.Users AS u
           WHERE u.Id = p.OwnerUserId
       ),
    p.AcceptedAnswerId,
    p.CreationDate,
    p.LastActivityDate,
    p.ParentId,
    p.PostTypeId,
    p.Score,
    p.CommentCount,
    VoteCount =
        (
            SELECT
                COUNT_BIG(*)
            FROM dbo.Votes AS v
            WHERE v.PostId = p.Id
        )
FROM dbo.Posts AS p
ORDER BY
    p.Score DESC;

And resulting plan:

We can surmise a few things from this plan:

If there are good indexes, SQL Server isn’t using them
That hash spill is some extra kind of bad news
Spools remain a reliable indicator that something is terribly wrong

Okay, so I’m kidding a bit on the last point. Sorta.

The Query Plan Details

You might look at all this work that SQL Server is doing and wonder why: With no good, usable indexes, and such big tables, why in the overly-ambitious heck are we doing all these nested loop joins?

And the answer, my friend, is blowing in the row goal.

The TOP has introduced one here, and it has been applied across the all of the operators along the top of the plan.

Normally, a row goal is when the optimizer places a bet on it being very easy to locate a small number of rows and produces an execution plan based on those reduced costs.

In this case, it would be 10 rows in the Posts table that will match the Users table and the Votes table, but since these are joins of the left outer variety they can’t eliminate results from the Posts table.

The row goals do make for some terrible costing and plan choices here, though.

This all comes from cardinality estimation and costing and all the other good stuff that the optimizer does when you throw a query at it.

The Query Rewrite

One way to show the power of TOPs is to increase and then decrease the row goal. For example, this (on my machine, at this very moment in time, given many local factors) will change the query plan entirely:

SELECT TOP (10)
    p.*
FROM
(
    SELECT TOP (26)
        DisplayName =
           (
               SELECT
                   u.DisplayName
               FROM dbo.Users AS u
               WHERE u.Id = p.OwnerUserId
           ),
        p.AcceptedAnswerId,
        p.CreationDate,
        p.LastActivityDate,
        p.ParentId,
        p.PostTypeId,
        p.Score,
        p.CommentCount,
        VoteCount =
            (
                SELECT
                    COUNT_BIG(*)
                FROM dbo.Votes AS v
                WHERE v.PostId = p.Id
            )
    FROM dbo.Posts AS p
    ORDER BY
        p.Score DESC
) AS p
ORDER BY
    p.Score DESC;

You may need to toggle with the top a bit to see the change on your machine. The resulting plan looks a bit funny. You won’t normally see two TOPs nuzzling up like this.

But the end result is an improvement by a full minute and several seconds.

Because the inner TOP has a bigger row goal, the optimizer changes its mind about how much effort it will have to expend to fully satisfy it before clenching things down to satisfy the smaller TOP.

If you’re only allowed quick query rewrites, this can be a good way to get a more appropriate plan for the amount of work required to actually locate rows at runtime, when the optimizer is dreadfully wrong about things.

The Index Rewrite

In this case, just indexing the Votes table is enough to buy us all the performance we need, but in my personal row goal for completeness, I’m going to add in two indexes:

CREATE INDEX 
    v 
ON dbo.Votes 
    (PostId) 
WITH 
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

CREATE INDEX 
    p 
ON dbo.Posts 
    (Score DESC, OwnerUserId) 
WITH 
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

Going back to the original query, we no longer need to play games with the optimizer and pitting TOPs against each other.

This is obviously much faster, if you’re in the enlightened and enviable position to create them.

Perhaps you are, but maybe not in the exact moment that you need to fix a performance problem.

In those cases, you may need to use rewrites to get temporary performance improvements until you’re able to.

Thanks for reading!

Going Further

Actual Execution Plans Finally Show Lock Waits!

Posted on May 15, 2024May 2, 2024 by Erik Darling

Shorty

This is a short post. I know you’ve been somewhat spoiled by longer posts and videos lately!

Just kidding, y’all don’t pay attention (with the exception of Kevin Feasel), so maybe this will be consumable enough for even the most squirrel brained amongst us.

A long time ago, I complained that wait stats logged by actual execution plans don’t show lock waits. That seemed like a pretty big deal, because if you’re running a query and wondering why sometimes it’s fast and sometimes it’s slow, that could be a pretty huge hint.

But now, if you run a query that experienced lock waits, you can see that in the details. Just highlight the root operator, hit F4 or right click and go to Properties, and look under the wait stats node, you’ll see this:

When did this get added? I have no idea.
How far was it back ported? I have no idea.

I could look on VMs with older versions of SQL Server, but it’s dinner time. Or as they call it in Saskatchewan, “supper”.

Thanks for reading!

Going Further

T-SQL Tuesday: Your Favorite Job Interview Question

Posted on May 14, 2024May 14, 2024 by Erik Darling

Hello Yes Nice To Meet You

This month we have an invitation from Kevin Feasel.

Since Kevin is often kind enough to link to my posts via his Curated SQL feed/aggregator, I figured I’d add to the SEO pyramid scheme this month.

As part of my consulting services, I often help clients decide if they could keep a full time database person (there are too many potential titles to list, here) busy for 40 hours a week for the foreseeable future.

If the Magic 8-Ball comes back with a Yes, I’ll also help them write or review their job posting, and screen candidates. If a resume makes it past various detectors and doesn’t end up in the randomly generated unlucky pile, I’ll interview them.

Me. Personally.

Not in-person, though.

There’s not enough armed security in the world for me to get that close to HR.

The Production DBA Question

If someone is going to be in charge of production DBA tasks, I’ll of course ask questions about experience with whatever model is in use, or is to be implemented by the company.

So like, if the company has Availability Groups, or wants Availability Groups, we’ll talk about those.

For flavor, I’ll also ask them why anyone would be so insane as to not just use a Failover Cluster with Log Shipping.

But the real question and answer that tells me if someone knows their business is this: When you set up backups, how often do you take log backups?

If anyone says “every 15 minutes” without any further clarification or qualification, they immediately go into the “No” pile.

See, 15 minute log backups are a meme in the same Pantheon as 5% and 30% for index fragmentation. Neither answer shows any deeper understanding of what exactly they’re doing.

Log backup frequency is a business requirement based on RPO goals (Recovery Point Objective). If your RPO goal is less than 15 minutes of data loss, 15 minute log backups don’t meet that goal.

And anyone who says that Availability Groups help meet RPO goals gets their resume burned in front of them.

The Developer DBA Question

For anyone responsible for performance tuning, I need to make sure that they’re staying current with their learning and techniques.

I’ll show a lot of pictures of queries and query plans, ask about various anti-patterns and tuning techniques, but my absolute favorite is to show them pictures of query plans.

Actual execution plans, as it were.

Why is this important? Because at least half of the people I show this to will totally ignore the operator times and start talking about costs.

If you’re looking at a plan where this much information is available, and all you can dredge up to troubleshoot things are estimated costs, I’ll probably mail you a copy of this.

And I’m not kidding here — it shows a complete lack of attention and growth — what’s the point of calling yourself a performance tuning expert if your expertise peaked in 2008?

There Are Good People Out There

They may not spend every waking moment writing, recording, speaking, or chasing MVP status.

And that’s fine — I don’t care if you partake in that stuff or not — there are plenty of very smart people out there who don’t file quarterly paperwork or schedule posts months in advance.

What I care about is that you aren’t just an automaton seeking a new place to go through the motions in.

Thanks for reading!

Going Further

Join Me And @Kendra_Little At @PASSDataSummit For 2 Days Of SQL Server Performance Tuning Precons!

Posted on May 13, 2024May 3, 2024 by Erik Darling

I am a heading

Thanks for watching!

Going Further

Why Performance Tuners Need To Use The Right Type Of Join In SQL Server

Posted on May 10, 2024January 22, 2026 by Erik Darling

Why Performance Tuners Need To Use The Right Type Of Join In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the importance of writing queries in a logically correct way, highlighting common pitfalls and offering practical advice to improve query performance. I share a client-inspired example where a left join was unnecessarily used, leading to a poor row-to-second ratio and inefficient parallel merge joins in the query plan. By changing the join type to an inner join without adding any indexes or making other changes, we significantly improved the query’s execution time from 9 seconds to just 3 seconds. This example underscores the importance of understanding the intent behind your queries and ensuring that the types of joins you use are necessary and appropriate for the task at hand.

Full Transcript

Erik Darling here with Darling Data. Darling Data. I don’t know why everything that attempts to do a transcription of my videos insists that I work for Darlene Data. Clearly saying Darling, not Darlene. Starting to hurt feelings a little bit. But today we’re going to talk about the importance of writing your queries in a logically correct way, which sounds like a very obvious thing that everyone should do and know how to do, but not everyone does. There is a profound sense of safetyism with people who write queries, either on their own or via an ORM, aka the lazy way. And this is a client-inspired query tuning exercise that I want to go through with you. Now, not just every query requires a left join. A lot of people freak out, think about, I don’t know what, but they just randomly sprinkle left joins everywhere just in case. And that’s not always necessary. Now, this tactic isn’t always going to work out. Sometimes you might need a left join. Sometimes changing the type of join might not make any sort of profound difference to your query, but it’s an important thing to keep in your brain.

as you are assessing queries that you are assessing queries that you are working on, either to first first write, first birth into the world, or one that you’re working on trying to make faster. All right? It’s a good thing to keep in mind. Think about the intent of your query, and if you actually need the type of join that you’re writing. All right? It’s a smart thing to do. So, this query has a couple supporting indexes.

They’re mostly supporting indexes. There’s one index that’s not supporting in here, and that’s part of why the query isn’t as fast as it could be, but you can’t always just add an index immediately. Some people have change controls. Some people are unfortunate enough to be on standard edition and not be able to just create indexes when handsome young consultants are working with them, and they need to do it later because they don’t want to block things because standard edition is proof that Microsoft hates you. So, let’s look at this query. And what we’re doing is looking for the top 10 users ordered by a few things down here that kind of take the need for a left join out of the equation.

We’re looking at people with the highest question score, the highest answer score, and the most posts. Now, if this were like the first day that Stack Overflow existed, and maybe like the first hour, and let’s say we had like 11 users, and we were really afraid that like two of them hadn’t posted anything yet, then you would probably need a left join to round out the like 10 rows that we need to make this report, right? Or make this page or whatever. Make this, look at our top 10 users. They’re amazing. Whatever.

But, like, this is the Stack Overflow 2013 copy of the database. By this writing, there were like 2.5 million rows in the users table, and the odds are pretty good. I’m not much of a bookie, but the odds are pretty darn good that 10 of them have made posts. Questions and answers. Questions and answers jammed into one table. Absolute madhouse. Who would do that? Who would establish a parent-child relationship all in one table? I don’t know. It’s probably on my list of things to do to split out questions and, well, I guess, I was going to say questions and answers, but questions and every other kind of post into two separate tables and demo how queries are better when you normalize like a decent human being.

But that’s for another day. Day far, far, far, far, far away from now. So, what we’re going to do is just run this query. Just as it is, we’ve got query plans turned on, so we don’t need to worry too much about missing anything important here. And we have a left join in our query, and we’re going to run this, and we’re going to wait 8 or 9 seconds. I always forget. Oh, that’s my hand. 4, 5, 6, 7, 8. Okay, 8 seconds.

It’s a pretty long time to get 10 rows, right? Not a good row-to-seconds ratio. I keep stressing the importance of the row-to-seconds ratio. 8 seconds for 10 rows? Very bad. It’s like a little bit more than one row a second. It’s a terrible, terrible ratio.

Again, not much of a bookie, but I would not want to put money on that horse. Might want you to put money on that horse, so I make some money, but, you know, whatever. So let’s look at this query plan. We can see from the query plan itself, it actually took about 8 and a half seconds.

So my initial estimate of 9 seconds was only off by 500 milliseconds. That’s actually a pretty good call there. It’s not like I run this thing 15 times or anything, right? Definitely not.

And this gets a pretty shabby query plan. Part of it is, of course, because I don’t have any kind of good index on the comments table, and so we spend about 2 seconds in this branch of the plan aggregating some data, and then aggregating data some more, and then sorting data.

Now, the reason why this is one of my least favorite types of query plans is because there are not one, but two parallel merge joins. Uno dos, right?

And personally, I think that the optimizer should cost parallel merge joins out of existence. They’re nothing but trouble. They are hassles, and they have caused so many performance problems that I’ve seen in my query tuning time that I just don’t know why they continue to exist except to keep my bar tabs paid, I guess.

So the problem with merge joins is that they expect sorted input, and when you expect sorted input, you create intra-thread dependencies, right? So parallel plan, you have multiple threads.

Those threads all rely on each other, putting stuff through things in order, especially around parallel exchanges. If things go really bad in a parallel plan, you’re going to end up with intra-query parallel deadlocks, you said.

You’re so smart. You could also end up with just exchange spills. Either way, you’re not in for a good time performance-wise. I hate these things.

I wish they’d go away. I wish it was just parallel nested loops or hash joins. Just, like, if it can’t be an adaptive join, it shouldn’t be in the picture. Get rid of it.

So anyway, I’m, like, half-joking. I suppose there’s a use for them. Aside from keeping my bar tabs paid, I just haven’t found it yet. So this query takes nine seconds. A lot of bad stuff goes on.

And what we’ll notice is if we write this query in a way that more accurately expresses what we’re looking for, because if we’re trying to find the users who may have the top ten most impact on the site, they’re people who have had to have made posts, right?

They’re not going to be non-posting people with high question or answer scores or a high number of posts. We’re just not going to find them. See, they’re people who have had to have done something in the database.

They must have. They must have. So we’re going to change that join to an inner join without adding any indexes, without changing anything else. We’re not going to update statistics.

We’re not going to defragment anything. We’re not going to restart SQL Server. We’re not going to recompile, optimize for unknown. We’re not going to do anything, any of these bizarre things that I see people do when trying to figure out why a query is slow. So we’re just going to change that left join to an inner join, because there’s no way we need to worry about preserving people who haven’t posted anything.

And this query immediately goes from eight and a half, I’m going to say nine seconds. Nine seconds. I’m going to round up in my favor.

Nine seconds down to three seconds just by doing the right type of join. Now, you might notice that the shape of the plan changed quite a bit, and you might notice that certain join types will remain nameless, mainly parallel merge joins, have been stricken from the record. We got a couple hash joins.

We got a nested loops join. And this is more typical of the type of parallel plan that I want to see when I’m tuning queries. I almost never want to see a parallel merge join unless I want to write a good demo about parallel merge joins being terrible.

So just to repeat myself a little bit here, just to make sure that this is utterly clippable, if anyone decides to stitch this or whatever they call it. So when you’re writing a query, when you’re tuning a query, make sure you understand the intent of the query. You don’t need to understand absolutely all of the business logic.

But you should understand the intent of the query and use some of your God-given gray meats to figure out if the types of joins you’re requesting SQL Server to do are necessary or even correct. Right? Right?

Because a lot of the times when I see people using left joins is because they had no idea what they were doing. And they were just like, I see a lot of other left joins here. I’m going to do a left join everywhere. And you can end up with, you know, not great performance if you are not writing queries the correct way. Now, again, inner joins won’t always be faster.

Left joins won’t always be slower. There may be times when that crosses over. But when you’re analyzing query stuff, you really do need to keep in mind that these choices of joins do matter. Right?

They do matter when you’re writing queries. And sometimes how much they matter, like to query correctness, can also have an impact on how fast your query is. So it’s a really good thing to keep in mind, to pay attention to, to make sure you understand what your query actually needs to do so that you don’t add superfluous join types to your query and end up with a bunch of parallel merge joins.

It’d be sad for you. I don’t know. Maybe you’d have to hire me, which wouldn’t be so sad.

Keep those bar tabs paid. So, thank you for watching. Hope you enjoyed yourselves. Hope you learned something. If you like this video, thumbs up is the absolute best way to show that you like this video.

If you like SQL Server content about performance tuning, other things, bar tabs, sports betting, you know, stuff like that. Subscribe to my channel so that you can get a notification every time I talk about performance tuning or bar tabs or sports betting. You know, got to keep things lively over here.

Anyway, I guess that’s probably about close enough. Thank you for watching.

Going Further

User Experience Under Different Isolation Levels In SQL Server

Posted on May 9, 2024January 22, 2026 by Erik Darling

User Experience Under Different Isolation Levels In SQL Server

Thanks for watching!

Video Summary

In this video, I explore the nuances of isolation levels in SQL Server, specifically focusing on read committed and read committed snapshot (RCSI) isolation modes. Given my recent foray into unexpected hot weather, it’s clear that even my brain can get a bit foggy when things heat up! However, today’s content is anything but lukewarm. I delve into how these isolation levels behave differently under various conditions, particularly highlighting the challenges and inconsistencies you might encounter with read committed. Through a series of demonstrations, I show how queries running under read committed can be blocked or return jumbled results due to changes in the underlying data, even for short durations. By contrast, RCSI provides a more consistent snapshot view but still doesn’t reflect intermediate changes within transactions. This video aims to help you understand the trade-offs and choose the most suitable isolation level based on your application’s needs, ensuring better performance and fewer surprises.

Full Transcript

Erik Darling here with Darling Data on an unexpectedly hot and sticky day here in New York. So unexpectedly hot and sticky that I think parts of my brain have just ceased functioning. It’s just shut down. Nap time. We will siesta. We will siesta you tomorrow. That was bad. That was real bad. I should have thought about that before I said it out loud. I have that problem, though. I do have that problem. So today’s video is going to be sort of, I’ve said in a number of videos about isolation levels. I like talking about them because exploring isolation levels is an important part of any SQL Server journey because eventually you will hit strange concurrency phenomenons that you can’t explain or reproduce or even find out. any real evidence of. And so I want to talk about what I’ve said in these videos is that almost no isolation level is universally perfect for every workload. And it’s more about making a choice about you making a choice about how what you want your application to behave under certain circumstances. So we’re going to compare and contrast a little bit between read committed to the pessimistic isolation level and read committed snapshot isolation level. I know that I recorded a video sort of about this, but I came up with this demo that I like because it highlights a lot more of the weird changes that can take place while a query under read committed is unable to make progress or even is just making slow progress for some reason. Like in the case that I’m using, there’s some blocking involved. And we’re going to compare and see what I’m using.

And in real life, you might have a query that’s just like has to read from a big table or like scan along or seek along a big index. And maybe that index isn’t in memory. And maybe you have to maybe it take you a couple seconds. And a lot of stuff can change in a couple seconds. The point of this isn’t like, like, you know, begin trend, do a few things and like look at all the stuff that changed in like 10 seconds. It’s like a lot of this stuff can change very quickly. Like within like a few hundred milliseconds. And your query would still return just jacked up looking results. So I’m going to hit execute here to reload this table. I’ve got a table called read committed stinks. You know, perhaps a little overkill in the table name. You know, I’m willing to give read committed a little bit of credit. I’m getting a haircut tomorrow. And this like this patch here is just annoying the crap out of me. But if I had the wherewithal, I would probably just shave my head at this point. Nothing but trouble. So this table has like some standard like account information stuff.

You can ID and account ID, how much money you have in the account, your first name, your last name, when the account was created and the last time you were active in your account. And I couldn’t think of any good people names. So I just stuck a bunch of brunch menu items in the table. The prices do not reflect my respect for, adoration of, or preferences for these brunch items in any way.

I just rattled them off the top of my head. So please don’t try to infer anything about my brunch habits based on this table. And what I want to do is just give you a quick view of what this table currently looks like.

It pretty much looks like what I did up there, except now it’s a nice Excel format. All right. So that’s all null. That’s all like right now. We got first names, last names, values, everything.

Everything that you could want in a table. Really. Except maybe throw a nice XML or JSON column in there that just concatenates all that stuff together so that you can pull that out with your application instead.

I don’t know. People do dumb things all the time. So I want to make sure that recommitted snapshot isolation is off for the first run through. And what I’m going to do is, well, I’m not going to do it quite yet.

I’m going to have to hold on to those horses of yours for a couple seconds. So over in this window, I have a couple queries. This first query is doing a select from our knee-jerk table name.

And the idea is to look for anything with an account value greater than or equal to 1,000. You could put, you know what’s kind of funny about currency in SQL Server, is you could put any currency symbol in front of that 1,000, and it would find anything over 1,000.

SQL Server does not do currency conversion for you. So if I made that pound sign or whatever the euro is, it would just look for 1,000. It wouldn’t be like, oh, well, the pound is worth an extra 50 cents or something.

We’re going to look for anything that’s over 950. No, it just looks for anything over. You can put any currency you want in there.

You could put, like, I don’t know, whatever they use in Zimbabwe. If Zimbabwe is still a country, I’m not sure. You could put, like, 1,000 Zimbabwean dollars on there, and it would just be like, yeah, it’s over 1,000.

No problem. SQL Server does not do currency exchange rates for you. So you’d have to write a CLR function to go do a web call and check exchange rates and bring that back and then do some local conversion.

I don’t want to give you any worse ideas, though. So this query goes and looks for anything with an account value over 1,000. And if we think about what, you know, I put into the table up here, everything is over 1,000, right, or 1,000 or greater, right, 1,000 through 10,000.

And I’ve got this little decoder column because I’m going to make changes to the table that are not going to be reflected or might be extra reflected in the final results.

And I have go 2 after this because I want the first go. So this is going to execute this one query twice. I want the first go to get blocked to show you what happens when a query gets blocked.

Even for, again, like a couple hundred milliseconds, all this stuff could change in. And then run the query again afterwards because what I want you to see is that read committed, this pessimistic isolation level is not a snapshot of your point-in-time snapshot of your data, and that running the same query twice in a row can get you very different results.

And then I just have a query down at the end that just gives a select of everything in the table, right? So let’s come back up here a little bit, and let’s switch tabs, and let’s go and begin a transaction.

And what we’re going to do is we’re going to update the account value for ID 7, and we’re going to leave that hanging for a second. Now, again, just as I start this one off, again, like this isn’t stuff that has to go on for a long time in order for you to get mangled results from read committed, the pessimistic isolation level, because all of these queries that I’m going to run to make changes to the table, like, for instance, this is going to update three rows to set account value to 999.

If you remember, our original query is looking for anything with an account value greater than or equal to 1,000. And so these rows would no longer qualify.

We’re going to mess with a couple primary key values. And I realize that primary keys don’t often change in a database, but you might be working with other data that has more volatile keys.

You might be working with data that does not, like, that does change fairly often, like the key of an index that does change fairly often, right? Certain relational values might change from time to time.

And so you might see weird things where rows get thrown around and just juggled all over results. Think about, like, the context of Stack Overflow, where you might get a hot network question, and your score might jump tremendously, very quickly.

Or you might get downvoted into oblivion very quickly if you give a bad answer or ask a bad question or something. All this stuff can change, rapid fire. And you could move around in results and queries that you, results you should have been and that you’re not, or query results that you shouldn’t be and that you are.

It’s terrible, right? So I’m going to delete some rows, and then I’m going to reinsert a couple rows. Now, what I’m doing here is, you know, again, kind of tricky. I’m just switching French toast and steak frites around, right?

So they’re going to both get values of 1,000, but I’m going to flip their primary key values and their user account values, I think. Yeah, that’s all flipping.

So, yeah, made a bunch of changes, right? And again, this doesn’t, I don’t know how long I’ve been talking about this for, 10, 20, 30 interminable, uncalculatable numbers of seconds. But now I’m going to commit all those changes.

So, bloop, you go and do something. And now this query is finished. So what we got in our results, this first chunk of results up here, and if this SSMS would be so kind as to let me drag things around, the first set of results, remember, we paused on ID 7, right?

ID 7 was where we paused, because that’s where we got blocked as we were setting the account value to 5,001. So we made a whole bunch of changes to this table that are either reflected because we moved things past 7 or not reflected because we made changes before the ID 7, right?

So we read up to ID 7, got stuck, and we made a bunch of changes all around ID 7 that made these results weird. So like ID 9 didn’t change. ID 11 used to be ID 6.

ID 6 got set to ID 11. ID 7 was where we were blocked. This row got deleted. This value, this value, and this value all got changed to 999.

This got deleted and reinserted with different values. This got deleted and reinserted with different values. And now we have some weird stuff in the table. So coming back over here, right? Remember the table definition where I don’t only have a primary key on ID, but I also have a unique constraint on account ID.

And if we come over and look at the initial set of results, right? We have stuff that’s kind of all over the place.

We have two account IDs, 1006. We have two scotch eggs in a row with different IDs, but the same account ID. We have two steak frites, right?

If we look here, where’s that other? Whoa, that wasn’t what I wanted to do. Yeah, we got stuff all over the place. Like here’s, zoom back in there. Come on, zoom it, work with me.

We got one steak frites here. We got another steak frites here. We got a bunch of rows with, you know, account values that changed, right? Like these got set down. This one got set down.

This is the first run. This is just the block query, right? So a bunch of stuff changed around that query that this read query was stuck. It did a bunch of reads and then went, oh, I don’t know. I don’t care if things change all around me.

All I know is that I’m stuck here, right? We’re just stuck on ID 7 and we made changes all around ID 7. And really the results should have looked like this. We should have only gotten six rows back and we should have gotten six rows back with these values, right?

Like this is the actual state of the table for accounts that have a value greater than or equal to 1,000 after all that stuff goes through, right? And this is just what the table looks like as a whole.

So you can see like this is all just janky. Like I think one of the things that like really sticks out is in the first result set, this last activity column, even though we made a bunch of changes and updated this for a bunch of rows, only two of the rows actually ended up with a last activity date, right?

Well, the rest of them are null. So this looks bad and weird, okay? Like it’s not great. Like if you think about like repeatable read or serializable, like sure you wouldn’t have necessarily those problems.

You would just have fun deadlocking problem because, you know, if you tried to like run an update in the transaction and then selected data with serializable and then you ran another update, it would just deadlock, right?

Just same with repeatable read. It’d be over with, through, kaput, finito. So let’s contrast that with, of course, RCSI. Now I’m not saying that the RCSI results are necessarily better because the RCSI results are, well, they’re more consistent.

I’ll give it that. But you’re still not getting back like the intermediate changes within all those things, right?

So if we, I think I have to rerun this because it’s going to tell me that, oh no, it didn’t work. Okay, good. All right. So let’s reload the table. And for some reason, that something weird opened up on my monitor.

So forget that. So let’s reload the table. And now we have the table set back to its original state. We have RCSI turned on. And now if we do this and we look at the table, we come over here and we run this, we’re just getting a snapshot of the table back before this change to 5001, right?

Because that first update that we run over here, we’re saying set account value equals 5001, where ID equals seven.

So we’re still getting back the last known good row for ID seven, right? This did not change to 5001. We did not have a last activity date. And the same will go for all the other stuff that happens within this transaction, right?

If we update these things and we come back over here and we run these queries again, nothing in this table has changed, right? Like everything is exactly the same as before things ended up being committed.

And if we run this and do the inserts and we run this, like we can still see that we’re not reflecting any changes, right? We’re not blocked, but we’re still not showing the changes from within this transaction yet.

If we commit this, of course, let’s just make sure that’s extra committed and we run this. Now we see all the changes, but that was a lot like when, like in the second query, sorry, the first demo, when this query got blocked up and like didn’t, like didn’t show a bunch of wrong stuff, like inconsistent state data from the table, like as things changed around it.

And then this one down here, which is, you know, essentially, like in the first query, this one was essentially like the right results. Like we just had to run the query again after the blocking resolved.

And this is sort of like the full state of the table. So yeah, basically, coming back to my original point, no isolation level is exactly perfect.

What you want to ask yourself when you’re designing applications and when you’re trying to choose things like isolation levels is what you would prefer. With read committed to pessimistic isolation level, if you, you know, remember that first set of queries that we did, we got blocked and our query returned bad results anyway.

So being blocked in SQL Server is not a pleasant user experience. It’s not fun for anybody. So our query got blocked and then still gave us crappy results at the end.

Under RCSI, like we didn’t get blocked. We didn’t see all of the reflected changes just yet. We didn’t get blocked though, right? The query results returned immediately. I’m not saying that users would be maybe satisfied with that because like, you know, they got back like that snapshot of the table where ID7 still hadn’t changed, right?

We did all that stuff around ID7 and we didn’t see any of those changes until the transaction committed. That might be right for you, but that might not be exactly what end users are expecting either.

So really, it’s just a matter of you asking yourself the question, do I want users to get, you know, like committed data back very quickly or do I want to risk users getting blocked and like getting like crappy data back after getting blocked for a long time?

For me, I would much, much rather prefer, I don’t think I need to rather prefer. I would much rather or I would much prefer, not much rather prefer, that’s absurd, I should smack myself, self-flagellate for using English so dumbly.

I would prefer that users get consistent data back until a transaction commits and then see the final result of everything that happened in that transaction rather than get blocked, get back some plum weird results and then have to rerun it again anyway.

So, you might not want that though. For some reason, you might hate your end users and you might want them to get blocked and you might want them to get janky results back and have to run the query again.

Maybe you charge them by the query, maybe you charge them by the CPU tick, maybe every month, at the beginning of every month, you record the number of CPU ticks and at the end of the month, you record the number of CPU ticks and whatever the difference is, you charge like a dollar a tick or something.

Right? This is a pretty good pricing. I think, wait, isn’t that a DTU? That’s interesting. Anyway, yeah, these are the application design questions that you should ask yourself.

Now, again, something that I think obviously bears repeating even though many of you, if you’ve been watching my videos, are already well aware of this. If we use no lock, everything would have been terrible.

Right? We would have seen not good stuff along the way. So, we’re not no locking. We’re not going to, we’re not even going to demo it because I don’t want people to say, oh, look, Eric did no, like, like, turn off the sound and just be like, oh, look, Eric did no lock in the demo and it didn’t get blocked and maybe that looks cool.

I don’t, I don’t, I don’t think that’s all right either. So, we’re going to not do that. What we’re going to do is take this, take this time to reflect upon the applications we’re developing and ask ourselves, what would we prefer users experience with our application?

You know, it might be okay for users to just hit refresh a few times and get eventually the right results, eventual consistency, or you might just want them to sit there and get blocked for a while and then get weird results back.

Start threatening to sue you or something. I don’t know. I don’t know how all that works. So, take this time, take this lovely, well, I mean, it’s Monday afternoon here.

I don’t know when you’re going to be watching this. So, take this, take this moment now, this moment, while you have it. This time is ticking and precious and fleeting and you’re never going to have this time back.

So, take this time to really think carefully about how you want your applications to function and how you want your end users to experience your applications. RCSI is usually a better choice for most applications and most application developers.

It takes away a lot of the headaches that come with having to troubleshoot blocking and deadlocking between readers and writers and it just generally gives you more consistent results without the concerns of no lock or read uncommitted.

So, yeah. Yeah. I think that’s about good there. Like I said, it’s hot and my brain is fried.

So, we’re going to end this one here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you all go turn RCSI on immediately. I kid, but I don’t kid.

If you liked this video, there’s a little thumbs up you lucky button I’m told on YouTube that you can say thank you with. Comments are another nice way to say thank you.

And if you would like to see more videos like this immediately, hot off the presses as soon as Beer Gut Magazine finishes cutting me my check and shooting me up with my adrenochrome, I can talk through this, and I record the video, then you can subscribe to the channel and experience me in almost real time.

I guarantee you I’m quite a real time experience. You should do that. You and me. Subscribey buddies.

I’m going to go open windows and take a cold shower. Maybe not in that order, but in some order. Eventually that’ll be consistent too.

Anyway, thank you for watching.

Going Further

Loops, Transactions, and Transaction Log Writes In SQL Server

Posted on May 8, 2024January 22, 2026 by Erik Darling

Loops, Transactions, and Transaction Log Writes In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into an interesting aspect of SQL Server transaction management by exploring how loops and transactions interact in a practical scenario. I demonstrate three different approaches for handling modifications within a loop—allowing implicit transactions to handle each operation individually, wrapping all operations in a single explicit transaction, and conditionally committing based on specific criteria. By using SP Pressure Detector to monitor the transaction log activity, I show that optimizing the transaction commit strategy can significantly reduce the load on the transaction log and speed up the overall process. Whether you’re dealing with cursors or other looping constructs, this video offers valuable insights into how to tune your SQL Server operations for better performance.

Full Transcript

Erik Darling here with Darling Data on my tenth take at starting this thing. Usually recording these videos in the morning is against my religion because I am not suitably or favorably dispositioned to be presentable for this sort of work before like 3pm. So this video is about how if sort of like loops in terms of the work that you can do, transactions and the transaction log in SQL Server and how you can use transactions in loops to be more favorable to the transaction log and speed those loops up considerably. So, well, I don’t often recommend looping code. Sometimes it is unavoidable and you’ve got to do it because it’s unavoidable and the two are synonymous. So what we’re going to do is look at three different options you have for different options you have for writing to or doing modifications in a loop. If this were select queries, we wouldn’t care because select queries don’t do anything to the transaction log and this would have no impact. It would just be about tuning the select queries in a loop, you know, tune your cursor queries or whatever. So, yeah, we have three options here and we’re going to look at the first one, which is allowing SQL Server to behave as it normally does and use implicit, well, I mean, I mean, like automatic transactions. It’s not implicit transactions. It’s automatic transactions. But implicit transactions are a completely different thing. But implicitly, each one of these things is a transaction, right? So this insert is a transaction. This update is a transaction. This delete is a transaction. And what we’re going to do is highlight this and go over to this window, this tab, this tabulature. And we’re going to use a newer addition to SP pressure detector that allows you to sample a server for a number of seconds. And look at what happened in that number of seconds. So I’m going to kick this off. And I’m going to run this. And if this, this demo lives up to prior executions, it should finish in about seven or eight seconds. Look at seven seconds. Look at that professional presenter, even at whatever time it is in the morning.

Nailed it, right? Not like I didn’t just run through this or anything. So looking at SP pressure detectors results, the stuff that I want to focus on is first up here. So this second line is going to be rights to the transaction log for our database. Looks a bit stranger now that I see it on the screen. But if we look over here for that second line, we wrote about 235 megs to the transaction log over 60,000 total rights. And that is backed up in, you know, mostly, like mostly, you know, correct numbers. If we look at perfmon counters as well, where, let’s frame this up a little bit more nicely.

If we look at log bytes flushed, there were 247 million total or about 24 million a second. And if we look at the log flushes, we’ll have about 60,000 total flushes or about 6,000 flushes per second. And that lines up pretty well with, so like the 247 million bytes is probably pretty close to 235 megs. And 60,000 log flushes is pretty close to 60,000 total rights to the transaction log. So that might be fine, right? You might be doing this at a time when it doesn’t matter if your loop runs for seven seconds. It just might not be a big deal.

That’s okay. But if you’re like me and you often need to tune processes like this, you might be looking at other ways to improve upon this. One way you can do that is by wrapping all three of the modifications into an explicit transaction. So we have up here, we have a begin train and down here we have a commit. So rather than having each transaction, each insert, update, delete, auto commit when they run, we’re going to make them commit as a group when each loop or when each thing finishes.

So let’s highlight this code. And let’s come over here and kick off SP pressure detector. And let’s come back over here and run, oh, not you, run this. And this should finish in about between two and four seconds. We got two seconds on that one. Things seem to be finishing on the low end when I run them here. And what we’re going to see is that we cut everything down to about a third of what it was before.

So coming up here and looking at the total megs written, that’s just about 80, which is just about 30% of 250, whatever it was before. And the total write count is about 20,000, which is about 30% of the 60,000 that it was before. Something like that.

20, 40, 30, 33 and a third. 20, 40, 60, 33, something. 33 point infinite threes. And if we look down in the perfmon stats section at the same counters that we looked at before, if you look at log flush bytes, it’s about 1.6 bajillion.

And really what we’re looking at over here is the total difference, which is about 83 million and about 8.3 million a second. So that’s all coming down by about 30%. And the same thing we’re going to see here for the log flushes a second, where that’s at about 20,000 total and about 2,000 per second.

So before this was 60,000 and 6,000. Before this was, you know, 240, whatever bajillion. So that’s one way of doing it.

One way that I’ve found of making this even better is by not making every single loop through a transaction that commits, but conditionally committing the transactions based on something. And in this case, excuse me, the something that I’m using, it looks like this.

So we have, at the very top, we have a begin transaction. And at the very bottom, we have a commit transaction. But in the middle, every time, get in there, every time the ID value is modulist by 1,000 and equal to 0, so basically every 1,000 loops through, we’re going to commit the transaction and begin a new transaction.

All right, so it’s very important that you do this, and it’s very important that you do this and this. All right, cool. So let’s get this highlighted.

It’s a little bit more verbosity to the code. Let’s start this running, and now let’s run this. And that didn’t take two seconds.

That took no seconds. That was very fast, right? Pretty good, I think, anyway, at least if you’re into that sort of thing. And if you look at what happened with regards to Perfmon, we wrote a total of 10 megs to the transaction log, over 180 writes.

That’s a little cut off over there. And if we look down at the Perfmon counters, and let’s frame this up a little bit more nicely, we have for log flushed bytes a second, we are down to 10 million there, or 1 million a second.

And if we look at the log flushes a second for our database, we are down to 180, or about 18 per second. So that lines up, the 180 there lines up with the 180 there, and the 10 megs there lines up with the total difference there.

So everything kind of agrees that SQL Server writes to the log more efficiently when you, you know, A, like, don’t use, like, the auto-commit transactions if you’re doing multiple modifications in a loop.

And if you do an explicit transaction that encompasses all of the modifications in the loop, you’ll do better. And then if you change your code a little bit so that you control exactly the sort of cadence of commits to the transaction log, you can do even better.

Again, this is kind of a rare thing, but I do see it often enough that I find this to be a useful tactic to speed things up. So if you’re ever looking at code that’s running in a loop, whether it’s a cursor or a while loop or any other sort of construct that might loop over things, and you’re like, there are just not enough hours remaining in my life for me to write this as a set-based solution, you might consider using one of these techniques, either wrapping all of the modifications into a single transaction or controlling the cadence of transaction commits and begins and stuff to speed that up.

So, an admittedly quick video this morning because I have stuff to do soon, and I got to do those things. So, thank you for watching.

I hope you learned something. I hope you enjoyed yourselves. If you like this video, even early in the morning, I am clearly a bleary-eyed individual, thumbs up is a good way to say thank you.

Leaving a comment that says thank you is even more verbosity. And, of course, if you like this sort of SQL Server performance tuning training content, you should subscribe to my channel so that you get notified every single time I post one of these whiz-bang things, and I promise you that most of them will not be in the morning.

I prefer to work in the dark or something. Anyway, thank you for watching. I need to leave.

Bye.