Normally, when SQL Server updates statistics on an object, it invalidates the cached plans that rely on that statistic as well. That's why you'll see recompiles happen after stats updates: SQL Server knows the stats have changed, so it's a good time to build new execution plans based on the changes in the data.
However, updates to system-created stats don't necessarily cause plan recompiles.
Some folks are seeing an ad at the top of their SSMS v22, like this one reported in the feedback site:
Today, Microsoft's using this to test ads for an upcoming conference. Interesting that they give deeper discounts to Reddit readers as opposed to SSMS users, but I digress. Tomorrow, they might be pushing SQL Server 2025 upgrades, or Microsoft Fabric, or Copilot, or whatever.
Say you've got a table with an identity column, something that's supposed to start at 1 and go up to a bajillion:
[crayon-6ab9ee308dc00504444614/]
Back in March 2025 when Microsoft first announced that REGEX support was coming to SQL Server 2025 and Azure SQL DB, I gave it a quick test, and the performance was horrific. It was bad in 3 different ways:
The CPU usage was terrible, burning 60 seconds of CPU time to check a few million rows
It refused to use an index
The cardinality estimation was terrible, hard-coded to 30% of the table
In last week's Query Exercise, I challenged you to play some code golf to generate big spills with tiny T-SQL.
Today, I'm going to walk you through my thought process - the initial attempts I tried and failed with, and the discoveries I made along the way, because I think it makes for fun storytelling.
Nah, not necessarily. SQL Server's query optimizer behavior keeps changing with every freakin' version. Let's illustrate it with a simple query against the Stack Overflow Users table:
[crayon-6ab9ee3090126687445836/]
When I was testing SQL Server 2025's new ability to limit TempDB usage with Resource Governor, I wrote a few fun diabolical demos. One of them was to generate giant spills to TempDB, quickly.
When you're looking at an actual (not estimated) query plan, and you see a yellow exclamation point on a sort operator, that means:
In the Query Plan Pop Quiz, questions 2 and 3 asked you about what the sizes of arrows on query plans meant. The good news is that almost all of you got Question 2 right, but the bad news is that the vast majority of you got Question 3 completely incorrect, and the saddest part of that is that you've been using that inaccurate knowledge to guide your query tuning - and wasting your time.
In last week's Query Plans Pop Quiz, the first question was, someone hands you these two queries and you get their estimated plans to decide which query to tune. Perhaps you get the estimated plans from SSMS, or from sp_BlitzCache, or from your monitoring tool. The question was, which query should you focus on tuning?…
SQL Server Management Studio 22 Preview 3 is out, and it brings with it a new Query Hint Recommendation tool. Start by highlighting the query you want to test, then click Tools, Query Hint Recommendation Tool. It slides out a new pane on the right hand side: The maximum tuning time defaults to 300 seconds,…
Question 1: Pick the Problematic Plan: someone hands you a pair of queries, and you get the estimated query plans. (Perhaps you get the estimated plans from SSMS, or from sp_BlitzCache, or from your monitoring tool.) Which one of these two should you focus on tuning first, Query 1 or Query 2?
Question 2: on an estimated plan, what does the thickness of the colored arrow represent?
You're building a new table or adding a column, and you wanna know which datatype to use: VARCHAR or NVARCHAR?
If you need to store Unicode data, the choice is made for you: NVARCHAR says it's gonna be me.
I have never seen a T-SQL feature that people love as much as NOLOCK.
I keep thinking I've written enough blog posts about it, but a client came up with a new one:
When I'm tuning queries, the normal answer is to make the query perform better - either via changing the T-SQL, adding hints, or adding indexes so that the data's better prepared for the query.
However, sometimes when I'm looking at the output of sp_BlitzCache, I scroll across to the Average Rows column and double-check that the query's actually returning a reasonable number of rows out in the wild.
I've posted a couple of updated videos to my Mastering courses.
In Mastering Server Tuning, I updated and expanded the RESOURCE_SEMAPHORE module to cover more details about query workspace grants, plus cover SQL Server 2022's updates to Adaptive Memory Grants.
In your most recent Query Exercise challenge, I gave you these two tables:
[crayon-6ab9ee3093721431797929/]
I'mma be honest with you, dear reader: this query exercise stems from a client problem that I couldn't figure out how to solve in the time constraints that I had, and it drove me crazy. I wanted to put hours into this to figure it out, and I was sure there'd be a fun T-SQL way to do it.
I use AI tools constantly in my work, so when the new SSMS v21 came out with Copilot support, I was excited to try it. The short story is that the user interface is fine, and it doesn't feel buggy, but the advice is simply terrible compared to other LLMs due to the prompt Copilot is using. You're better off using tools like ChatGPT, Gemini, and Claude directly for now.
Several years ago, I wrote a post about how I configure older versions of SQL Server Management Studio, but now that SSMS v21 is out, it's time to revisit that post. SSMS v21 is built on the Visual Studio shell, so it's got a lot of cool new tricks. Let's make sure you're using 'em.
We're heading towards graduation time.
In just a few short months, your high school kids are going to be packing up their bags and heading off to college. It's a time of adventure and discovery, and the first time they'll be away from your parenting and guiding influences.
