Blog

Announcing More Improvements to PasteThePlan.com

Last Updated April 11, 2026

Need to share an execution plan, like on forums or on Stack Overflow, and you wanna get their advice as quickly as possible? Paste the XML into PasteThePlan.com and you get a link that you can share to anyone with your plan’s details.

The user interface is now cleaner, with separate tabs for query plan, query text, AI suggestions, raw XML, and your recent plans:

We noticed that a lot of people were pasting in estimated execution plans, which frankly doesn’t get you very good advice from either humans or AI. We added a warning suggesting that you paste the actual plan instead.

Note the “Delete Plan” button at the top right – that’s new too. I’d been getting more and more emails from people who realized that pasting their query plans into a web site meant that their query plans were, uh, on a web site, and I got tired of manually deleting those behind the scenes.

On the AI Suggestions tab, there’s a new feedback form so that after you try the AI’s advice, you can tell me how it did. I get notifications about the feedback, and I review it on a regular basis to help tune the prompt I send to the AI.

Finally, there’s a new “Get a Second Opinion” button at the bottom. The default first opinion is a fast, cheap call to gpt-5.4-nano that should return within 30 seconds. The second opinion calls gpt-5.4, a slower model that produces more in-depth results, but takes longer to do it (up to 3 minutes.)

I hope this helps you get more help, faster, whether that help is from your fellow humans by sharing your query plan’s link to forums and friends, or whether it’s instant free AI advice.

SQLBits Bonus: Free Fundamentals + Mastering Bundle for My Training Day Attendees

Last Updated April 10, 2026

Brent Ozar

SQLBits

Good news! My training day workshop, Dev/Prod Demon Hunters, was sold out, but Bits just moved me into the auditorium with way more capacity and space for you to stretch out.

Plus, there’s power and tables at each seat. Feel free to bring your charger, gadget, and laptop. You don’t have to follow along, but it’ll be nice for note-taking and for asking questions online, since I’ll have Pollgab up for questions so you don’t have to raise your hand and yell.

So to celebrate, I’m giving the attendees my Fundamentals & Mastering Bundle free for a year! Here’s everything you wanna know to attend.

The conference is at the International Convention Center in Newport, Wales.

Weds, 22 April: training days & general sessions, including my all-day workshop, Dev-Prod Demon Hunters: Finding the Real Cause of Production Slowness – and attendees get a free Fundamentals & Mastering Bundle
Weds evening: welcome drinks in the exhibit hall, board games night, code club
Thursday, 23 April: training days & general sessions, including my panel discussion, 20 Years of the Cloud: What Changed, What Didn’t, What’s Next
Thursday night: Pub Quiz
Friday, 24 April: training days & general sessions, including my session, Watch Brent Tune a Query in SQL Server 2025
Friday Night: the legendary SQLBits fancy dress party – this year’s theme is “cartoons”, which I also take to include anime, manga, and animation, heh
Saturday, 25 April: free day of general sessions, including my new session, Pokemon Battle, Choose Your Index: You Can’t Have Them All

SQLBits also has ticket + hotel packages for a handful of nearby hotels – expand the “Accommodation” section on that page to see the prices and hotels.

Book now here. I’ll see you in a few days! Hit reply if you’ve got any questions about how the conference works or what to expect. I’ve been there many times, and I love it.

[Video] Office Hours: Microsoft Database Q&A

Last Updated March 28, 2026

Brent Ozar

Videos

Let’s go through a LOT of your top-voted questions from https://pollgab.com/room/brento on a VERY early Saturday morning.

Office Hours: Microsoft Database Q&A

Watch this video on YouTube

00:00 Start
01:52 DBAInAction: Hi Brent Microsoft just announced Automatic Index Compaction for Azure SQL and Fabric. do you see this finally killing off the traditional ‘index maintenance’ debate? Also, any gut feeling on whether this will eventually be backported to box versions of SQL Server? Thank you!
04:19 VladDBA: Hey Brent, meant to DM you but figured this fits office hours: I’m on 2-month garden leave and weighing whether to hunt for another job or launch my own consulting business. If you were in my shoes, what would you do?
06:28 Jacob H: What is everyone using for stress testing SQL Server in 2025? Looks like Microsoft has deprecated all it’s tools. (Distributed Replay and it’s replacement Replay Markup Language Utility)
09:01 EagerBeaver: hi Brent, when populating table from data from another table via INSERT INTO SELECT is there a way to OUTPUT columns that are not being inserted? I need to store mapping between old and new PK and the only way I found was to use MERGE which you don’t recommend. Thanks
10:30 Thomas Franz: Auto Create statistics is enabled on my DWH (billions of rows in partitioned Columnstore tables). When someone executes a query that uses on uncommon column first time in WHERE / JOIN it takes forever to create an execution plan. Is there a way to autocreate the stats async too?
11:56 Zale: Ola Brent, have you encountered any significant performance issues in SQL Server caused by the use of cursors?
12:37 SteveE: Hi Brent, Do you know of any resources for creating good AI prompts for query tuning? The people who appear to get the most out of AI appear to be good at writing prompts
14:57 MyTeaGotCold: Has SAN multipathing improved over the last decade? I remember when we had to be very careful with vendors not having true multipathing.
18:43 Jason Not JSON: Should we start designing schemas differently now that AI tools prefer semi-structured data like JSON?
21:11 Felipe: Hi Brent, Nowadays, do you think it’s still worth becoming or remaining a DBA specialist? Maybe this is just my impression, but I’ve been working as a DBA for over a decade (mostly with Oracle) and I’m starting to think about changing my career to another role.
23:48 .Net Dev: I’m on Azure SQL DB at compat level 140. I’ve heard you say that being “out of support” is a valid reason to upgrade. Since SQL 2017 is hitting EOL, does that logic apply to compat levels in Azure? Or is it fine to stay on 140 indefinitely if I have no performance issues?
24:36 Andrew G: I am SaaS support. I work with VMs and SQL server. Why do t-logs, if not properly maintained, affect memory / cause timeouts? Is this something to do with TempDB? If you could let me know if you have a course that covers, this currently working through fundamentals! TY !
27:59 Dopinder: We have many old sp’s that reference no longer existent columns but fortunately these sp’s are no longer used. Is there a good way to force a recompile for all sp’s so that we can identify these crusty / old sp’s that error on recompile so we can delete? SQL 2019
29:16 Matteo: Hi Brent, I just finished the ‘Faster, cheaper, cloud databases’ module. How much do you think the recent price increases for physical RAM and SSDs will impact the way we evaluate the cost-effectiveness of cloud VMs?
32:21 YouTubeFreeLoader: As a query tuner, how do you address or identify performance issues that might be a data issue in a system you aren’t familiar with. For example, a query suddenly performing worse but the root cause is a data issue like a job isn’t running that should be but you don’t know it.
34:50 How Did I Even Get Here: Just an update. I recently asked for ideas on choosing a pet project (don’t worry, during work hours). I set up a CMS to deploy/run sp_Blitz and its friends across 12 servers from my CMS and collect data in a table on the CMS. It’s AMAZING! Is this still rare, and if so, why?
38:19 Josef: How do you index for a WHERE condition with LIKE ‘%’ + @SearchText + ‘%’?
40:06 chris: Howdy, Brent! When working with a client to resolve a problem they’ve brought to you, how much time would you say you spend on documentation after you’ve landed on a solution?
43:35 DickBowen: Is using DBCC SHOW_STATISTICS to extract the histogram information into a temporary table for a lookup of RANGE_HI_KEY values a good idea?

Contest: Make the Comments Look Like My ChatGPT History.

Last Updated April 17, 2026

Brent Ozar

AI, Humor

Let’s have some fun.

Put yourself in the frame of mind of the fella who writes this blog.

What do you think I send to ChatGPT?

In the comments, write prompts that you think I’m sending to the giant robots. In one week (on April 15), I’ll go through ’em and pick my favorites. My top 3 favorites will get a Fundamentals & Mastering Bundle, and 5 honorable mentions will get the Fundamentals.

Update: the contest is over.

How Multi-Column Statistics Work

Last Updated April 7, 2026

Brent Ozar

Statistics

The short answer: in the real world, only the first column works. When SQL Server needs data about the second column, it builds its own stats on that column instead (assuming they don’t already exist), and uses those two statistics together – but they’re not really correlated.

For the longer answer, let’s take a large version of the Stack Overflow database, create a two-column index on the Users table, and then view the resulting statistics:

DropIndexes;
GO
CREATE INDEX Location_Reputation
    ON dbo.Users(Location, Reputation);
GO
DBCC SHOW_STATISTICS('dbo.Users', 'Location_Reputation');
GO

DropIndexes;

CREATE INDEX Location_Reputation

ON dbo.Users(Location, Reputation);

DBCC SHOW_STATISTICS('dbo.Users', 'Location_Reputation');

The output of DBCC SHOW_STATISTICS shows that we’ve got about 22 million rows in this table. So, what does the statistics histogram say about the relationships between locations and reputations?

In the first result set, you can see that Rows = 22,484,235, and Rows Sampled = 22,484,235 – the same number. That means our statistics had a full scan, which is as good as they can possibly get.

The density vectors aren’t very useful.

The second result set are the density vectors – aka, the averages – and it has 3 rows. The first one says Location: all density = 3.282714E-06. If you take that number, times 22,484,235, you get 73.8093. That’s the density vector: if SQL Server needs to estimate how many rows are going to match for a given location, and it doesn’t know what the location is, it’ll estimate 73.8093.

Here’s an example query to prove that. I’m using a local variable to prevent SQL Server from sniffing the value, so it’ll have no idea what the @LocationUnknown value will be at compilation time:

DECLARE @LocationUnknown 
    NVARCHAR(200) = N'Las Vegas, NV';

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = @LocationUnknown;

DECLARE @LocationUnknown

NVARCHAR(200) = N'Las Vegas, NV';

SELECT COUNT(*) FROM dbo.Users

WHERE Location = @LocationUnknown;

In the resulting query plan:

When you hover your mouse over the index seek and look at “Estimated Number of Rows Per Execution”, you get 73.8093:

Back to our DBCC SHOW_STATISTICS output. We explained that the first row was the density vector for Location alone – so what’s the second row, which says Location, Reputation?

What’s that “1.058257E-06” number mean in the Location, Reputation column? Multiply that by the number of rows in the table (22,484,235) and you get 23.794. I bet you can see where I’m going with this:

DECLARE @LocationUnknown NVARCHAR(200) = N'Las Vegas, NV',
        @ReputationUnknown INT = 1234;

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = @LocationUnknown
AND Reputation = @ReputationUnknown;

DECLARE @LocationUnknown NVARCHAR(200) = N'Las Vegas, NV',

@ReputationUnknown INT = 1234;

SELECT COUNT(*) FROM dbo.Users

WHERE Location = @LocationUnknown

AND Reputation = @ReputationUnknown;

Here’s our query plan, and bingo, 23.794 estimated rows:

If you’re searching for an unknown (or unpredictable before runtime) location and reputation combo, SQL Server uses the density vector in the histogram to calculate how many rows are going to match. SQL Server thinks that for any given location and reputation, no matter what values you pass in, they’re going to produce 23.7941.

At first, that sounds ridiculous: Reputation is an integer number. If you’re asking for equality searches on that number, and you’re passing in random numbers, there’s practically no way that estimate could be right. On “average”, maybe across thousands or millions of searches, this could be vaguely the average number – but it’s never going to be correct. It’s going to be wildly overestimated some times, and wildly underestimated at other times.

But what about the histogram?

The next result set in DBCC SHOW_STATISTICS is the histogram, which contains the detailed list of up to 201 location values – because Location is the first column in our statistic:

Let’s scroll down to the Vegas area:

Las Vegas isn’t big enough of an outlier to get its own bucket, so if we query for the people who live in Las Vegas:

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = N'Las Vegas, NV' AND 1 = (SELECT 1);

1 2	SELECT COUNT(*) FROM dbo.Users WHERE Location = N'Las Vegas, NV' AND 1 = (SELECT 1);

Note that I’m using 1 = (SELECT 1) in order to prevent autoparameterization, which is a totally different subject for another day. Hover our mouse over the execution plan to see the estimated number of rows:

The 7.04485 estimate comes from our statistics. Scroll back up a couple of images, and note that in our statistics, I highlighted the row for Lebanon. “Las Vegas, NV” is somewhere between “Lahore, Pakistan” and “Lebanon” (it certainly feels that way on the highway sometimes), so SQL Server uses the AVG_RANGE_ROWS number of 7.044848.

When SQL Server is searching for an unknown Location, it uses the density vector. When it’s searching for a known location in between two range high keys, it uses the AVG_RANGE_ROWS number. So far, so good.

But what happens if we pass in a search for a known location AND a known reputation?

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = N'Las Vegas, NV' 
AND Reputation = 1234
AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users

WHERE Location = N'Las Vegas, NV'

AND Reputation = 1234

AND 1 = (SELECT 1);

Before we look at the execution plan of that query, take a moment to review the statistics at play here. Which column is SQL Server going to use for its estimate?

Which column in the above screenshot tells SQL Server that any particular location has a higher or lower average reputation score, or how distributed the values are?

That’s right: there isn’t one!

This statistics histogram isn’t really about the second column of the object at all. It’s about the first column! Multi-column stats aren’t, really: they’re really just single-column stats!

Here’s the part that’s kinda mind-blowing. Here’s the query plan for Las Vegas and 1234:

Look familiar? That’s a 7.04485 estimate. Exactly the same as if we weren’t filtering on reputation at all. It’s using the avg_range_rows from our statistics, giving us the exact same estimate that we got from just filtering on Location = Las Vegas.

The histogram values for the first column in our object is really useful. Subsequent columns, not so much.

The Reputation value I’m searching for doesn’t really matter here either. Let’s try one of the biggest values, Reputation = 1. You’ll see why this is important later:

The estimate is still 7.04485: exactly the same as not filtering by reputation at all. That’s… not great.

Things change a little for really big outliers.

Let’s try searching for the very biggest location value: India. If we search for just the location value (not a reputation yet), the histogram is useful because India’s one of our outliers:

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = N'India' 
AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users

WHERE Location = N'India'

AND 1 = (SELECT 1);

The resulting execution plan estimates are absolutely bang on:

Then add in a filter for a given reputation number, and here I’m going to do both 1234 and 1 back to back:

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = N'India' 
AND Reputation = 1234
AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = N'India' 
AND Reputation = 1
AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users

WHERE Location = N'India'

AND Reputation = 1234

AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users

WHERE Location = N'India'

AND Reputation = 1

AND 1 = (SELECT 1);

And whaddya know: now our estimates are not 7.04485, unlike Las Vegas:

SQL Server manages to figure out that India is not only huge, but also that Reputation = 1 is huge. So, how’d it do that? Right-click on the SELECT operator of the second query, go into Properties, and then OptimizerStatsUsage:

SQL Server didn’t just use the stat on Location_Reputation. In order to understand that Reputation = 1 is an outlier, it also automatically created a statistic on the Reputation column separately because the Reputation data in the Location_Reputation statistic just wasn’t useful enough.

Multi-column stats just don’t help much by themselves.

To really prove it, let’s set up an artificial scenario. Let’s say that everyone in China has really high reputation. And, just to give SQL Server the best defenses possible, let’s create a multi-column index (and therefore stat) on Reputation, Location. Hell, let’s even update statistics on Users so that our existing Location, Reputation stat completely understands that China’s where the smart people are at:

UPDATE dbo.Users SET Reputation = 1000000
WHERE Location = N'China';

CREATE INDEX Reputation_Location
    ON dbo.Users(Reputation, Location);

UPDATE STATISTICS dbo.Users WITH FULLSCAN;

UPDATE dbo.Users SET Reputation = 1000000

WHERE Location = N'China';

CREATE INDEX Reputation_Location

ON dbo.Users(Reputation, Location);

UPDATE STATISTICS dbo.Users WITH FULLSCAN;

If that was a Venn diagram, we would now have a perfect circle: all of the people in China have exactly 1,000,000 reputation points, and the only people with exactly 1,000,000 reputation points are in China:

SELECT COUNT(*) FROM dbo.Users
WHERE Location = N'China';

SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000;

SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000
AND Location <> N'China';

SELECT COUNT(*) FROM dbo.Users

WHERE Location = N'China';

SELECT COUNT(*) FROM dbo.Users

WHERE Reputation = 1000000;

SELECT COUNT(*) FROM dbo.Users

WHERE Reputation = 1000000

AND Location <> N'China';

The results:

So now, let’s ask SQL Server:

How many people do you THINK live in China?
How many people do you THINK have 1,000,000 reputation points?
How many people do you THINK live in China, AND have 1,000,000 points?

SELECT COUNT(*) FROM dbo.Users
WHERE Location = N'China' AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000 AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000
AND Location = N'China' AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users

WHERE Location = N'China' AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users

WHERE Reputation = 1000000 AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users

WHERE Reputation = 1000000

AND Location = N'China' AND 1 = (SELECT 1);

Check out the estimated number of rows on the query plans:

Or for those of you who prefer memes:

If SQL Server had anything even remotely resembling true multi-column stats, the estimate would be closer than this. We don’t, so it’s not.

The documentation suggests that you should create these statistics manually when you know there’s correlation:

CREATE STATISTICS Stat_Location_Reputation
ON dbo.Users(Location, Reputation)
WITH FULLSCAN;

CREATE STATISTICS Stat_Reputation_Location
ON dbo.Users(Reputation, Location)
WITH FULLSCAN;

DBCC FREEPROCCACHE;
GO
SELECT COUNT(*) FROM dbo.Users
WHERE Location = N'China' AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000 AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000
AND Location = N'China' AND 1 = (SELECT 1);

CREATE STATISTICS Stat_Location_Reputation

ON dbo.Users(Location, Reputation)

WITH FULLSCAN;

CREATE STATISTICS Stat_Reputation_Location

ON dbo.Users(Reputation, Location)

WITH FULLSCAN;

DBCC FREEPROCCACHE;

SELECT COUNT(*) FROM dbo.Users

WHERE Location = N'China' AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users

WHERE Reputation = 1000000 AND 1 = (SELECT 1);

SELECT COUNT(*) FROM dbo.Users

WHERE Reputation = 1000000

AND Location = N'China' AND 1 = (SELECT 1);

But yeah no, that still doesn’t work, and still produces the same 89-row estimate.

To learn how to solve these kinds of problems, check out my Mastering Query Tuning class.

Who’s Hiring Database People? April 2026 Edition

Last Updated January 15, 2026

Brent Ozar

Who's Hiring

Is your company hiring for a database position as of April 2026? Do you wanna work with the kinds of people who read this blog? Let’s make a love connection.

If your company is hiring, leave a comment. The rules:

Your comment must include the job title, and either a link to the full job description, or the text of it.
An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
It has to be a data-related job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

Each month, I publish a new post in the Who’s Hiring category here so y’all can get the latest opportunities.

SQL Server 2025 CU4 Adds Automatic Updates

Last Updated April 2, 2026

Brent Ozar

Humor

April 1, 2026: Big news for everyone who has to manage Microsoft SQL Server, whether they’re DBAs, sysadmins, or developers.

Seven years ago, Microsoft announced automatic, downtime-free hot patching of the SQL Server engine in Azure SQL DB. The tail end of that post had a teaser in it:

Well, I guess enough of you emailed in, because 7 years later, starting with Cumulative Update 4, SQL Server finally has that capability! The original technical post covers the internals of how it works, but there are a few changes for the on-premises boxed-product version of SQL Server:

SQL Server’s CEIP service (the Customer Experience Improvement Program telemetry service that constantly connects back to Microsoft’s servers) gets the list of available updates for your build. It was already sending your build number to Microsoft anyway,
SQL Server downloads the most recent available update, staging it in the binaries directory
The updates are applied based on the new sp_configure setting for ‘automatic updates’

Obviously, for safety reasons, you’re not opted into automatic updates. You have to choose when you want the binaries to be applied with that new sp_configure setting:

/* Apply updates at the next service restart: */
EXECUTE sp_configure 'automatic updates', 'on restart';

/* Apply updates immediately only if no databases are primary: */
EXECUTE sp_configure 'automatic updates', 'on secondary';

/* Apply updates at the next service restart: */

EXECUTE sp_configure 'automatic updates', 'on restart';

/* Apply updates immediately only if no databases are primary: */

EXECUTE sp_configure 'automatic updates', 'on secondary';

There are more options, similar to SQL Server 2014’s options for online index rebuilds, and I love that they used the exact same syntax to keep things simple:

/* Wait in the background, and when no queries are running, 
apply updates - aka Azure SQL DB style: */
EXECUTE sp_configure 'automatic updates', 'wait at low priority';

/* Wait up to 30 minutes (or put in the integer of your choice),
and if queries are still running then, apply updates anyway: */
EXECUTE sp_configure 'automatic updates', 'max duration 30';

/* Wait in the background, and when no queries are running,

apply updates - aka Azure SQL DB style: */

EXECUTE sp_configure 'automatic updates', 'wait at low priority';

/* Wait up to 30 minutes (or put in the integer of your choice),

and if queries are still running then, apply updates anyway: */

EXECUTE sp_configure 'automatic updates', 'max duration 30';

Some of you out there are going to raise your wrinkled old hands to the sky and shake your fist angrily, cursing modern Microsoft. First, they came for your job with automatic index tuning in Azure in 2018, then brought that to the SQL Server boxed product, then automated patching in Azure SQL DB, and now they’re coming for your maintenance jobs on-premises too. That’s scary, I know, because it can feel like there’s less and less left for database administrators to do.

All we’ve got left to do with SQL Server is the initial installation, engine configuration, backups, high availability, disaster recovery, corruption checking, permissions setup, performance monitoring, query tuning, and outage troubleshooting. I don’t know how future DBAs will be able to stay busy, and they should definitely start thinking about better long term career options now that the database is self-managing and self-patching.

Note: this post was published on April 1, 2026. That date probably means something significant, given that I’ve shown it a few times on the page.

How to Draw Line Graphs in SSMS

Last Updated March 31, 2026

Brent Ozar

SQL Server Management Studio

Ever just need a quick visualization of some numbers to get a rough idea of the trend, like this:

Option 1: ask Copilot to write the query for you.

In SSMS v22.3 and newer, with the results query on your screen, open up SSMS Copilot and copy/paste this into the prompt:

Given the query in this window, graph the results as a line chart in the SSMS Spatial Results tab using these rules:

1. Wrap the original query in a CTE called SourceData

2. Create a ScaledData CTE that scales X values to 0–300 and Y values to 0–100 using window functions MIN/MAX OVER()

3. Build a LINESTRING geometry from the scaled points

4. Use STDifference to cut holes in the line where data points are, so tooltips don’t conflict

5. Use STBuffer(3) to make each data point a large circle

6. UNION ALL the line (with holes) and the circles, with the line row having NULL for all non-geometry columns

7. The first column in the SELECT should be the X-axis label, the second column should be the Y-axis measure, and the third column should be the geometry — so tooltips show meaningful data when hovering over circles

Use a large model (like Claude Opus 4.6 or newer) for the best results. It’ll write the query for you, and if you’ve been living your life right, you’ll get something like this:

-- Created by GitHub Copilot in SSMS - review carefully before executing
WITH YearlyData AS (
    SELECT 
        YEAR(CreationDate) AS Yr, 
        SUM(1) AS UsersCreated
    FROM dbo.Users
    GROUP BY YEAR(CreationDate)
),
ScaledData AS (
    SELECT 
        Yr,
        UsersCreated,
        -- Scale X: years to 0-300 range (wide)
        (Yr - MIN(Yr) OVER ()) * 300.0 / NULLIF(MAX(Yr) OVER () - MIN(Yr) OVER (), 0) AS X,
        -- Scale Y: users to 0-100 range (short)
        UsersCreated * 100.0 / NULLIF(MAX(UsersCreated) OVER (), 0) AS Y
    FROM YearlyData
),
LineGeom AS (
    SELECT geometry::STGeomFromText(
        'LINESTRING(' + 
        STRING_AGG(CAST(X AS VARCHAR(20)) + ' ' + CAST(Y AS VARCHAR(20)), ', ') 
        WITHIN GROUP (ORDER BY Yr) + 
        ')', 0) AS Geom
    FROM ScaledData
),
AllCircles AS (
    SELECT geometry::UnionAggregate(geometry::Point(X, Y, 0).STBuffer(3)) AS Geom
    FROM ScaledData
)
-- Line with holes cut out where circles are
SELECT 
    NULL AS Yr,
    NULL AS UsersCreated,
    lg.Geom.STDifference(ac.Geom) AS Geometry
FROM LineGeom lg
CROSS JOIN AllCircles ac

UNION ALL

-- Circles on top
SELECT 
    Yr,
    UsersCreated,
    geometry::Point(X, Y, 0).STBuffer(3) AS Geometry
FROM ScaledData;

-- Created by GitHub Copilot in SSMS - review carefully before executing

WITH YearlyData AS (

SELECT

YEAR(CreationDate) AS Yr,

SUM(1) AS UsersCreated

FROM dbo.Users

GROUP BY YEAR(CreationDate)

ScaledData AS (

SELECT

Yr,

UsersCreated,

-- Scale X: years to 0-300 range (wide)

(Yr - MIN(Yr) OVER ()) * 300.0 / NULLIF(MAX(Yr) OVER () - MIN(Yr) OVER (), 0) AS X,

-- Scale Y: users to 0-100 range (short)

UsersCreated * 100.0 / NULLIF(MAX(UsersCreated) OVER (), 0) AS Y

FROM YearlyData

LineGeom AS (

SELECT geometry::STGeomFromText(

'LINESTRING(' +

STRING_AGG(CAST(X AS VARCHAR(20)) + ' ' + CAST(Y AS VARCHAR(20)), ', ')

WITHIN GROUP (ORDER BY Yr) +

')', 0) AS Geom

FROM ScaledData

AllCircles AS (

SELECT geometry::UnionAggregate(geometry::Point(X, Y, 0).STBuffer(3)) AS Geom

FROM ScaledData

)

-- Line with holes cut out where circles are

SELECT

NULL AS Yr,

NULL AS UsersCreated,

lg.Geom.STDifference(ac.Geom) AS Geometry

FROM LineGeom lg

CROSS JOIN AllCircles ac

UNION ALL

-- Circles on top

SELECT

Yr,

UsersCreated,

geometry::Point(X, Y, 0).STBuffer(3) AS Geometry

FROM ScaledData;

Voila! Graph. Un-check the “Show grid lines” box on the SSMS results tab because the X/Y axis won’t make sense.

Option 2: use this user-defined function.

I asked Copilot to bundle this into a reusable piece of code so that I could use it for any query. It sets up a user-defined table type, then a function that accepts that user-defined table type as an input parameter:

-- Created by GitHub Copilot in SSMS - review carefully before executing

-- Step 1: Create user-defined table type for input data
CREATE TYPE dbo.GraphInput AS TABLE
(
    SortOrder FLOAT,       -- Controls point ordering on X-axis
    Label     NVARCHAR(100), -- Displayed in tooltip
    Measure   FLOAT          -- Y-axis value displayed in tooltip
);
GO

-- Step 2: Create the graphing function
CREATE FUNCTION dbo.fn_GraphSpatial
(
    @Data dbo.GraphInput READONLY
)
RETURNS @Result TABLE
(
    Label    NVARCHAR(100),
    Measure  FLOAT,
    Geometry geometry
)
AS
BEGIN
    DECLARE @XRange      FLOAT = 300.0;
    DECLARE @YRange      FLOAT = 100.0;
    DECLARE @CircleRadius FLOAT = 3.0;

    DECLARE @MinSort    FLOAT;
    DECLARE @SortSpan   FLOAT;
    DECLARE @MaxMeasure FLOAT;
    DECLARE @PointCount INT;

    SELECT
        @MinSort    = MIN(SortOrder),
        @SortSpan   = NULLIF(MAX(SortOrder) - MIN(SortOrder), 0),
        @MaxMeasure = NULLIF(MAX(Measure), 0),
        @PointCount = COUNT(*)
    FROM @Data;

    -- Pre-compute scaled coordinates
    DECLARE @Scaled TABLE
    (
        Label     NVARCHAR(100),
        Measure   FLOAT,
        X         FLOAT,
        Y         FLOAT,
        SortOrder FLOAT
    );

    INSERT INTO @Scaled (Label, Measure, X, Y, SortOrder)
    SELECT
        Label,
        Measure,
        CASE WHEN @SortSpan IS NULL THEN @XRange / 2.0
             ELSE (SortOrder - @MinSort) * @XRange / @SortSpan END,
        CASE WHEN @MaxMeasure IS NULL THEN 0
             ELSE Measure * @YRange / @MaxMeasure END,
        SortOrder
    FROM @Data;

    -- Insert data point circles
    INSERT INTO @Result (Label, Measure, Geometry)
    SELECT Label, Measure, geometry::Point(X, Y, 0).STBuffer(@CircleRadius)
    FROM @Scaled;

    -- Build the connecting line (2+ points required)
    IF @PointCount >= 2
    BEGIN
        DECLARE @LineWKT NVARCHAR(MAX);
        SELECT @LineWKT = 'LINESTRING(' +
            STRING_AGG(
                CAST(X AS VARCHAR(20)) + ' ' + CAST(Y AS VARCHAR(20)), ', '
            ) WITHIN GROUP (ORDER BY SortOrder) +
            ')'
        FROM @Scaled;

        DECLARE @Line geometry = geometry::STGeomFromText(@LineWKT, 0);

        -- Cut holes where circles are so tooltips don't conflict
        DECLARE @AllCircles geometry;
        SELECT @AllCircles = geometry::UnionAggregate(
            geometry::Point(X, Y, 0).STBuffer(@CircleRadius)
        )
        FROM @Scaled;

        INSERT INTO @Result (Label, Measure, Geometry)
        SELECT NULL, NULL, @Line.STDifference(@AllCircles);
    END

    RETURN;
END
GO


-- Usage example
DECLARE @data dbo.GraphInput;

INSERT INTO @data (SortOrder, Label, Measure)
SELECT
    YEAR(CreationDate),
    CAST(YEAR(CreationDate) AS NVARCHAR(100)),
    COUNT(*)
FROM dbo.Users
GROUP BY YEAR(CreationDate);

SELECT * FROM dbo.fn_GraphSpatial(@data);

100

101

102

103

104

105

106

107

-- Created by GitHub Copilot in SSMS - review carefully before executing

-- Step 1: Create user-defined table type for input data

CREATE TYPE dbo.GraphInput AS TABLE

(

SortOrder FLOAT, -- Controls point ordering on X-axis

Label NVARCHAR(100), -- Displayed in tooltip

Measure FLOAT -- Y-axis value displayed in tooltip

);

-- Step 2: Create the graphing function

CREATE FUNCTION dbo.fn_GraphSpatial

(

@Data dbo.GraphInput READONLY

)

RETURNS @Result TABLE

(

Label NVARCHAR(100),

Measure FLOAT,

Geometry geometry

)

BEGIN

DECLARE @XRange FLOAT = 300.0;

DECLARE @YRange FLOAT = 100.0;

DECLARE @CircleRadius FLOAT = 3.0;

DECLARE @MinSort FLOAT;

DECLARE @SortSpan FLOAT;

DECLARE @MaxMeasure FLOAT;

DECLARE @PointCount INT;

SELECT

@MinSort = MIN(SortOrder),

@SortSpan = NULLIF(MAX(SortOrder) - MIN(SortOrder), 0),

@MaxMeasure = NULLIF(MAX(Measure), 0),

@PointCount = COUNT(*)

FROM @Data;

-- Pre-compute scaled coordinates

DECLARE @Scaled TABLE

(

Label NVARCHAR(100),

Measure FLOAT,

X FLOAT,

Y FLOAT,

SortOrder FLOAT

);

INSERT INTO @Scaled (Label, Measure, X, Y, SortOrder)

SELECT

Label,

Measure,

CASE WHEN @SortSpan IS NULL THEN @XRange / 2.0

ELSE (SortOrder - @MinSort) * @XRange / @SortSpan END,

CASE WHEN @MaxMeasure IS NULL THEN 0

ELSE Measure * @YRange / @MaxMeasure END,

SortOrder

FROM @Data;

-- Insert data point circles

INSERT INTO @Result (Label, Measure, Geometry)

SELECT Label, Measure, geometry::Point(X, Y, 0).STBuffer(@CircleRadius)

FROM @Scaled;

-- Build the connecting line (2+ points required)

IF @PointCount >= 2

BEGIN

DECLARE @LineWKT NVARCHAR(MAX);

SELECT @LineWKT = 'LINESTRING(' +

STRING_AGG(

CAST(X AS VARCHAR(20)) + ' ' + CAST(Y AS VARCHAR(20)), ', '

) WITHIN GROUP (ORDER BY SortOrder) +

')'

FROM @Scaled;

DECLARE @Line geometry = geometry::STGeomFromText(@LineWKT, 0);

-- Cut holes where circles are so tooltips don't conflict

DECLARE @AllCircles geometry;

SELECT @AllCircles = geometry::UnionAggregate(

geometry::Point(X, Y, 0).STBuffer(@CircleRadius)

)

FROM @Scaled;

INSERT INTO @Result (Label, Measure, Geometry)

SELECT NULL, NULL, @Line.STDifference(@AllCircles);

END

RETURN;

END

-- Usage example

DECLARE @data dbo.GraphInput;

INSERT INTO @data (SortOrder, Label, Measure)

SELECT

YEAR(CreationDate),

CAST(YEAR(CreationDate) AS NVARCHAR(100)),

COUNT(*)

FROM dbo.Users

GROUP BY YEAR(CreationDate);

SELECT * FROM dbo.fn_GraphSpatial(@data);

To use it, insert your data into the user-defined table type. In this example below, I’m loading in the top 10 biggest locations in the Stack Overflow Users table:

DECLARE @data dbo.GraphInput;

WITH TopLocations AS (
    SELECT TOP 10 Location, COUNT(*) AS People
    FROM dbo.Users
    WHERE Location <> ''
    GROUP BY Location
    ORDER BY COUNT(*) DESC
)
INSERT INTO @data (SortOrder, Label, Measure)
SELECT ROW_NUMBER() OVER (ORDER BY Location) AS RankNumber,
    Location, People
FROM TopLocations
ORDER BY Location;

SELECT * FROM dbo.fn_GraphSpatial(@data);

DECLARE @data dbo.GraphInput;

WITH TopLocations AS (

SELECT TOP 10 Location, COUNT(*) AS People

FROM dbo.Users

WHERE Location <> ''

GROUP BY Location

ORDER BY COUNT(*) DESC

)

INSERT INTO @data (SortOrder, Label, Measure)

SELECT ROW_NUMBER() OVER (ORDER BY Location) AS RankNumber,

Location, People

FROM TopLocations

ORDER BY Location;

SELECT * FROM dbo.fn_GraphSpatial(@data);

And voila:

Line charts don’t make as much sense for that particular set of data – you’d probably want bar charts or column charts instead. I’ll leave that to you and your robot friend to work on.

If you wanna watch me work through this with Copilot to come up with the queries, here’s a video of me working on it. Enjoy!

Using SSMS Copilot to Draw Graphs

Watch this video on YouTube

And no, this isn’t my April Fool’s post, hahaha. This does actually work – although I obviously wouldn’t recommend it for any end-user-facing stuff. It’s just fun to have a nice, quick, easy visualization that you can copy/paste into emails or presentations.

I’m Coming to the PASS Summit in Frankfurt!

Last Updated March 27, 2026

Brent Ozar

#SQLPass

The pre-conference lineup for the PASS Data Community Summit Frankfurt event was just released, and I’m proud to share that I’ll be teaching my new all-day pre-conference workshop.

Dev-Prod Demon Hunters:
Finding the Real Cause of Production Slowness

Production is slow. Development is fast. The same query runs in both. Somewhere between the two, a performance demon is hiding—and this session is about hunting it down.

Your queries drive me to drink. — My own demons

Inspired by Brent Ozar’s love of the K-Pop Demon Hunters theme song, this class is delivered almost entirely as live demos, not slides. Brent Ozar will run real queries against two environments labeled “dev” and “prod,” then work through them exactly the way an experienced DBA would in the real world: comparing server settings, analyzing execution plans, and uncovering the subtle differences that led SQL Server to make different decisions. Each “hunt” reveals another demon—statistics, configuration, data distribution, or plan choice—and shows how easily a test environment can lie.

Along the way, Brent will demonstrate practical techniques you can use immediately: running sp_Blitz to surface meaningful environment differences, comparing execution plans to understand why SQL Server behaved differently, and making targeted changes to development so it better reflects production reality. By the end, you’ll understand how to stop guessing, stop blaming the engine, and follow the clues that lead to the truth—because when dev and prod finally move in sync, that’s when performance goes golden.

3 things you’ll get out of this session

Discover what caused query plans to vary from production
Learn how to quickly assess environment differences that would cause query plan changes
Understand how to change dev to more closely match prod

Pre-requisites: You should already be comfortable writing queries, reading execution plans, and using the First Responder Kit to gather data about your server’s wait stats and health.

Registration is open now with early bird pricing expiring March 31 (quickly!), and attendees will get a free year of my Recorded Class Season Pass: Fundamentals. See you in Frankfurt!

[Video] Office Hours: Q&A on the Mountaintop

Last Updated March 15, 2026

Brent Ozar

Videos

Well, maybe mountain is a bit of a strong word, but it’s one of the highest elevation home sites in Las Vegas, with beautiful views over the valley, the Strip, the airport, and the surrounding mountains. Let’s go through your top-voted questions from https://pollgab.com/room/brento while taking in the view – and you can move the camera around, since this is an Insta360 video.

I had to cut the first ~60 seconds of the intro, so it seems to start in an odd place:

Office Hours: Database Q&A on the Mountaintop

Watch this video on YouTube

Here’s what we covered:

00:00 Start
00:33 Sun Ra: Who is your favorite musician?
01:27 Dopinder: What is your opinion of the query hint tool in SSMS22? Who is the target audience? Will end users misuse it?
02:30 Trushit: I need medallion architecture for BI initiative. What would be your thought process? Largest table today is about 3M rows.
03:42 I Graduated MIT: In the MIT class, you say developers just need clustered indexes on each table (and FK) as a starting point, and DBAs take over later once it’s in production. Does AI change that? Should devs use AI to predict indexes earlier?
05:15 Bandhu: SQL log shipping performance can be bad over cloud provider SMB storage options. What’s your opinion of swapping out the default log shipping transport (SMB file copy) to using something more network efficient (Blob storage for Azure or S3 for Amazon)?
06:54 racheld933: Do you have a specific industry/sector of clients that you prefer over others? Like healthcare, finance, education, etc. Are there any specific challenges or trends that show up within each group?
08:21 RoJo: Can you comment on the use of BitLocker on a SQL server host? I’m concerned on speed, and another layer to add when patching or updating. Is it really needed if the Host is physically locked in a rack and room.
10:09 Alice: AI sent me down a powershell rabbit hole of try this, oh wait, sorry try that only to tell me many attempts later that what I was asking for is no longer supported. What is the worst AI rabbit hole you’ve experienced?
11:30 Mister Robot: At what point does “AI-assisted DBA” turn into “why do we still have DBAs?”
12:58 NicTheDataGuy: Hi Brent, you made a comment in a previous post that ” I actually put serious thought into deciding which tool I was going to learn next because I’d have so much free time”, curious what you would have done and why? is there a tool/language you think is going to be in demand?

Y’all Are Getting Good Free AI Advice from PasteThePlan.

Last Updated March 24, 2026

Brent Ozar

AI, PasteThePlan.com

PasteThePlan has a new AI Suggestions tab, and it’s been really fun to watch the query plans come in and the advice go out. Here are some examples:

Date “tables” – when I looked at the query, I glossed over the real problem. I thought arc_Calendar was a real date table, but AI figured out that it’s actually generated on the fly with spt_values, something that blows my mind at what an incredibly bad idea that is. It never crossed my mind that someone would even consider doing this in production, and AI tells them what to do instead.
This is fine – someone asked for advice on a select from a 1-row temp table. AI said get outta here with that nonsense.
One-table delete – but due to foreign keys, it’s actually touching multiple tables. The AI advice explains what’s going on and how to make it go faster, plus boils it down to “if you only do one thing” – I love that! I love prioritized advice.
Reporting process – a kitchen-sink style query that at first glance should probably be rewritten to dynamic SQL, but they’ve already solved that to some extent by slapping an option recompile hint on it. The AI advice catches a scalar UDF causing problems, suggests a rewrite using a technique the company’s already using on another column, and suggests a slight rewrite to push filtering earlier. Again, I love its recap here, all for free, in less than 30 seconds.
Lengthy paged dynamic SQL – ugh, even just glancing at this makes me look at the clock to think about how long it would take me to analyze. In seconds, AI reasoned over this monster to suggest indexes that would help the paging, plus raises some eyebrows at a weird not-exists-not-exists design.
Meaningful function rewrites – advice to quickly and easily change non-sargable functions into index seeks, and remove a correlated subquery.

Advice I’m not as wild about, and I’m thinking about how to tune the AI prompt in order to improve it:

16,000 row export with no WHERE clause – my first reaction here is to say, “Whoa now, we don’t tune for 16,000-row XML export queries.” However, when it comes to tuning the prompt to deliver better advice, I think we need to push the user harder to copy/paste in actual execution plans that include timing information, and then evaluate whether we should bother making changes, or whether the query’s performing good enough based on the wild output we’re asking for. This Power BI query is a similar example – whenever I see scientific notation for the estimated number of rows, I wanna stop tuning there and go get the actual plan.
Covering indexes galore – this is just one example, but my first iteration on my AI prompt didn’t discourage covering indexes, and AI seems to really wanna suggest ’em. I need to refine the prompt to suggest starting indexes with just the keys, and then only add includes for covering if the query’s still not fast enough after the initial indexes. There also seems to be a hesitance to recommend clustered indexes on heaps, even reporting heaps that were just created for the purpose of the query we’re looking at.
Lose the cursor – I’m surprised at how often I’ve seen folks paste cursor plans in. I do love the advice – it’s always “hey lose the cursor” – but I think we could do better. I’d like to be able to proactively rewrite stuff for folks, but realistically, a web page isn’t the best UI for that, and it will probably need a better, slower model with a >30 second timeout. We need to tell people, “Here’s a link to a set of commands that will rewrite the query for you” – whether that’s Claude Code, ChatGPT Codex, the plain chat interface for those tools, or something else.

And some queries & advice are just making me think. For example, in this multi-step reporting query, the AI seemed to find a lot of advice, but to understand if the advice is any good, I kinda want a followup status report from the user. Did they implement any of these fixes? Which ones? What kinds of differences did they see? We probably need a feedback loop of some kind to help iterate over the AI prompt.

I’m not delusional enough to think PasteThePlan.com is the right long-term solution for getting plan advice! We only have about 50 people using it each weekday. However, if that helps 50 people per day avoid posting questions to forums, and get instant answers that solve their problems for free and make users happier, then I’m very happy with that result. I couldn’t possibly answer 50 peoples’ query questions per day for free in my spare time!

My Wish List for SQL Server Performance Features

Last Updated March 12, 2026

Brent Ozar

SQL Server vNext

There are a lot of shopping days left before Christmas, and even more before the next version of SQL Server ships, so might as well get my wish list over to Santa now so the elves can start ~~working on~~ ignoring them.

My work focuses on performance tuning, so that’s what my wish list focuses on, too. They really are wishes, like I-want-a-pony, because I know I’m discussing some stuff that’s easy to describe, but really challenging to implement.

Forced Parameterization v2: The original implementation of this feature helped mitigate the multiple-plans-for-one-query issue, but it has a ton of gotchas, like not fully parameterizing any partially parameterized queries, or the select list of select statements. I still hit a lot of clients with those problems in their queries, making monitoring tools, Query Store, and the plan cache way less useful, and I’d love to see forced parameterization updated to fix these additional issues. If you search the the SQL feedback site for forced parameterization, there are a few dozen issues that reference it in various ways, including plan guide challenges.

Forced Parameterization v3, Handling IN Lists: When you use Contains() in a LINQ query, it gets translated into a T-SQL IN clause. This list can have different numbers of values depending on how many things you’re looking for – maybe you’re looking for just 1 value, or 10, or 100. Unfortunately, even when forced parameterization catches these, it still builds different plans for different quantities of values – like 1 value, 10 values, and 100 values all produce different plans in the cache – again, making monitoring tools, Query Store, and the plan cache way less useful. I would love the ability to just build one plan for an IN list, regardless of the number of values.

Better Approach to Query Hash: The whole reason I’m focusing on Forced Parameterization here is that SQL Server compiles different execution plans for each submitted query string. Even differences in spacing and casing cause different plans to be cached in memory – again, as discussed in this multiple-plans-one-query post. What if the optimizer was just smarter about recognizing that these two queries are really the same thing, and only caching one plan instead of building two separate plans?

SELECT * FROM StackOverflow.dbo.Users WHERE DisplayName = 'Brent Ozar';
GO
SELECT * FROM StackOverflow.dbo.Users WHERE DisplayName = 'Lady Gaga';
GO

SELECT * FROM StackOverflow.dbo.Users WHERE DisplayName = 'Brent Ozar';

SELECT * FROM StackOverflow.dbo.Users WHERE DisplayName = 'Lady Gaga';

Cost Threshold for Recompile: If a query plan’s cost is higher than X, recompile it every time rather than caching the plan. If the query cost is 5000 Query Bucks, it’s worth the extra 1-10 seconds to compile the dang thing again before we run a giant data warehouse report with the wrong parameter plan. It’s basically like adding a Query Store hint for OPTION (RECOMPILE), but doing it automatically on expensive queries rather than trying to play whack-a-mole. Here’s my feedback request for it.

We do Christmas decorations a little differently around here

Execution Threshold for Recompile: If a query has run 100 times, asynchronously compile a new plan for it, but this time around, take a lot more time to build the plan. Don’t do an early termination of statement optimization to save a few milliseconds – we’re serious about running this query. Use the execution metrics that have piled up over those 100 executions to think about whether this is really small data or big data. This is challenging to do, I know, because it means SQL Server would need a task list (like plans to be recompiled), plus an asynchronous way of processing those tasks, plus a way to know when it’s safe to run those tasks without impacting end user activity. It would also require a lot of new monitoring to know if we’re falling behind on that task list, and ways to identify which plans were re-thought, and persistence of those “better” plans in places like Query Store. Here’s my feedback request for it.

Configurable Statistics Size: SQL Server’s stats histogram size has been frozen at a max of 201 buckets since the dawn of time. In the age of big data, that’s nowhere near enough for accurate estimates, as explained in this Query Exercise challenge post and the subsequent answer and discussion posts. I wish SQL Server would switch to a larger statistics object by default, perhaps jumping to an extent per stat for large objects rather than a single page, or a configurable stats object size. This would be painful to develop, for sure – not only would it affect building the stats, but it would also impact everything that reads those stats, like PSPO which needs to detect stats outliers. Here’s the feedback request for it.

Update Statistics Faster: In SQL Server 2016, Microsoft added parallelism during stats updates, and then promptly ripped it back out in 2017, as the documentation explains in a note in the sampling section. Forget parallelism on a single stat: I want SQL Server to do a single parallel pass on a table, updating all of the statistics on that table at the same time, instead of doing separate passes of the table for each and every statistic on it. Think merry-go-round scans meets stats updates. Here’s the feedback request for it.

DDL Change Logging: When any object is modified – table, index, stored proc, database setting, server-level setting, login – have an API hook or call of some kind. Send notifications to an API to log that action: who did it, what object was changed, and what the change was. This would make true source control integration and alerting easier, not just for the database, but for the server itself, getting us closer to the point of being able to rebuild existing servers from some kind of source of truth. Yes, I know the “right” thing to do is make people check their own changes into some kind of source control system first, and then use that source control to build the server and the database, but in reality, that poses both organizational AND technical problems that most organizations can’t fix. We need a change logging system so we can at least be reactive. There have been multiple feedback requests for this over time and they’ve all gotten archived, but ever the optimist, here’s my new feedback request for it.

That last one isn’t technically a performance feature, but… if people can make changes in a production system without easy alerting, logging, and source control, then it’s a performance issue in one way or another, because people gonna break stuff. Users gonna use.

SSMS v22.4.1: Copilot is GA. So What’s It Do Right Now?

Last Updated March 21, 2026

Brent Ozar

AI, SQL Server Management Studio

Copilot in SSMS has two parts. Usually people focus on the pop-out Copilot chat window, and that’s useful for sure, but honestly I think you’re going to get way more mileage out of the code completions feature, right away, because it blends in with your existing workflows.

Let’s say that I’m working with the Stack Overflow database and I wanna find the top 10 users with the highest reputations, and for each one, find their top-scoring Post. I would start by typing a comment describing what I’m doing, then type SELECT, and the magic happens:

Copilot’s code completions automatically fill out pretty much what I’m looking for! That’s awesome. Note that I did have to type the word SELECT, but, uh, I’m okay with that. Copilot code completions don’t kick in until you at least give it a character. Hey, I’ve got plenty of characters around here. Let’s hit Tab to accept its work, and then hit enter:

We’re at an impasse until I type the word FROM, which is fine, let’s do that:

And it figures out that I want the Users table first. It even suggests aliasing the table correctly so that it matches up with what it’s already got in the SELECT! Lovely. Note that it only wrote one line – just the Users table – and not the subsequent join. Hit Tab to accept it, then enter:

Again, nothing happens until I type something else in, so we’ll prompt it with an inner join:

It adds the bang-on correct join to the Posts table, even though there’s no foreign key to explain the join, AND it lays out the WHERE clause. Note that earlier it only added one line for the From – just the Users table – but here it adds both the Posts table, and suggests no more joins are necessary, and it’s time for the WHERE clause, and adds it.

It does filter for only questions, which is something I didn’t ask for. Hmm. I would imagine that this filter and the joins are influenced by the fact that the Stack Overflow database is open source, and there’s a lot of ~~copyrighted blog posts~~ AI training material out there that Copilot learned from, so it’s automatically adding that. Your own surely private database might not get that quality of recommendations (although here of course the quality is what we call “bad”, since I didn’t ask for that filter, but “bad” is still a quality.)

Hit tab to accept, and it just sits there until we start the ORDER, at which point it fires back up with more advice:

The order is pretty good, but overall the query doesn’t produce the results we’re actually looking for, as we’ll see when we hit F5:

That’s … not what we wanted. We specifically asked for the top 10 users, and for each one, get their highest-scoring post. That’s not what we’re seeing here.

Let’s switch over to the Copilot Chat window and ask the same question:

Note that I didn’t even ask Copilot Chat to evaluate the query in the SSMS window! It just decided to do that, and gauged the code completion chat as lacking in brains. That’s fantastic! In fairness, Copilot Chat takes a hell of a lot more time to figure that out, and as its analysis continues…

It shows the results, and those results are bang on. I love how it adds the top post title, too, which our code completions query didn’t. Continue scrolling down and after the results:

Brilliant! It … well, it doesn’t show me the actual query it wrote, but it does follow exactly what I asked for. I technically didn’t ask it to write the query – I just asked it for the results, so I’m left with copy/pasting the results out of the text, or asking it to show me its query.

If you want to get a feel for the real time response speed, you can watch this going down in the silent video below:

SSMS Copilot at Work

Watch this video on YouTube

(No audio narration from me on that one because I was jamming out to the John Summit set in Vail as I wrote this post, getting psyched up for Experts Only Vail this weekend. Any EDM fans in the house?)

My verdict: enable code completions, NOW.

I adore Copilot’s code completions because they show up where you are. You have to enable them by going into Tools, Options, Text Editor, Inline Suggestions, and Copilot completions, then go into Inline Suggestions, Preferences, and check Show suggestions only after a pause in typing. (Otherwise they’re obnoxiously fast and when you hit tab, you’ll get the wrong stuff constantly, and you’ll be constantly forced to go back.)

Are they as good as a human can do? Not even close, as this quick & dirty demo showed. Code completions just makes your life easier, silently, as you’re working, without interrupting the tools and workflow you’re already used to using. I feel like it’s about 70% accurate, 30% inaccurate, which sounds terrible, but that 70% is massively helpful.

The Copilot Chat window is much more accurate, but it’s slow as hell in comparison, and it requires you to change your workflow. Nothing against that – it does good work – but I know you, dear reader, and you’re lazy. You’re not gonna switch over to the chat window. You’re gonna just keep typing in SSMS, and for that, Copilot code completions is the bomb.

Now if you’ll forgive me, I wanna go throw a sweater in the car for the road trip lest I shiver.

[Video] Office Hours in North Bay, Ontario

Last Updated March 14, 2026

Brent Ozar

Videos

I’m up in North Bay, Ontario, Canada for the 2026 Can-Am Curling Cup run by Aaron Bertrand – and my team actually won!

Hanging out with Andy Mallon, Aaron Bertrand, Leanne Swart, Michael J Swart, and Ken Mallon before the tournament

Let’s bask in the warmth (cough) of our glory as we go through your top-voted questions from https://pollgab.com/room/brento.

Office Hours: Database Q&A in North Bay, Canada

Watch this video on YouTube

Here’s what we covered:

00:00 Start
02:07 Adrian: With all the storage on the same SAN with nVME, having separate virtual disks for data, log, temp data and temp log still makes sense? Our SQL servers have 7-8 drives each and managing free space is boring.
03:44 Newish Jr DBA: I’m a 7 month old SQL/DBA baby, but I just heard about policy management (PBM). After searching your blogs I did not find much. Do most shops not use it? Could you please elaborate on the pros and cons and primary use case for this tool?
05:05 Silent but Threadly: If a DBA never speaks at conferences or blogs, does that limit their career growth?
06:07 A Rose: Is “Database Engineer” just a rebranded DBA, or is it actually a different skill set?
07:02 MyTeaGotCold: Got much left to do for updating your mastering classes for SQL Server 2025?
08:49 Not Brent O: At what size or number of servers do you recommend clients use Central Management Servers (CMS), and any other advice regarding CMS?
09:20 Meatbag: How is AI changing your training classes and conference sessions?
11:35 For a Friend: How many years of experience does someone really need before they can call themselves a “Senior DBA”?
13:11 MidwestDataPerson: I use SP_Rename to switch tables. Any gotchas about this approach? What about indexes and statistics? e.g. ‘Table1’ becomes ‘Table1_old’ and ‘Table1_new’ becomes ‘Table1’. Indexes are built on ‘Table1_new’ prior to rename.

Updated First Responder Kit and Consultant Toolkit for March 2026

Last Updated March 18, 2026

Brent Ozar

First Responder Kit Updates

For the last few years, I’ve slowed the First Responder Kit release down to once per quarter. It felt pretty feature-complete, and I didn’t want to make y’all upgrade any more than you have to. You’re busy, and I don’t want to take up your time unless there’s something really big to gain from doing an upgrade.

However, things are changing fast – in a good way – and you can blame AI.

Thanks to AI, we’re able to iterate faster, and bring in more improvements in less time than I ever would have thought possible. I know a lot of folks out there hate AI, but this release is a good example of how it can make your job better.

sp_BlitzIndex now offers AI advice for a table’s indexes. It’s as easy as:

sp_BlitzIndex @TableName = 'Users', @AI = 2;

1	sp_BlitzIndex @TableName = 'Users', @AI = 2;

And you get a new result set with advice from ChatGPT or Gemini:

Click on it to see the advice in more detail, in Markdown for easy copy/pasting into client-friendly recommendations:

Including the exact index creation & undo scripts:

For more details on that, check out the new documentation on Using AI with the First Responder Kit, this free video on using @AI = 2, and this video on using @AI = 1.

Another big set of commits this month: Erik Darling had the brilliant idea to run the FRK scripts through Claude Code for an automated code review. I’m kicking myself for not doing it sooner. It found a bunch of real bugs across the scripts, stuff that slipped past everybody for years, plus a lot of code quality things. You can click on each issue in the below list to see the specific fixes for each proc. Make no mistake, most of these weren’t fluffy “your syntax should be better” bugs – these were real bugs, like sp_BlitzLock was over-reporting memory grant values by 8x because a DMV’s contents was already in KB as opposed to data pages. That’s the kind of bug that humans are rarely going to catch because we rarely do things like compare a query’s memory grants between diagnostic tools and their plans.

This release has a breaking change in the AI config tables for sp_BlitzCache (and now, sp_BlitzIndex as well.) We used to have both the AI providers and prompts in the same table, but I needed to normalize that out into two tables now that we’re adding AI capabilities to more procs. If you’ve started playing around with sp_BlitzCache’s AI config table, run this script to migrate your configs to the new table structure before running the new version of sp_BlitzCache.

To get the new version:

Download the updated FirstResponderKit.zip
Get The Consultant Toolkit to quickly export the First Responder Kit results into an easy-to-share spreadsheet
PowerShell users: run Install-DbaFirstResponderKit from dbatools

sp_Blitz Changes

Enhancement: add warning about AI-influencing Agents.md and Consitution.md extended properties being present in user databases. (#3798)
Enhancement: warn if Automatic Tuning is in a non-default state. (#3800, thanks Reece Goding.)
Enhancement: skip Google Cloud SQL admin database gcloud_cloudsqladmin. (#3818, thanks Vlad Drumea.)
Fix: typo in Acclerated Database Recovery Enabled. (#3796, thanks Christophe Platteeuw.)
Fix: typo in “individial”. (#3835, thanks CuriousGeoSq and GitHub Copilot – this was our first completely robot-performed bug fix done inside of Github.com. I was delighted by how easy the process was.)
Fix: code review by Claude Code found 14 assorted bugs. (#3807 and #3808, thanks Erik Darling.)

sp_BlitzCache Changes

Breaking change: the last release used a single Blitz_AI table to hold both AI provider configurations and AI prompts. In this release, to support sp_BlitzIndex having its own set of prompts, we split that Blitz_AI table into two tables. This script will migrate the data from old to new tables. (#3823)
Enhancement: include the CONSTITUTION.md extended database property when building an AI prompt so that your company’s code and database standards will (hopefully) be honored. (#3809)
Fix: performance tuning by removing a duplicate join. (#3791, thanks Connor Moolman.)
Fix: code review by Claude Code found 16 assorted bugs. (#3806, thanks Erik Darling.)

sp_BlitzIndex Changes

Enhancement: skip Google Cloud SQL admin database gcloud_cloudsqladmin. (#3818, thanks Vlad Drumea.)
Enhancement: add AI advice for table-level index review. (#3670, #3827, #3837)
Enhancement: add more support for JSON indexes. Note that SQL Server itself doesn’t appear to track index usage statistics on these indexes, at least not yet as of 2025 CU3. (#3736)
Fix: in table level detail mode, show details for resumable index builds that are still building for the first time. (#3812, thanks Reece Goding.)
Fix: don’t error out when @GetAllDatabases = 1 is used with restricted permissions. (#3820, thanks Vlad Drumea.)

sp_BlitzLock Changes

Fixes: code review by Claude Code found 9 assorted bugs and a handful of code quality issues. (#3802, thanks Erik Darling.)
Fix: no longer fails if login doesn’t have select permissions on msdb.dbo.sysjobsteps. (#3816, thanks Vlad Drumea.)

sp_BlitzWho Changes

Fixes: code review by Claude Code found 4 assorted bugs and a couple of code quality issues. (#3803, thanks Erik Darling.)
Fix: no longer fails if login doesn’t have select permissions on msdb.dbo.sysjobs. (#3814, thanks Vlad Drumea.)

Wanna watch me work on some of these pull requests? Here was a live stream with me plugging along:

Working on First Responder Kit Pull Requests

Watch this video on YouTube

Consultant Toolkit Changes

We didn’t release a Consultant Toolkit in conjunction with this month’s FRK release. We’re having some problems with our automated build system, and I didn’t want to hold back the FRK release. Once we get that fixed, I’ll actually do another quiet FRK release (because we’ve already got a couple of things in the works for it), and email the Consultant Toolkit owners about that new release.

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. Be patient: it’s staffed by volunteers with day jobs. If it’s your first time in the community Slack, get started here.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

Get Free AI Query Advice in PasteThePlan.

Last Updated March 12, 2026

Brent Ozar

PasteThePlan.com

At PasteThePlan.com, you can paste execution plans into your browser, then send a link to someone else to get query advice. It’s useful for online forums, Stack Exchange, and the like.

After you paste the plan, you’ve got a new AI Suggestions tab. It sends your query plan (and my custom prompt) up to ChatGPT 5.3, and in 30 seconds or less, you get a second opinion on ways to make it go faster:

Here’s the prompt we’re using for now, in case you’d rather do this kind of thing yourself:

Please give specific, actionable advice for the attached Microsoft SQL Server query plan. You can give recommendations to tune the query or the indexes on the underlying tables. Do not give server-level or database-level advice: stay focused on this specific query, these specific tables. Your one-time advice will be handed to the developer responsible for the database. They will not be able to change server-level or database-level settings, and they will not be able to correspond with you again. Distill your advice down to the most important things that are likely to make a difference in performance. Deliver your advice in a friendly, upbeat way because you are on the same team, rooting for their success, and having a good time.

Right now, we’re using OpenAI’s ChatGPT 5.3 with a 25-second timeout. If it times out, we advise you to get out your own wallet and use your own AI tool, since we have to pay for these API calls. I like you, though. I think you’re worth it.

[Video] Office Hours, Standing in the Ocean Edition

Last Updated March 12, 2026

Brent Ozar

Videos

My tripod is probably never gonna recover from the salt water, but the water was so nice that I couldn’t resist. This is a 360 video, so you can grab the screen and move it around to see Magens Bay in St Thomas, US Virgin Islands, as I go through your top-voted questions from https://pollgab.com/room/brento.

Office Hours in the Water, St. Thomas, USVI

Watch this video on YouTube

Here’s what we covered:

00:00 Start
01:22 NotCloseEnoughToRetirementToStopLearning: Hi Brent-In a recent office hours you talked about aggressive stats maint. causing plan sensitivity\cache issues. My shop is aggressive on stats. Can you talk about how to investigate if we have this problem to a point we should adjust are current practice?
02:36 kladze: I’m trying to learn XE, most written material out there is how to setup a basic session and then query it. But since there is a huge amount of objects you can capture, i find it almost impossible to find the once i need. How can i properly learn about XE and the objects i need?
03:37 Sujan N: What is your take on certifications? Eg: DP-300. Does it actually help you step up? What advice would you give to an 8-month old dba on leveling up? Thanks for all the help!
04:50 Mark Richey: With opensource DB solutions like Postgress being the back end of very publicly facing and hugely scaled solutions like ChatGPT along with the like of ahem yourself using and teaching them, can you foresee the big licence products slowly becoming a thing of the past?
05:23 racheld933: Regarding upgrades from 2008r2 or 2012 to 2025, is there ever a situation where you’d want to upgrade to a lower version like 2019 or 2022 before 2025? Or is a direct upgrade always the way to go? I’ve upgraded Postgres in AWS RDS where I was forced to do version hops.
07:51 ArchipelagoDBA: You do recommend do update statistics maybe weekly. I get your reasoning behind that but isn’t it there then a risk that you will run into stale statistics like values outside of the histogram?
08:35 Sov: Any thoughts on managing databases as code in git and building/deploying DACPAC’s to manage the production schema? My org is working to adopt this model
09:29 dbApe: Sometimes my SQL server gets stuck on UpdateQPStats, where app is unavailable, AutoUpdateStats and AutoUpdateStatsAsync are set to True, Is this my cue to turn it off?
10:33 Andrew: Hi, I’ve stumbled into a DBA job and taken on around 30 database servers which were somewhat managed by several different people across the business prior to this. What would be your recommendation to get some consistency across them now that one person is managing them?

Using Claude Code with SQL Server and Azure SQL DB

Last Updated March 12, 2026

Brent Ozar

Let’s start with a 7-minute demo video – I didn’t edit this down because I want you to be able to see what happens in real time. In this video, I point the desktop version of Claude Code at a Github issue for the First Responder Kit and tell it to do the needful:

Claude Code Creating a Pull Request

Watch this video on YouTube

That’s certainly not the most complex Github issue in the world, but the idea in that short video was to show you how easy the overall workflow is, and why you and your coworkers might find it attractive.

Now, let’s zoom out and talk big picture.

The rest of this blog post is not for people who already use Claude Code. I don’t wanna hear Code users complaining in the comments about how I didn’t cover X feature or Y scenario. This is a high-level ~2,000-word overview of what it is, why you’d want it, what you’ll need to talk to your team about, and where to go to learn more.

I should also mention that I use a ton of bullet points in my regular writing. As with all of my posts, none of this is written with AI, period, full stop. These words come directly from my booze-addled brain, written as of March 2026, and this stuff will undoubtedly drift out of correctness over time.

What’s Claude Code?

Think of it as an app (either desktop or command line) that can call other apps including:

sqlcmd – Microsoft’s command-line utility for running queries. You’re used to using SSMS because it’s much prettier and more powerful, but sqlcmd is fine if all you need to do is run queries and get results, and that’s all Claude Code needs to get started. As you get more advanced, you can use something called an MCP that gives Claude Code an easier way to chat with the database.
Git / Github – so that it can get the latest versions of your app code (or DBA scripts, or in this case, the First Responder Kit) from source control, make changes, and submit pull requests for you to review. For the purposes of this post, I’m just gonna use the term Github, but if your company uses a different source control method, the same principles apply.

That means it has access to:

Your Github issues and pull requests – which may present confidentiality issues for your company.
Your local file system – in theory, you might be able to lock this down, but in practice you’re probably going to gradually expand Claude Code’s permissions to let it do more stuff over time.
A database server – so think about where you’re pointing this thing, and what login you give it. If it’s going to test code changes, it’s probably going to need to alter procs, create/alter/drop tables, insert/update/delete test data, etc. On harder/longer tasks, it’s also going to be processing in the background while you’re doing other stuff, so you’re probably going to want to give it its own SQL Server service for its development use so it doesn’t hose up yours.
Your code base – and if everything before didn’t raise security and privacy concerns, this one certainly should.

Think of it as an outside contractor.

When your company hires outside contractors, they put a lot of legal protections in place. They’ll set up:

A non-disclosure agreement to make sure the contractor doesn’t share your secrets with the rest of the world
A contract specifying what exactly each side is responsible for and what they’ll deliver to each other
Insurance requirements to make sure the contractor will be able to pay for any egregious mistakes
Human resources standards to make sure the contractor isn’t high and hallucinating while they work

With AI tools, you don’t really get any of that. That means if you choose to hire one of these tools for your company, all of this is on you. Even worse, anybody on your team can endanger your entire company if they don’t make good decisions along the way. I can totally understand why some/most companies are a little gun-shy on this stuff. It’s right to be concerned about these risks.

Here – and most of the time when you see me working with AI on the blog or videos – I’m working with the open source First Responder Kit, or code that I use as part of my training classes. This stuff is all open source, licensed under the MIT License. I’m not concerned about AI companies stealing my code.

That’s the best way for you to get started, too: play around with Claude Code on an open source Github repo that you usually use as a user (not a developer), like the First Responder Kit, Ola Hallengren’s maintenance scripts, Erik Darling’s SQL Server Performance Monitor, DBAtools, or even Microsoft’s SQL Server documentation. Learn to use Claude Code there, and later on, after you’ve built up confidence and a few good wins, then think about bringing it into your own company to work on your day job stuff. And when you do that…

When your company brings in an outside contractor…

The security and legal teams are going to care about:

What Claude Code has access to – aka, Github, your local file system, your development database server, etc.
Where Claude Code sends that data for thinking/processing – you should assume that it’s sending all of the accessible data somewhere
If you send that data outside your company walls for thinking/processing, your company is also going care about how the thinker/processor uses your data – as in, not just to process your requests, but possibly for analysis to help the overall public or paying users

This leads to one of the big decisions when you’re using Claude Code: where does the thinking/processing happen?

The thinking can be done locally or remotely.

Claude Code is an app, but the thinking doesn’t actually happen in the app. Claude Code sends your data, prompt, database schema, etc somewhere.

Most people use Anthropic’s servers. They’re the makers of Claude Code. For around $100/month per person, you get unlimited processing up in their cloud. The advantage of using Anthropic’s servers is that you’ll get the fastest performance, with the biggest large language models (LLMs) that have the best thinking power, most accurate answers, and largest memories (context.) The drawback, of course, is that you’re sending your data outside your company’s walls, and you may not be comfortable with that.

If you’re not comfortable with Anthropic, maybe your company is more comfortable with Google Gemini’s models, or OpenAI’s ChatGPT models. At any given time, it’s an arms race between those top companies (and others, like hosting companies like OpenRouter) as to who produces the best tradeoffs for processing speed, accuracy, and cost.

If you’re not comfortable with any of those, you can do the processing on your own server. When I say “server”, that could be a Docker container running on your laptop, an app installed on your gaming PC with a high-powered video card, or a shared server at your company with a bunch of GPUs stuffed in it.

In that case, it’s up to you to pick the best LLM that you can, that runs as quickly as possible, given your server’s hardware. There are tiny not-so-bright models that run (or perhaps, leisurely stroll) on hardware as small as a Raspberry Pi. There are pretty smart models that require multiple expensive and power-hungry video cards. But even the best local models can’t compete with what you get up in Anthropic’s servers today.

The good news is that you don’t have to make some kind of final decision: you can switch between hosted and local models by just changing Claude Code’s config file.

The contractor and prompt qualities affect the results.

Generally speaking, the better/newer LLM that you use, and the smaller of a problem you’re working with, the more vague prompts you can get away with, like “we’re having deadlock problems – can you fix that?”

On the other hand, the older/smaller/cheaper LLM that you use – especially small locally hosted models – the more specific and directed your prompts have to be to get great results. For example, you may have to say something like, “sp_AddCustomer and sp_AddOrder are deadlocking on the CustomerDetails table when both procs are called simultaneously. Can you reduce the deadlock potential by making code changes to one or both of those procs? You can use hints, query rewrites, retry logic, whatever, as long as the transactions still finish the same way.”

And no matter what kind of LLM you’re using, the more ambitious your code changes become, the more important the prompt becomes. When I’m adding a major new feature or proposing a giant change, I start a chat session with Claude – not Claude Code, but just plain old Claude, the chat UI like ChatGPT – and say something like:

I’m working on the attached sp_Blitz.sql script, which builds a health check report on Microsoft SQL Server. It isn’t currently compatible with Azure SQL DB because it uses sp_MSforeachdb and some of the dynamic SQL uses the USE command. I’d like to use Claude Code to perform the rewrite. Can you review the code, and help me write a good prompt for Claude Code?

I know, it sounds like overkill, using one AI to tell another AI what to do, but I’ve found that in a matter of seconds, it produces a muuuuch better prompt than I would have written, taking more edge cases of the code into account. Then I edit that prompt, clarify some of my design decisions and goals, and then finally take the finished prompt over to Claude Code to start work there.

For now, I use Claude Code on a standalone machine.

I really like to think of AI tools like Claude Code as an outside contractor.

I’m sure the contractor is a nice person, and I have to trust it at least a little – after all, I’m the guy who hired it, and I shouldn’t hire someone that I don’t trust. Still, though, I gotta put safeguards in place.

So I keep Claude Code completely isolated.

I know that sounds a little paranoid, but right now in the wild west of AI, paranoia is a good thing.

For me, it starts with isolated hardware. A few years ago, I got a Windows desktop to use for gaming, streaming, and playing around with local large language models (LLMs). It’s got a fast processor, 128GB RAM, a decently powerful NVidia 4090 GPU, Windows 11, Github, and SQL Server 2025.

I think of that computer as Claude Code’s machine: he works there, he lives there. That way, I can guarantee none of my clients’ code or data is on there, and it doesn’t have things like my email either. When I wanna work, stream, record videos from that Windows machine, I just remote desktop into it from my normal Mac laptop.

When I wanna do client work without sending the data to Anthropic, I’ve got Ollama set up on that machine too. It’s a free, open source platform for running your own local models. It supports a huge number of LLMs, and there is no one right answer for which model to use. I love finding utilities like llmfit which check hardware to see what models can be run on it, and finding posts like which models run best on NVidia RTX 40 series GPUs as of April 2025 or on Apple Silicon processors as of February 2026, because they help me take the guesswork out of experimenting. I copy client data onto that machine temporarily, do that local work, and then delete the client data again before reconfiguring Claude Code to talk to Anthropic’s servers.

How you can get started with Claude Code

Your mission, should you choose to accept it, is to add a new warning to sp_Blitz when a SQL Server has Availability Groups enabled at the server level, but it doesn’t have any databases in an AG. To help, I’ve written a short, terse Github issue for this request, and a longer, more explicit one so you can also see how the quality of the input affects the quality of your chosen LLM’s code.

To accomplish the task, the bare minimum tasks would be:

Install Claude Code (I’d recommend the terminal version first because the documentation is much better – the desktop version looks cool, but it’s much harder to get started with)
Clone the First Responder Kit repo locally
Prompt Claude Code to write the code – tell it about the Github issue and ask it to draft a pull request with the improved code, for your review

Stretch goals:

Set up a SQL Server instance for Claude Code to connect to – could be an existing instance or a new one
Set up sqlcmd or the SQL Server MCP so Claude Code can connect to it – if you use the MCP, you’ll need to edit Claude Code’s config files to include the server, login, password you want it to use
Prompt Claude Code to test its code

You don’t have to submit your actual work as a pull request – I’m not going to accept any of those pull requests anyway. (I’ll just delete them if they come in – and it’s okay if you do one, I won’t be offended.) These Github issues exist solely to help you learn Claude Code.

How I can help

Unfortunately, I can’t do free personalized support for tens of thousands of readers to get their Claude Code setups up and running. At some point, I might build a paid training class for using Claude Code with SQL Server, and at that point, the paid students would be able to get some level of support. For now, though, I wanted to get this blog post, video, and GitHub issues out there for the advanced folks to start getting ahead of the curve.

However, If your company would like to hire me to help get a jump start on using Claude Code to improve your DBA productivity, proactively find database issues before they strike, and finally start making progress on your known issues backlog, email me.

Row-Level Security Can Slow Down Queries. Index For It.

Last Updated March 6, 2026

Brent Ozar

Execution Plans

The official Azure SQL Dev’s Corner blog recently wrote about how to enable soft deletes in Azure SQL using row-level security, and it’s a nice, clean, short tutorial. I like posts like that because the feature is pretty cool and accomplishes a real business goal. It’s always tough deciding where to draw the line on how much to include in a blog post, so I forgive them for not including one vital caveat with this feature.

Row-level security can make queries go single-threaded.

This isn’t a big deal when your app is brand new, but over time, as your data gets bigger, this is a performance killer.

Setting Up the Demo

To illustrate it, I’ll copy a lot of code from their post, but I’ll use the big Stack Overflow database. After running the below code, I’m going to have two Users tables with soft deletes set up: a regular dbo.Users one with no security, and a dbo.Users_Secured one with row-level security so folks can’t see the IsDeleted = 1 rows if they don’t have permissions.

USE StackOverflow;
GO
/* The Stack database doesn't ship with soft deletes,
so we have to add an IsDeleted column to implement it. 
Fortunately this is a metadata-only operation, and the
table isn't rewritten. All rows just instantly get a
0 default value. */
ALTER TABLE dbo.Users ADD IsDeleted BIT NOT NULL DEFAULT 0;
GO
/* Copy the Users table into a new Secured one: */
CREATE TABLE [dbo].[Users_Secured](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AboutMe] [nvarchar](max) NULL,
    [Age] [int] NULL,
    [CreationDate] [datetime] NOT NULL,
    [DisplayName] [nvarchar](40) NOT NULL,
    [DownVotes] [int] NOT NULL,
    [EmailHash] [nvarchar](40) NULL,
    [LastAccessDate] [datetime] NOT NULL,
    [Location] [nvarchar](100) NULL,
    [Reputation] [int] NOT NULL,
    [UpVotes] [int] NOT NULL,
    [Views] [int] NOT NULL,
    [WebsiteUrl] [nvarchar](200) NULL,
    [AccountId] [int] NULL,
    [IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_Users_Secured_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF, 
    DATA_COMPRESSION = PAGE) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Users_Secured] ADD  DEFAULT ((0)) FOR [IsDeleted]
GO
SET IDENTITY_INSERT dbo.Users_Secured ON;
GO
INSERT INTO dbo.Users_Secured (Id, AboutMe, Age, CreationDate, 
    DisplayName, DownVotes, EmailHash, LastAccessDate, 
    Location, Reputation, UpVotes, Views, WebsiteUrl, 
    AccountId, IsDeleted)
SELECT Id, AboutMe, Age, CreationDate, 
    DisplayName, DownVotes, EmailHash, LastAccessDate, 
    Location, Reputation, UpVotes, Views, WebsiteUrl, 
    AccountId, IsDeleted
FROM dbo.Users;
GO
SET IDENTITY_INSERT dbo.Users_Secured OFF;
GO
DropIndexes @TableName = 'Users';
GO





CREATE LOGIN TodoDbUser WITH PASSWORD = 'Long@12345';
GO
CREATE USER TodoDbUser FOR LOGIN TodoDbUser;
GO
GRANT SELECT, INSERT, UPDATE ON dbo.Users TO TodoDbUser;
GO


CREATE FUNCTION dbo.fn_SoftDeletePredicate(@IsDeleted BIT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_result
    WHERE
        (
            DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('TodoDbUser')
            AND @IsDeleted = 0
        )
        OR DATABASE_PRINCIPAL_ID() <> DATABASE_PRINCIPAL_ID('TodoDbUser');
GO

CREATE SECURITY POLICY dbo.Users_Secured_SoftDeleteFilterPolicy
ADD FILTER PREDICATE dbo.fn_SoftDeletePredicate(IsDeleted)
ON dbo.Users_Secured
WITH (STATE = ON);
GO

USE StackOverflow;

/* The Stack database doesn't ship with soft deletes,

so we have to add an IsDeleted column to implement it.

Fortunately this is a metadata-only operation, and the

table isn't rewritten. All rows just instantly get a

0 default value. */

ALTER TABLE dbo.Users ADD IsDeleted BIT NOT NULL DEFAULT 0;

/* Copy the Users table into a new Secured one: */

CREATE TABLE [dbo].[Users_Secured](

[Id] [int] IDENTITY(1,1) NOT NULL,

[AboutMe] [nvarchar](max) NULL,

[Age] [int] NULL,

[CreationDate] [datetime] NOT NULL,

[DisplayName] [nvarchar](40) NOT NULL,

[DownVotes] [int] NOT NULL,

[EmailHash] [nvarchar](40) NULL,

[LastAccessDate] [datetime] NOT NULL,

[Location] [nvarchar](100) NULL,

[Reputation] [int] NOT NULL,

[UpVotes] [int] NOT NULL,

[Views] [int] NOT NULL,

[WebsiteUrl] [nvarchar](200) NULL,

[AccountId] [int] NULL,

[IsDeleted] [bit] NOT NULL,

CONSTRAINT [PK_Users_Secured_Id] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF,

DATA_COMPRESSION = PAGE) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[Users_Secured] ADD DEFAULT ((0)) FOR [IsDeleted]

SET IDENTITY_INSERT dbo.Users_Secured ON;

INSERT INTO dbo.Users_Secured (Id, AboutMe, Age, CreationDate,

DisplayName, DownVotes, EmailHash, LastAccessDate,

Location, Reputation, UpVotes, Views, WebsiteUrl,

AccountId, IsDeleted)

SELECT Id, AboutMe, Age, CreationDate,

DisplayName, DownVotes, EmailHash, LastAccessDate,

Location, Reputation, UpVotes, Views, WebsiteUrl,

AccountId, IsDeleted

FROM dbo.Users;

SET IDENTITY_INSERT dbo.Users_Secured OFF;

DropIndexes @TableName = 'Users';

CREATE LOGIN TodoDbUser WITH PASSWORD = 'Long@12345';

CREATE USER TodoDbUser FOR LOGIN TodoDbUser;

GRANT SELECT, INSERT, UPDATE ON dbo.Users TO TodoDbUser;

CREATE FUNCTION dbo.fn_SoftDeletePredicate(@IsDeleted BIT)

RETURNS TABLE

WITH SCHEMABINDING

RETURN

SELECT 1 AS fn_result

WHERE

(

DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('TodoDbUser')

AND @IsDeleted = 0

)

OR DATABASE_PRINCIPAL_ID() <> DATABASE_PRINCIPAL_ID('TodoDbUser');

CREATE SECURITY POLICY dbo.Users_Secured_SoftDeleteFilterPolicy

ADD FILTER PREDICATE dbo.fn_SoftDeletePredicate(IsDeleted)

ON dbo.Users_Secured

WITH (STATE = ON);

Now let’s start querying the two tables to see the performance problem.

Querying by the Primary Key: Still Fast

The Azure post kept things simple by not using indexes, so we’ll start that way too. I’ll turn on actual execution plans and get a single row, and compare the differences between the tables:

SELECT * FROM dbo.Users
    WHERE Id = 26837;

SELECT * FROM dbo.Users_Secured
    WHERE Id = 26837;

SELECT * FROM dbo.Users

WHERE Id = 26837;

SELECT * FROM dbo.Users_Secured

WHERE Id = 26837;

If all you’re doing is getting one row, and you know the Id of the row you’re looking for, you’re fine. SQL Server dives into that one row, fetches it for you, and doesn’t need multiple CPU cores to accomplish the goal. Their actual execution plans look identical at first glance:

If you hover your mouse over the Users_Secured table operation, you’ll notice an additional predicate that we didn’t ask for: row-level security is automatically checking the IsDeleted column for us:

Querying Without Indexes: Starts to Get Slower

Let’s find the top-ranked people in Las Vegas:

SELECT TOP 101 *
    FROM dbo.Users
    WHERE Location = N'Las Vegas, NV'
    ORDER BY Reputation DESC;

SELECT TOP 101 *
    FROM dbo.Users_Secured
    WHERE Location = N'Las Vegas, NV'
    ORDER BY Reputation DESC;

SELECT TOP 101 *

FROM dbo.Users

WHERE Location = N'Las Vegas, NV'

ORDER BY Reputation DESC;

SELECT TOP 101 *

FROM dbo.Users_Secured

WHERE Location = N'Las Vegas, NV'

ORDER BY Reputation DESC;

Their actual execution plans show the top query at about 1.4 seconds for the unsecured table, and the bottom query at about 3 seconds for the secured table:

The reason isn’t security per se: the reason is that the row-level security function inhibits parallelism. The top query plan went parallel, and the bottom query did not. If you click on the secured table’s SELECT icon, the plan’s properties will explain that the row-level security function can’t be parallelized:

That’s not good.

When you’re using the database’s built-in row-level security functions, it’s more important than ever to do a good job of indexing. Thankfully, the query plan has a missing index recommendation to help, so let’s dig into it.

The Missing Index Recommendation Problems

Those of you who’ve been through my Fundamentals of Index Tuning class will have learned how Microsoft comes up with missing index recommendations, but I’mma be honest, dear reader, the quality of this one surprises even me:

Missing Index (Impact 99.6592): 
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] 
ON [dbo].[Users_Secured] ([Location]) 
INCLUDE ([AboutMe],[Age],[CreationDate],[DisplayName],[DownVotes],
[EmailHash],[LastAccessDate],[Reputation],[UpVotes],[Views],
[WebsiteUrl],[AccountId],[IsDeleted])

Missing Index (Impact 99.6592):

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[Users_Secured] ([Location])

INCLUDE ([AboutMe],[Age],[CreationDate],[DisplayName],[DownVotes],

[EmailHash],[LastAccessDate],[Reputation],[UpVotes],[Views],

[WebsiteUrl],[AccountId],[IsDeleted])

The index simply ignores the IsDeleted and Reputation columns, even though they’d both be useful to have in the key! The missing index hint recommendations are seriously focused on the WHERE clause filters that the query passed in, but not necessarily on the filters that SQL Server is implementing behind the scenes for row-level security. Ouch.

Let’s do what a user would do: try creating the recommended index on both tables – even though the number of include columns is ridiculous – and then try again:

CREATE NONCLUSTERED INDEX Location_Includes 
    ON [dbo].[Users] ([Location]) 
    INCLUDE ([AboutMe],[Age],[CreationDate],[DisplayName],[DownVotes],
    [EmailHash],[LastAccessDate],[Reputation],[UpVotes],[Views],
    [WebsiteUrl],[AccountId],[IsDeleted]);
GO
CREATE NONCLUSTERED INDEX Location_Includes 
    ON [dbo].[Users_Secured] ([Location]) 
    INCLUDE ([AboutMe],[Age],[CreationDate],[DisplayName],[DownVotes],
    [EmailHash],[LastAccessDate],[Reputation],[UpVotes],[Views],
    [WebsiteUrl],[AccountId],[IsDeleted]);
GO
SELECT TOP 101 *
    FROM dbo.Users
    WHERE Location = N'Las Vegas, NV'
    ORDER BY Reputation DESC;

SELECT TOP 101 *
    FROM dbo.Users_Secured
    WHERE Location = N'Las Vegas, NV'
    ORDER BY Reputation DESC;
GO

CREATE NONCLUSTERED INDEX Location_Includes

ON [dbo].[Users] ([Location])

INCLUDE ([AboutMe],[Age],[CreationDate],[DisplayName],[DownVotes],

[EmailHash],[LastAccessDate],[Reputation],[UpVotes],[Views],

[WebsiteUrl],[AccountId],[IsDeleted]);

CREATE NONCLUSTERED INDEX Location_Includes

ON [dbo].[Users_Secured] ([Location])

INCLUDE ([AboutMe],[Age],[CreationDate],[DisplayName],[DownVotes],

[EmailHash],[LastAccessDate],[Reputation],[UpVotes],[Views],

[WebsiteUrl],[AccountId],[IsDeleted]);

SELECT TOP 101 *

FROM dbo.Users

WHERE Location = N'Las Vegas, NV'

ORDER BY Reputation DESC;

SELECT TOP 101 *

FROM dbo.Users_Secured

WHERE Location = N'Las Vegas, NV'

ORDER BY Reputation DESC;

Our actual execution plans are back to looking identical:

Neither of them require parallelism because we can dive into Las Vegas, and read all of the folks there, filtering out the appropriate IsDeleted rows, and then sort the remainder, all on one CPU core, in a millisecond. The cost is just that we literally doubled the table’s size because the missing index recommendation included every single column in the table!

A More Realistic Single-Column Index

When faced with an index recommendation that includes all of the table’s columns, most DBAs would either lop off all the includes and just use the keys, or hand-review the query to hand-craft a recommended index. Let’s start by dropping the old indexes, and creating new ones with only the key column that Microsoft had recommended:

CREATE INDEX Location ON dbo.Users(Location);
DROP INDEX Location_Includes ON dbo.Users;
CREATE INDEX Location ON dbo.Users_Secured(Location);
DROP INDEX Location_Includes ON dbo.Users_Secured;
GO
SELECT TOP 101 *
    FROM dbo.Users
    WHERE Location = N'Las Vegas, NV'
    ORDER BY Reputation DESC;

SELECT TOP 101 *
    FROM dbo.Users_Secured
    WHERE Location = N'Las Vegas, NV'
    ORDER BY Reputation DESC;
GO

CREATE INDEX Location ON dbo.Users(Location);

DROP INDEX Location_Includes ON dbo.Users;

CREATE INDEX Location ON dbo.Users_Secured(Location);

DROP INDEX Location_Includes ON dbo.Users_Secured;

SELECT TOP 101 *

FROM dbo.Users

WHERE Location = N'Las Vegas, NV'

ORDER BY Reputation DESC;

SELECT TOP 101 *

FROM dbo.Users_Secured

WHERE Location = N'Las Vegas, NV'

ORDER BY Reputation DESC;

The actual execution plans of both queries perform identically:

Summary: Single-Threaded is Bad, but Indexes Help.

The database’s built-in row-level security is a really cool (albeit underused) feature to help you accomplish business goals faster, without trying to roll your own code. Yes, it does have limitations, like inhibiting parallelism and making indexing more challenging, but don’t let that stop you from investigating it. Just know you’ll have to spend a little more time doing performance tuning down the road.

In this case, we’re indexing not to reduce reads, but to avoid doing a lot of work on a single CPU core. Our secured table still can’t go parallel, but thanks to the indexes, the penalty of row-level security disappears for this particular query.

Experienced readers will notice that there are a lot of topics I didn’t cover in this post: whether to index for the IsDeleted column, the effect of residual predicates on IsDeleted and Reputation, and how CPU and storage are affected. However, just as Microsoft left off the parallelism thing to keep their blog post tightly scoped, I gotta keep mine scoped too! This is your cue to pick up this blog post with anything you’re passionate about, and extend it to cover the topics you wanna teach today.

Logical Reads Aren’t Repeatable on Columnstore Indexes. (sigh)

Last Updated March 6, 2026

Brent Ozar

Columnstore Indexes

Sometimes I really hate my job.

Forever now, FOREVER, it’s been a standard thing where I can say, “When you’re measuring storage performance during index and query tuning, you should always use logical reads, not physical reads, because logical reads are repeatable, and physical reads aren’t. Physical reads can change based on what’s in cache, what other queries are running at the time, your SQL Server edition, and whether you’re getting read-ahead reads. Logical reads just reflect exactly the number of pages read, no matter where the data came from (storage or cache), so as long as that number goes down, you’re doing a good job.”

To illustrate it, we’ll start with the large version of the Stack Overflow database, and count the number of rows in the Users table.

SET STATISTICS IO ON;
GO
DBCC DROPCLEANBUFFERS;
GO
SELECT COUNT(*) FROM dbo.Users;
GO 3

SET STATISTICS IO ON;

DBCC DROPCLEANBUFFERS;

SELECT COUNT(*) FROM dbo.Users;

GO 3

Statistics io output shows that the first execution has to read pages up from disk because they’re not in cache yet:

Beginning execution loop

(1 row affected)
Table 'Users'. Scan count 1, logical reads 317822, physical reads 4, 
page server reads 0, read-ahead reads 329114, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)
Table 'Users'. Scan count 1, logical reads 317822, physical reads 0,
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)
Table 'Users'. Scan count 1, logical reads 317822, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.
Batch execution completed 3 times.

Beginning execution loop

(1 row affected)

Table 'Users'. Scan count 1, logical reads 317822, physical reads 4,

page server reads 0, read-ahead reads 329114, page server read-ahead reads 0,

lob logical reads 0, lob physical reads 0, lob page server reads 0,

lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

Table 'Users'. Scan count 1, logical reads 317822, physical reads 0,

page server reads 0, read-ahead reads 0, page server read-ahead reads 0,

lob logical reads 0, lob physical reads 0, lob page server reads 0,

lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

Table 'Users'. Scan count 1, logical reads 317822, physical reads 0,

page server reads 0, read-ahead reads 0, page server read-ahead reads 0,

lob logical reads 0, lob physical reads 0, lob page server reads 0,

lob read-ahead reads 0, lob page server read-ahead reads 0.

Batch execution completed 3 times.

The first execution has 4 physical reads and 329,114 read-ahead reads. Those were all read up off disk, into memory. But the whole time, logical reads stays consistent, so it’s useful for measuring performance tuning efforts regardless of what’s in cache.

The same thing is true if we create a nonclustered rowstore index too:

CREATE INDEX Age ON dbo.Users(Age);
GO
DBCC DROPCLEANBUFFERS;
GO
SELECT COUNT(*) FROM dbo.Users;
GO 3

CREATE INDEX Age ON dbo.Users(Age);

DBCC DROPCLEANBUFFERS;

SELECT COUNT(*) FROM dbo.Users;

GO 3

Statistics io output shows physical reads & readahead reads on the first execution, but logical reads stays consistent throughout:

Beginning execution loop

(1 row affected)
Table 'Users'. Scan count 1, logical reads 39000, physical reads 1, 
page server reads 0, read-ahead reads 38997, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)
Table 'Users'. Scan count 1, logical reads 39000, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)
Table 'Users'. Scan count 1, logical reads 39000, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.
Batch execution completed 3 times.

Beginning execution loop

(1 row affected)

Table 'Users'. Scan count 1, logical reads 39000, physical reads 1,

page server reads 0, read-ahead reads 38997, page server read-ahead reads 0,

lob logical reads 0, lob physical reads 0, lob page server reads 0,

lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

Table 'Users'. Scan count 1, logical reads 39000, physical reads 0,

page server reads 0, read-ahead reads 0, page server read-ahead reads 0,

lob logical reads 0, lob physical reads 0, lob page server reads 0,

lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

Table 'Users'. Scan count 1, logical reads 39000, physical reads 0,

page server reads 0, read-ahead reads 0, page server read-ahead reads 0,

lob logical reads 0, lob physical reads 0, lob page server reads 0,

lob read-ahead reads 0, lob page server read-ahead reads 0.

Batch execution completed 3 times.

But with columnstore indexes on SQL Server 2017 & newer…

On SQL Server 2017 or newer (not 2016), create a nonclustered columnstore index:

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Age ON dbo.Users(Age);
GO
DBCC DROPCLEANBUFFERS;
GO
SELECT COUNT(*) FROM dbo.Users;
GO 3

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Age ON dbo.Users(Age);

DBCC DROPCLEANBUFFERS;

SELECT COUNT(*) FROM dbo.Users;

GO 3

And watch lob logical reads while we run it 3 times:

Beginning execution loop

(1 row affected)
Table 'Users'. Scan count 2, logical reads 0, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 22432, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 89443, lob page server read-ahead reads 0.
Table 'Users'. Segment reads 22, segment skipped 0.

(1 row affected)
Table 'Users'. Scan count 2, logical reads 0, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 10947, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Segment reads 22, segment skipped 0.

(1 row affected)
Table 'Users'. Scan count 2, logical reads 0, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 10947, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Segment reads 22, segment skipped 0.
Batch execution completed 3 times.

Beginning execution loop

(1 row affected)

Table 'Users'. Scan count 2, logical reads 0, physical reads 0,

page server reads 0, read-ahead reads 0, page server read-ahead reads 0,

lob logical reads 22432, lob physical reads 0, lob page server reads 0,

lob read-ahead reads 89443, lob page server read-ahead reads 0.

Table 'Users'. Segment reads 22, segment skipped 0.

(1 row affected)

Table 'Users'. Scan count 2, logical reads 0, physical reads 0,

page server reads 0, read-ahead reads 0, page server read-ahead reads 0,

lob logical reads 10947, lob physical reads 0, lob page server reads 0,

lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Users'. Segment reads 22, segment skipped 0.

(1 row affected)

Table 'Users'. Scan count 2, logical reads 0, physical reads 0,

page server reads 0, read-ahead reads 0, page server read-ahead reads 0,

lob logical reads 10947, lob physical reads 0, lob page server reads 0,

lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Users'. Segment reads 22, segment skipped 0.

Batch execution completed 3 times.

Lob logical reads shows 22,342 for the first execution, then 10,947 for the next two passes.

This isn’t true on SQL Server 2016, which produces the same logical read numbers every time the columnstore query runs, clean buffer pool or not. Just 2017 and newer.

<sigh> This is why we can’t have nice things.

This is also one of those reasons why it’s so hard to teach training classes. Stuff changes inside the product, and then years later, a demo you wrote no longer produces exactly the same results. You have to try re-running the demo from scratch, thinking you just made a mistake, and then you have to narrow down the root cause, and then to do it right, you really need to check each prior version to understand when the thing changed, and Google trying to find out if anybody else shared this and you just didn’t read that particular post, and then update your own training and write a blog post so that nobody else gets screwed by the same undocumented change, which of course they will, because not everybody reads your blog posts.

You won’t, though, dear reader. At least I helped you out, hopefully. And that makes it all worthwhile. (Not really. I’m going to go have a shot of my office tequila, and it’s not even 10AM as I’m writing this.)

Blog

The density vectors aren’t very useful.

But what about the histogram?

Things change a little for really big outliers.

Multi-column stats just don’t help much by themselves.

Option 1: ask Copilot to write the query for you.

Option 2: use this user-defined function.

Dev-Prod Demon Hunters: Finding the Real Cause of Production Slowness

3 things you’ll get out of this session

My verdict: enable code completions, NOW.

sp_Blitz Changes

sp_BlitzCache Changes

sp_BlitzIndex Changes

sp_BlitzLock Changes

sp_BlitzWho Changes

Consultant Toolkit Changes

For Support

What’s Claude Code?

Think of it as an outside contractor.

When your company brings in an outside contractor…

The thinking can be done locally or remotely.

The contractor and prompt qualities affect the results.

For now, I use Claude Code on a standalone machine.

How you can get started with Claude Code

How I can help

Setting Up the Demo

Querying by the Primary Key: Still Fast

Querying Without Indexes: Starts to Get Slower

The Missing Index Recommendation Problems

A More Realistic Single-Column Index

Summary: Single-Threaded is Bad, but Indexes Help.

But with columnstore indexes on SQL Server 2017 & newer…

Hi! I’m Brent Ozar.

Get Free SQL Stuff

Dev-Prod Demon Hunters:
Finding the Real Cause of Production Slowness