SQL Server Consulting, Education, and Training
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.
The nice folks at the New England SQL Server User Group (w|t) had me in town a week ago to present my workshop, The Foundations Of SQL Server Performance.
We had about 40 folks show up — which ain’t bad for a random Friday in May — including one attendee from Nigeria.
Not just like, originated in Nigeria. Like, flew from Nigeria for the workshop. That’s probably a new record for me, aside from PASS Precons where folks are already headed in from all corners.
Speaking of PASS Precons — me and Kendra are double teaming your Monday and Tuesday — have I mentioned that lately?
As a favor to some dear friends, here’s a promo code to get $125 off Bob’s Friday Precon, The Azure Workshop for SQL Professionals.
The precon takes places on October 4th, and the SQL Saturday is October 5th. You can register separately for the SQL Saturday only here.
If you want to catch Bob’s Friday workshop, use the QR code below, or register here and use the code PPP50 to get your discount.
Thanks for reading!
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.
You and Me might feel like the lower back end of a thing if we’re tuning a query that has other problems. Perhaps it’s running on one of those serverless servers with half a hyper-threaded core and 8kb of RAM, as an example.
When I’m working with clients, I often get put into odd situations that limit what I’m allowed to do to fix query performance. Sometimes code comes from an ORM or vendor binaries that can’t be changed, sometimes adding an index on a sizable table on standard edition in the middle of the day is just an impossibility, and of course other times things are just a spectacle du derrière that I’m allowed to do whatever I want. You can probably guess which one I like best.
This post is about the two other ones, where you’re stuck between derrière and ânesse.
For the duration of reading this, make the wild leap of faith that it takes to embrace the mindset that not everyone who works with SQL Server knows how to write good queries or design good indexes.
I know, I know. Leap with me, friends.
Here’s what we’re starting with:
SELECT TOP (10)
DisplayName =
(
SELECT
u.DisplayName
FROM dbo.Users AS u
WHERE u.Id = p.OwnerUserId
),
p.AcceptedAnswerId,
p.CreationDate,
p.LastActivityDate,
p.ParentId,
p.PostTypeId,
p.Score,
p.CommentCount,
VoteCount =
(
SELECT
COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE v.PostId = p.Id
)
FROM dbo.Posts AS p
ORDER BY
p.Score DESC;
And resulting plan:
We can surmise a few things from this plan:
Okay, so I’m kidding a bit on the last point. Sorta.
You might look at all this work that SQL Server is doing and wonder why: With no good, usable indexes, and such big tables, why in the overly-ambitious heck are we doing all these nested loop joins?
And the answer, my friend, is blowing in the row goal.
The TOP has introduced one here, and it has been applied across the all of the operators along the top of the plan.
Normally, a row goal is when the optimizer places a bet on it being very easy to locate a small number of rows and produces an execution plan based on those reduced costs.
In this case, it would be 10 rows in the Posts table that will match the Users table and the Votes table, but since these are joins of the left outer variety they can’t eliminate results from the Posts table.
The row goals do make for some terrible costing and plan choices here, though.
This all comes from cardinality estimation and costing and all the other good stuff that the optimizer does when you throw a query at it.
One way to show the power of TOPs is to increase and then decrease the row goal. For example, this (on my machine, at this very moment in time, given many local factors) will change the query plan entirely:
SELECT TOP (10)
p.*
FROM
(
SELECT TOP (26)
DisplayName =
(
SELECT
u.DisplayName
FROM dbo.Users AS u
WHERE u.Id = p.OwnerUserId
),
p.AcceptedAnswerId,
p.CreationDate,
p.LastActivityDate,
p.ParentId,
p.PostTypeId,
p.Score,
p.CommentCount,
VoteCount =
(
SELECT
COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE v.PostId = p.Id
)
FROM dbo.Posts AS p
ORDER BY
p.Score DESC
) AS p
ORDER BY
p.Score DESC;
You may need to toggle with the top a bit to see the change on your machine. The resulting plan looks a bit funny. You won’t normally see two TOPs nuzzling up like this.
But the end result is an improvement by a full minute and several seconds.
Because the inner TOP has a bigger row goal, the optimizer changes its mind about how much effort it will have to expend to fully satisfy it before clenching things down to satisfy the smaller TOP.
If you’re only allowed quick query rewrites, this can be a good way to get a more appropriate plan for the amount of work required to actually locate rows at runtime, when the optimizer is dreadfully wrong about things.
In this case, just indexing the Votes table is enough to buy us all the performance we need, but in my personal row goal for completeness, I’m going to add in two indexes:
CREATE INDEX
v
ON dbo.Votes
(PostId)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
CREATE INDEX
p
ON dbo.Posts
(Score DESC, OwnerUserId)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
Going back to the original query, we no longer need to play games with the optimizer and pitting TOPs against each other.
This is obviously much faster, if you’re in the enlightened and enviable position to create them.
Perhaps you are, but maybe not in the exact moment that you need to fix a performance problem.
In those cases, you may need to use rewrites to get temporary performance improvements until you’re able to.
Thanks for reading!
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.
This is a short post. I know you’ve been somewhat spoiled by longer posts and videos lately!
Just kidding, y’all don’t pay attention (with the exception of Kevin Feasel), so maybe this will be consumable enough for even the most squirrel brained amongst us.
A long time ago, I complained that wait stats logged by actual execution plans don’t show lock waits. That seemed like a pretty big deal, because if you’re running a query and wondering why sometimes it’s fast and sometimes it’s slow, that could be a pretty huge hint.
But now, if you run a query that experienced lock waits, you can see that in the details. Just highlight the root operator, hit F4 or right click and go to Properties, and look under the wait stats node, you’ll see this:
I could look on VMs with older versions of SQL Server, but it’s dinner time. Or as they call it in Saskatchewan, “supper”.
Thanks for reading!
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.
This month we have an invitation from Kevin Feasel.
Since Kevin is often kind enough to link to my posts via his Curated SQL feed/aggregator, I figured I’d add to the SEO pyramid scheme this month.
As part of my consulting services, I often help clients decide if they could keep a full time database person (there are too many potential titles to list, here) busy for 40 hours a week for the foreseeable future.
If the Magic 8-Ball comes back with a Yes, I’ll also help them write or review their job posting, and screen candidates. If a resume makes it past various detectors and doesn’t end up in the randomly generated unlucky pile, I’ll interview them.
Me. Personally.
Not in-person, though.
There’s not enough armed security in the world for me to get that close to HR.
If someone is going to be in charge of production DBA tasks, I’ll of course ask questions about experience with whatever model is in use, or is to be implemented by the company.
So like, if the company has Availability Groups, or wants Availability Groups, we’ll talk about those.
For flavor, I’ll also ask them why anyone would be so insane as to not just use a Failover Cluster with Log Shipping.
But the real question and answer that tells me if someone knows their business is this: When you set up backups, how often do you take log backups?
If anyone says “every 15 minutes” without any further clarification or qualification, they immediately go into the “No” pile.
See, 15 minute log backups are a meme in the same Pantheon as 5% and 30% for index fragmentation. Neither answer shows any deeper understanding of what exactly they’re doing.
Log backup frequency is a business requirement based on RPO goals (Recovery Point Objective). If your RPO goal is less than 15 minutes of data loss, 15 minute log backups don’t meet that goal.
And anyone who says that Availability Groups help meet RPO goals gets their resume burned in front of them.
For anyone responsible for performance tuning, I need to make sure that they’re staying current with their learning and techniques.
I’ll show a lot of pictures of queries and query plans, ask about various anti-patterns and tuning techniques, but my absolute favorite is to show them pictures of query plans.
Actual execution plans, as it were.
Why is this important? Because at least half of the people I show this to will totally ignore the operator times and start talking about costs.
If you’re looking at a plan where this much information is available, and all you can dredge up to troubleshoot things are estimated costs, I’ll probably mail you a copy of this.
And I’m not kidding here — it shows a complete lack of attention and growth — what’s the point of calling yourself a performance tuning expert if your expertise peaked in 2008?
They may not spend every waking moment writing, recording, speaking, or chasing MVP status.
And that’s fine — I don’t care if you partake in that stuff or not — there are plenty of very smart people out there who don’t file quarterly paperwork or schedule posts months in advance.
What I care about is that you aren’t just an automaton seeking a new place to go through the motions in.
Thanks for reading!
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.
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.