SQL Server Consulting, Education, and Training
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I’m going to be totally open and honest with you, dear reader: I’ve been experimenting with… AI.
See, I’m just a lonely independent consultant, and sometimes it’s just nice to have someone to talk to. It’s also kind of fun to take a query idea you have, and ask “someone” else to write it to see what they’d come up with.
ChatGPT (for reference, 4 and 4o) does a rather okay job sometimes. In fact, when I ask it to write a query, it usually comes up with a query that looks a lot like the ones that I have to fix when I’m working with clients.
If I poke and prod it enough about the things that it has done wrongly, it will agree with me and do things the right way, eventually. That is an improvement over your average T-SQL developer.
Your average T-SQL developer will spend a terrible amount of time trying to figure out ways to write queries incorrectly, even when you show them the right way to do something, often under the assumption that they’ve found the one time it’s okay to do it wrong.
For this post, I came up with a query idea, wrote a query that did what I wanted, and then asked the AI to write its own version.
It came pretty close in general, and even added in a little touch that I liked and hadn’t thought of.
Here’s the query I wrote, combined with the nice touch that ChatGPT added.
WITH
DuplicateTitles AS
(
SELECT
Title,
EarliestPostId = MIN(p.Id),
FirstPostDate = MIN(p.CreationDate),
LastPostDate = MAX(p.CreationDate),
DuplicatePostIds =
STRING_AGG
(CONVERT(varchar(MAX), p.Id), ', ')
WITHIN GROUP
(ORDER BY p.Id),
TotalDupeScore = SUM(p.Score),
DuplicateCount = COUNT_BIG(*) - 1
FROM dbo.Posts AS p
WHERE p.PostTypeId = 1
GROUP BY
p.Title
HAVING
COUNT_BIG(*) > 1
)
SELECT
dt.Title,
dt.FirstPostDate,
dt.LastPostDate,
dt.DuplicatePostIds,
dt.DuplicateCount,
TotalDupeScore =
dt.TotalDupeScore - p.Score
FROM DuplicateTitles dt
JOIN dbo.Posts p
ON dt.EarliestPostId = p.Id
AND p.PostTypeId = 1
ORDER BY
dt.DuplicateCount DESC,
TotalDupeScore DESC;
If you’re wondering what the nice touch is, it’s the - 1 in DuplicateCount = COUNT_BIG(*) - 1, and I totally didn’t think of doing that, even though it makes total sense.
So, good job there.
To start, I added this index. Some of these columns could definitely be moved to the includes, but I wanted to see how having as many of the aggregation columns in the key of the index would help with sorting that data.
Those datums? These datas? I think one of those is right, probably.
CREATE INDEX
p
ON dbo.Posts
(PostTypeId, Title, CreationDate, Score)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
It leads with PostTypeId, since that’s the only column we’re filtering on to find questions, which are the only things that can have titles.
But SQL Server’s cost-based optimizer makes a very odd choice here. Let’s look at that there query plan.
There’s one expected Filter in the query plan, for the COUNT_BIG(*) > 1 predicate, which makes absolute sense. We don’t know what the count will be ahead of time, so we have to calculate and filter it on the fly.
The one that is entirely unexpected is for PostTypeId = 1, because WE HAVE AN INDEX THAT LEADS WITH POSTTYPEID.
¿Por que las hamburguesas, SQL Server?
I’ve written in the past about, quite literally not figuratively, how Max Data Type Columns And Predicates Aren’t SARGable.
My first thought was that that, since we’re doing this: (CONVERT(varchar(MAX), p.Id), ', '), that the compute scalar right before the filter was preventing the predicate on PostTypeId from being pushed into an index seek.
Keep in mind that this is quite often necessary when using STRING_AGG, because the implementation is pretty half-assed even by Microsoft standards. And unfortunately, the summer intern who worked on it has since moved on to be a Senior Vice President elsewhere in the organization.
At first I experimented with using smaller byte lengths in the convert. And yeah, somewhere in the 500-600 range, the plan would change to an index seek. But this wasn’t reliable. Different stats samplings and compatibility levels would leave me with different plans (switching between a seek and a scan). The only thing that worked reliably is using a FORCESEEK hint to override the optimizer’s mishandling.
This changes the plan to something quite agreeable, that no longer takes 12 seconds.
So why the decision to use the first plan, au naturale, instead of the plan that took me forcing things to seek?
The faster plan was estimated to cost nearly 10x the query bucks to execute. Go figure.
For anyone who needed a reminder:
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Thanks for watching!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Thanks for watching!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Wanna catch us at PASS Data Summit this November? Get your tickets here!
Thanks for watching!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Or at least at some point, back when I gave all my precious blog blood somewhere else, I used to quite enjoy writing the release notes for the First Responder Kit. It was fun, and there were a lot of contributors to credit.
This most recent release had a note in it that got me taking a stroll down memory lane.
Deprecating sp_BlitzInMemoryOLTP, sp_BlitzQueryStore, and sp_AllNightLog
sp_BlitzQueryStore was originally written by Erik Darling when he worked here. He’s moved on to start his own excellent company, plus his own sp_QuickieStore. You should be using that instead.
sp_BlitzInMemoryOLTP was always kinda distributed as a courtesy – the real home for it is in KTaranov’s Github repository, and you can still find it there. It hasn’t been updated in over 6 years, and I’ve never seen anyone using it, so I’m removing it to streamline support issues.
sp_AllNightLog was a ton of fun when we built it several years ago, but it’s consistently had a problem. Companies start using it, then decide they want to build something even more ambitious, typically a C# service with robust error handling and scheduling. sp_AllNightLog isn’t the kind of thing I want to encourage beginners to use – it’s complex.
I didn’t have much to do with sp_BlitzInMemoryOLTP. I’m still not entirely sure what it does. All I know is that In-Memory was the hottest frog in the pan for exactly 14.9 minutes.
But I have some quite fond memories of building sp_BlitzQueryStore, and sp_AllNightLog.
I didn’t write every bit of code in either one, but I definitely started work on and them pitched in quite a bit on both. Other contributors deserve whatever credit they’re publicly willing to take.
If you’ll permit a fella with a lot more grey on his face than there was when these two procedures were first F5 birthed into the world to reminisce a bit, I would like to eulogize them here.
I had been working on sp_BlitzCache just about full time for a couple years, since Jeremiah (who wrote it originally) had decided to embark on a professorial career. When Query Store got announced, I knew I wanted to write something for it.
After all, this seemed like a no-brainer for folks on SQL Server 2016 to adopt. I just had no concept of what I wanted to do, until one day…
I had just gotten off the phone with the worst credit card company in the world, because someone had purchased ONE-HUNDRED $99 Play Station gift cards from a Russian IP address with a .ru email, and they told me that I would need to fill out 100 dispute PDFs to dispute each charge separately.
I forget where I was walking home from, but I was in the company Slack, and I had a message from BrentO asking how I wanted to approach it, and I felt like I had to make something good up on the spot. It turned out to be: I want to find all of the worst metric spikes, and grab the queries that ran during them. So it would look for the highest CPU, reads, writes, memory, tempdb, etc. and look for the queries responsible for them. And since we have all this groovy historical data, I wanted to show which queries were sensitive to parameter sensitivity by looking for wild swings in those metrics.
In theory, this was a great idea. In practice, those queries were god awful slow. It wasn’t all my fault, of course; I can’t take full credit. I see a lot of scripts (including queries from the SSMS GUI) that hit Query Store which are equally as God awful slow.
Perhaps ironically, some of the absolute slowest points in any Query Store query are the ones that hit the “in memory” tables.
At any rate, SQL Server 2016 adoption was fairly slow, and Query Store adoption was even slower. It was even hard to recommend turning it on at first because of all the bugs and issues that were cropping up and getting fixed in CUs (and even then, Service Packs). SQL Server 2017 didn’t help things at all, and I was out on my own in the world by the time SQL Server 2019 got released.
So poor ol’ sp_BlitzQueryStore languished a bit. Of course, as I added checks and gizmos to sp_BlitzCache, I’d also add them to sp_BlitzQueryStore, but… It just wasn’t the same every day utility belt tool for me.
When I sort of lost faith in the whole thing was sometime in 2018 when I tweaked a query in sp_BlitzQueryStore to try to speed things up, and it made my local SQL instance stack dump, and I had to manually restart it. If that happened with a client, hoo boy!
But here’s to you, sp_BlitzQueryStore! It’s how I first started learning the Query Store DMVs, how they related, and what data was in them.
You’re like that long-term relationship that ends before you meet the person you end up marrying.
This made me feel cool, because:
Let me clarify point 2 a bit, because I’m not one of those “SQL isn’t coding” people. Most things that I write only work with data. This worked with REAL LIVE FILES. Writing them. Copying them. Restoring them. Across two different servers. Where neither one knew the other existed.
Wild. WILD!
I’m not sure if I’m allowed to say the name of the company that wanted it, but they were based in Chicago, so I ended up flying out there a couple times to work on it along with Brent.
That kind of stuff had never happened to me before, and has only happened a couple times since.
I learned some hard lessons from this one:
It was also when I learned that you can’t add triggers to “system” tables, like restorehistory, in the dbo schema, in msdb. If we could have done that, a few things would have been way easier.
Of course, my fondest memory of this one was when it finally worked. I remember hitting a bazillion errors and issues and debugging stuff for ages. And then one magical day, the Agent jobs started up, and it was all green.
I was far from alone in working on it; I don’t want it to sound like THIS ONE’S ALL ME. There were a group of 3-4 people who put in work writing and testing things.
That was kind of the nicest thing about it — real collaboration with real people in real life — not just pushing changes around the internet and asking people to test them.
While it is a bit sad to see them go, I totally understand why they had to. It’s difficult to be responsible for large code repos that you don’t use a lot, and have become unfamiliar with because you don’t use them regularly.
I’m a bit surprised that sp_BlitzBackups didn’t also end up in the deprecation pile. It hasn’t had an issue opened since 2019, or any commits aside from version bumps. But maybe it’s just that well-written! If that ever does get deprecated, I have exactly one funny memory of the writing process, and it probably won’t get a blog post. I can spare you the drama of “it runs really slow when there are hundreds of databases” and “how slow?” and “like a minute” and “why do you need to run it every 15 seconds anyway?”.
Perhaps the most surprising thing about the years since 2016 is that not a single third party monitoring tool has embraced Query Store data in their performance diagnostics. Hopefully someday someone tells them about it, I guess?
But hey, that’s enough about that! Go get the most recent release of the First Responder Kit because it has a bunch of new and improved in it. Enjoy your shinies while they last.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Thanks for watching!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I often see clients using forced plans or plan guides (yes, even still, to this day) to deal with various SQL Server performance problems with plans changing.
There’s usually an execution plan or two floating around that seems to be a good general idea for a given query, and a couple weird high-end and low-end outliers for very specific populations of values.
This is especially common in third party vendor environments where code and/or index changes may not be allowed without the okay from the high priest of tech support who only answers questions when their celestial craft passes near Earth every 27 years.
Of course, forced plans and plan guides can both fail. You may also run into a “morally equivalent plan” in Query Store that looks quite morally ambiguous.
Recently while working with a client, we came across just such a scenario. And of course, of the many reasons why a forced plan might fail, this one was just a… general failure.
The fix we came up with was to track down the compile values for that nice middle ground plan, and use OPTIMIZE FOR to push that plan shape into reliably reality.
Here’s a close enough approximation to what we did, with a good-enough demo. Trying to get a more realistic one was hard without a much more complicated schema, which the Stack Overflow is not.
An index!
CREATE INDEX
p
ON dbo.Posts
(OwnerUserId)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
And a procedure!
CREATE OR ALTER PROCEDURE
dbo.OptimizeForStuff
(
@ParentId integer = NULL,
@PostTypeId integer = NULL,
@OwnerUserId integer = NULL
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
SELECT TOP (1000)
p.*
FROM dbo.Posts AS p
WHERE (p.ParentId = @ParentId OR @ParentId IS NULL)
AND (p.PostTypeId = @PostTypeId OR @PostTypeId IS NULL)
AND (p.OwnerUserId = @OwnerUserId OR @OwnerUserId IS NULL)
ORDER BY
p.Score DESC,
p.Id DESC;
END;
All good so far, even if it does have an air of laziness.
The problem was that when the query executed something like this:
EXEC dbo.OptimizeForStuff
@OwnerUserId = 22656,
@ParentId = NULL,
@PostTypeId = 2;
It got a good-enough fast plan:
But when the query executed in almost any other way:
EXEC dbo.OptimizeForStuff
@OwnerUserId = 8,
@ParentId = 0,
@PostTypeId = 1;
EXEC dbo.OptimizeForStuff
@OwnerUserId = 1349,
@ParentId = 184618,
@PostTypeId = 2;
It got this sort of lousy plan.
When stranger executions came along, things got way worse!
EXEC dbo.OptimizeForStuff
@OwnerUserId = NULL,
@ParentId = 0,
@PostTypeId = 1;
EXEC dbo.OptimizeForStuff
@OwnerUserId = NULL,
@ParentId = 184618,
@PostTypeId = 2;
We need to avoid all of this.
Here’s what we did (again, round about) to make sure we got the generally good plan across the board, without failures!
CREATE OR ALTER PROCEDURE
dbo.OptimizeForStuff
(
@ParentId integer = NULL,
@PostTypeId integer = NULL,
@OwnerUserId integer = NULL
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
SELECT TOP (1000)
p.*
FROM dbo.Posts AS p
WHERE (p.ParentId = @ParentId OR @ParentId IS NULL)
AND (p.PostTypeId = @PostTypeId OR @PostTypeId IS NULL)
AND (p.OwnerUserId = @OwnerUserId OR @OwnerUserId IS NULL)
ORDER BY
p.Score DESC,
p.Id DESC
OPTION
(
OPTIMIZE FOR
(
@OwnerUserId = 22656,
@ParentId = 0,
@PostTypeId = 2
)
);
END;
Which gets us the original fast plan that I showed you, plus faster plans for all the other executions.
For example:
EXEC dbo.OptimizeForStuff
@OwnerUserId = 8,
@ParentId = 0,
@PostTypeId = 1;
EXEC dbo.OptimizeForStuff
@OwnerUserId = 1349,
@ParentId = 184618,
@PostTypeId = 2;
Go from 1.5 seconds to ~300ms:
And the two outlier queries improve quite a bit as well (though neither one is exactly great, admittedly).
EXEC dbo.OptimizeForStuff
@OwnerUserId = NULL,
@ParentId = 0,
@PostTypeId = 1;
EXEC dbo.OptimizeForStuff
@OwnerUserId = NULL,
@ParentId = 184618,
@PostTypeId = 2;
In all cases, the plan is generally better and faster, and sharing the plan across (though imperfect for the outliers) tamped down the extreme performance issues that were there before with attempts at forced plans.
While I’m no great fan of OPTIMIZE FOR UNKNOWN, using a specific value can act like a less faulty version of plan forcing.
You shouldn’t pull this out every time, because it is a bit of duct tape to keep a sinking ship above water, but in oddball cases, it can be a quick and rather painless fix.
At some point, better solutions should be explored and implemented, but emergencies don’t generally allow for the greatest care to be taken
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Thanks for watching!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I will leave the finer points of the problems with MERGE statements to the Michaels and Aarons of the SQL Server world.
This is just a… But why? post about them, because I’m honestly a bit puzzled by this missing implementation detail.
To get us to the point, I’m going to use a code snippet (with embellishments) from Aaron’s post here.
We’ll be starting with this table and trigger from the linked post, with a couple small tweaks to satisfy my OCD:
CREATE TABLE
dbo.MyTable
(
id integer
);
INSERT
dbo.MyTable
VALUES
(1),
(4);
CREATE OR ALTER TRIGGER
dbo.MyTable_All
ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
IF ROWCOUNT_BIG() = 0 RETURN;
IF TRIGGER_NESTLEVEL() > 1 RETURN;
PRINT 'Executing trigger. Rows affected: ' + RTRIM(@@ROWCOUNT);
IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
BEGIN
PRINT ' I am an insert...';
END;
IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
BEGIN
PRINT ' I am an update...';
END;
IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
BEGIN
PRINT ' I am a delete...';
END;
END;
After all, one of the best ways to make sure you get code right is to copy and paste it from the internet.
As much as we all love to dunk on MERGE, like cursors, heaps, and UDFs of various types, they did give you some neat options with the OUTPUT clause, like the $action column, and the ability to get columns from other tables involved in the query. You can’t do that with a normal insert, update, or delete when using the OUTPUT clause, though I think it would be cool if we could.
Working a bit with the code linked above, here’s an expansion on it showing the additional OUTPUT capability, but this is also where my annoyance begins.
BEGIN TRANSACTION
DECLARE
@t table
(
action varchar(6),
i_id integer,
d_id integer,
s_word varchar(5)
);
SELECT
mt.*
FROM dbo.MyTable AS mt;
MERGE
dbo.MyTable WITH (HOLDLOCK) AS Target
USING
(
VALUES
(1, 'one'),
(2, 'two'),
(3, 'three')
) AS Source (id, word)
ON Target.id = Source.id
WHEN MATCHED
THEN UPDATE
SET Target.id = Source.id
WHEN NOT MATCHED
THEN INSERT
(id)
VALUES
(Source.id)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT
$action,
Inserted.id,
Deleted.id,
Source.word
INTO @t
(
action,
i_id,
d_id,
s_word
);
SELECT
t.*
FROM @t AS t;
SELECT
mt.*
FROM dbo.MyTable AS mt;
ROLLBACK TRANSACTION;
You likely can’t guess what I’m sore about just looking at this, because this isn’t what annoys me.
This is all fine, and rather a nice showing of capabilities for an oft-maligned (by myself included) bit of syntax.
The problem is really in the trigger.
If you work with trigger code enough, you’ll get used to seeing:
The problem is that in any trigger, the $action column is not directly exposed for use to determine the action of a MERGE statement.
Sure, you can add a column to a table to track it, or some other hacky workaround, but I consider it a quite poor design choice to not have the $action column as a part of the Inserted and Deleted virtual tables.
Having it there would also benefit generic modifications that are captured by triggers in some manner to make the absolute type of modification quite clear to query writers.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.