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:
cherry bomb
When did this get added? I have no idea.
How far was it back ported? I have no idea.
I could look on VMs with older versions of SQL Server, but it’s dinner time. Or as they call it in Saskatchewan, “supper”.
Thanks for reading!
Going Further
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.
Since Kevin is often kind enough to link to my posts via his Curated SQL feed/aggregator, I figured I’d add to the SEO pyramid scheme this month.
As part of my consulting services, I often help clients decide if they could keep a full time database person (there are too many potential titles to list, here) busy for 40 hours a week for the foreseeable future.
If the Magic 8-Ball comes back with a Yes, I’ll also help them write or review their job posting, and screen candidates. If a resume makes it past various detectors and doesn’t end up in the randomly generated unlucky pile, I’ll interview them.
Me. Personally.
Not in-person, though.
There’s not enough armed security in the world for me to get that close to HR.
The Production DBA Question
If someone is going to be in charge of production DBA tasks, I’ll of course ask questions about experience with whatever model is in use, or is to be implemented by the company.
So like, if the company has Availability Groups, or wants Availability Groups, we’ll talk about those.
For flavor, I’ll also ask them why anyone would be so insane as to not just use a Failover Cluster with Log Shipping.
But the real question and answer that tells me if someone knows their business is this: When you set up backups, how often do you take log backups?
If anyone says “every 15 minutes” without any further clarification or qualification, they immediately go into the “No” pile.
See, 15 minute log backups are a meme in the same Pantheon as 5% and 30% for index fragmentation. Neither answer shows any deeper understanding of what exactly they’re doing.
Log backup frequency is a business requirement based on RPO goals (Recovery Point Objective). If your RPO goal is less than 15 minutes of data loss, 15 minute log backups don’t meet that goal.
And anyone who says that Availability Groups help meet RPO goals gets their resume burned in front of them.
The Developer DBA Question
For anyone responsible for performance tuning, I need to make sure that they’re staying current with their learning and techniques.
I’ll show a lot of pictures of queries and query plans, ask about various anti-patterns and tuning techniques, but my absolute favorite is to show them pictures of query plans.
Actual execution plans, as it were.
fine mess
Why is this important? Because at least half of the people I show this to will totally ignore the operator times and start talking about costs.
If you’re looking at a plan where this much information is available, and all you can dredge up to troubleshoot things are estimated costs, I’ll probably mail you a copy of this.
And I’m not kidding here — it shows a complete lack of attention and growth — what’s the point of calling yourself a performance tuning expert if your expertise peaked in 2008?
There Are Good People Out There
They may not spend every waking moment writing, recording, speaking, or chasing MVP status.
And that’s fine — I don’t care if you partake in that stuff or not — there are plenty of very smart people out there who don’t file quarterly paperwork or schedule posts months in advance.
What I care about is that you aren’t just an automaton seeking a new place to go through the motions in.
Thanks for reading!
Going Further
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.
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.
Why Performance Tuners Need To Use The Right Type Of Join In SQL Server
Thanks for watching!
Going Further
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.
User Experience Under Different Isolation Levels In SQL Server
Thanks for watching!
Going Further
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.
Loops, Transactions, and Transaction Log Writes In SQL Server
Thanks for watching!
Going Further
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.
ED: I moved up this post’s publication date after Mr. O posted this question. So, Dear Brent, if you’re reading this, you can consider it my humble submission as an answer.
It’s really not up my alley. I love performance tuning SQL Server, but occasionally things like this come up.
Sort of recently, a client really wanted a way to figure out if support staff was manipulating data in a way that they shouldn’t have. Straight away: this method will not track if someone is inserting data, but inserting data wasn’t the problem. Data changing or disappearing was.
The upside of this solution is that not only will it detect who made the change, but also what data was updated and deleted.
It’s sort of like auditing and change data capture or change tracking rolled into one, but without all the pesky stuff that comes along with auditing, change tracking, or change data capture (though change data capture is probably the least guilty of all the parties).
Okay, so here are the steps to follow. I’m creating a table from scratch, but you can add all of these columns to an existing table to get things working too.
Robby Tables
First, we create a history table. We need to do this first because there will be computed columns in the user-facing tables.
/*
Create a history table first
*/
CREATE TABLE
dbo.things_history
(
thing_id int NOT NULL,
first_thing nvarchar(100) NOT NULL,
original_modifier sysname NOT NULL,
/*original_modifier is a computed column below, but not computed here*/
current_modifier sysname NOT NULL,
/*current_modifier is a computed column below, but not computed here*/
valid_from datetime2 NOT NULL,
valid_to datetime2 NOT NULL,
INDEX c_things_history CLUSTERED COLUMNSTORE
);
I’m choosing to store the temporal data in a clustered columnstore index to keep it well-compressed and quick to query.
Next, we’ll create the user-facing table. Again, you’ll probably be altering an existing table to add the computed columns and system versioning columns needed to make this work.
/*Create the base table for the history table*/
CREATE TABLE
dbo.things
(
thing_id int
CONSTRAINT pk_thing_id PRIMARY KEY,
first_thing nvarchar(100) NOT NULL,
original_modifier AS /*a computed column, computed*/
ISNULL
(
CONVERT
(
sysname,
ORIGINAL_LOGIN()
),
N'?'
),
current_modifier AS /*a computed column, computed*/
ISNULL
(
CONVERT
(
sysname,
SUSER_SNAME()
),
N'?'
),
valid_from datetime2
GENERATED ALWAYS AS
ROW START HIDDEN NOT NULL,
valid_to datetime2
GENERATED ALWAYS AS
ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME
(
valid_from,
valid_to
)
)
WITH
(
SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = dbo.things_history,
HISTORY_RETENTION_PERIOD = 7 DAYS
)
);
A couple things to note: I’m adding the two computed columns as non-persisted, and I’m adding the system versioning columns as HIDDEN, so they don’t show up in user queries.
The WITH options at the end specify which table we want to use as the history table, and how long we want to keep data around for. You may adjust as necessary.
I’m tracking both the ORIGINAL_LOGIN() and the SUSER_SNAME() details in case anyone tries to change logins after connecting to cover their tracks.
Inserts Are Useless
Let’s stick a few rows in there to see how things look!
SELECT
table_name =
'dbo.things',
t.thing_id,
t.first_thing,
t.original_modifier,
t.current_modifier,
t.valid_from,
t.valid_to
FROM dbo.things AS t;
The results won’t make a lot of sense. Switching back and forth between the sa and ostress users, the original_modifier column will always say sa, and the current_modifier column will always show whichever login I’m currently using.
You can’t persist either of these columns, because the functions are non-deterministic. In this way, SQL Server is protecting you from yourself. Imagine maintaining those every time you run a different query. What a nightmare.
The bottom line here is that you get no useful information about inserts, nor do you get any useful information just by querying the user-facing table.
Updates And Deletes Are Useful
Keeping my current login as ostress, let’s run these queries:
UPDATE
t
SET
t.first_thing =
t.first_thing +
SPACE(1) +
t.first_thing
FROM things AS t
WHERE t.thing_id = 100;
UPDATE
t
SET
t.first_thing =
t.first_thing +
SPACE(3) +
t.first_thing
FROM things AS t
WHERE t.thing_id = 200;
DELETE
t
FROM dbo.things AS t
WHERE t.thing_id = 300;
DELETE
t
FROM dbo.things AS t
WHERE t.thing_id = 400;
Now, along with looking at the user-facing table, let’s look at the history table as well.
To show that the history table maintains the correct original and current modifier logins, I’m going to switch back to executing this as sa.
peekaboo i see you!
Alright, so here’s what we have now!
In the user-facing table, we see the six remaining rows (we deleted 300 and 400 up above), with the values in first_thing updated a bit.
Remember that the _modifier columns are totally useless here because they’re calculated on the fly every time
We also have the history table with some data in it finally, which shows the four rows that were modified as they existed before, along with the user as they logged in, and the user as the queries were executed.
This is what I would brand “fairly nifty”.
FAQ
Q. Will this work with my very specific login scenario?
A. I don’t know.
Q. Will this work with my very specific set of permissions?
A. I don’t know.
Q. But what about…
A. I don’t know.
I rolled this out for a fairly simple SQL Server on-prem setup with very little insanity as far as login schemes, permissions, etc.
You may find edge cases where this doesn’t work, or it may not even work for you from the outset because it doesn’t track inserts.
With sufficient testing and moxie (the intrinsic spiritual spark, not the sodie pop) you may be able to get it work under you spate of local factors that break the peace of my idyllic demo.
Thanks for reading!
Going Further
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.
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
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.
Since I first started reading about indexes, general wisdom has been to design the key of your indexes to support the most restrictive search predicates first.
I do think that it’s a good starting place, especially for beginners, to get acceptable query performance. The problem is that many databases end up designed with some very non-selective columns that are required for just about every query:
Soft deletes, where most rows are not deleted
Status columns, with only a handful of potential entries
Leaving the filtered index question out for the moment, I see many tables indexed with the “required” columns as the first key column, and then other (usually) more selective columns further along in the key. While this by itself isn’t necessarily a bad arrangement, I’ve seen many local factors lead to it contributing to bad performance across the board, with no one being quite sure how to fix it.
In this post, we’ll look at both an index change and a query change that can help you out in these situations.
Schema Stability
We’re going to start with two indexes, and one constraint.
CREATE INDEX
not_posts
ON dbo.Badges
(Name, UserId)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
CREATE INDEX
not_badges
ON dbo.Posts
(PostTypeId, OwnerUserId)
INCLUDE
(Score)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER TABLE
dbo.Posts
ADD CONSTRAINT
c_PostTypeId
CHECK
(
PostTypeId > 0
AND PostTypeId < 9
);
GO
The index and constraint on the Posts table are the most important. In this case, the PostTypeId column is going to play the role of our non-selective leading column that all queries “require” be filtered to some values.
You can think of it mentally like an account status, or payment status column. All queries need to find a particular type of “thing”, but what else the search is for is up to the whims and fancies of the developers.
A Reasonable Query?
Let’s say this is our starting query:
SELECT
DisplayName =
(
SELECT
u.DisplayName
FROM dbo.Users AS u
WHERE u.Id = b.UserId
),
ScoreSum =
SUM(p.Score)
FROM dbo.Badges AS b
CROSS APPLY
(
SELECT
p.Score,
n =
ROW_NUMBER() OVER
(
ORDER BY
p.Score DESC
)
FROM dbo.Posts AS p
WHERE p.OwnerUserId = b.UserId
AND p.PostTypeId < 3
) AS p
WHERE p.n = 0
AND b.Name IN (N'Popular Question')
GROUP BY
b.UserId;
Focusing in on the CROSS APPLY section where the Posts table is queried, our developer has chosen to look for PostTypeIds 1 and 2 with an inequality predicate. Doing so yields the following plan, featuring an Eager Index Spool as the villain.
i came to drop crumbs
SQL Server decided to scan our initial index and create a new one on the fly, putting the OwnerUserId column first, and the Score column second in the key of the index. That’s the reverse of what we did.
Leaving aside all the icky internals of Eager Index Spools, one can visually account for about 20 full seconds of duration spent on the effort.
Query Hints To The Rescue?
I’ve often found that SQL Server’s query optimizer is just out to lunch when it chooses to build an Eager Index Spool, but in this case it was the right choice.
If we change the query slightly to use a hint (FROM dbo.Posts AS p WITH(FORCESEEK)) we can see what happens when we use our index the way Codd intended.
It is unpleasant. I allowed the query to execute for an hour before killing it, not wanting to run afoul of my laptop’s extended warranty.
The big problem of course is that for each “seek” into the index, we have to read the majority of the rows across two boundaries (PostTypeId 1 and PostTypeId 2). We can see that using the estimated plan:
in this case, < 3 is not a heart.
Because our seek crosses range boundaries, the predicate on OwnerUserId can’t be applied as an additional Seek predicate. We’re left applying it as a residual predicate, once for PostTypeId 2, and once for PostTypeId 1.
The main problem is, of course, that those two ranges encompass quite a bit of data.
If you have many ill-performing queries, you may want to consider changing the order of key columns in your index to match what would have been spooled:
CREATE INDEX
not_badges_x
ON dbo.Posts
(OwnerUserId, PostTypeId)
INCLUDE
(Score)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
GO
This gets rid of the Eager Index Spool, and also the requirement for a FORCESEEK hint.
satisfaction
At this point, we may need to contend with the Lazy Table Spool in order to get across the finish line, but we may also consider getting a query from ~30 seconds down to ~4 seconds adequate.
Of course, you may just have one query suffering this malady, so let’s look at a query rewrite that also solves the issue.
Optimizer Inflexibility
SQL Server’s query optimizer, for all its decades of doctors and other geniuses working on it, heavily laden with intelligent query processing features, still lacks some basic capabilities.
With a value constraint on the table telling the optimizer that all data in the column falls between the number 1 and 8, it still can’t make quite a reasonable deduction: Less than 3 is the same thing as 1 and 2.
Why does it lack this sort of simple knowledge that could have saved us so much trouble? I don’t know. I don’t even know who to ask anymore.
But we can do it! Can’t we? Yes! We’re basically optimizer doctors, too.
With everything set back to the original two indexes and check constraint, we can rewrite the where clause from PostTypeId < 3 to PostTypeId IN(1, 2).
If we needed to take extraordinary measures, we could even use UNION ALL two query against the Posts table, with a single equality predicate for 1 and 2.
Doing this brings query performance to just about equivalent with the index change:
good and able
The main upside here is the ability for the optimizer to provide us a query plan where there are two individual seeks into the Posts table, one for PostTypeId 1, with an additional seek to match OwnerUserId, and then one additional seek for PostTypeId 2, with an additional seek to match OwnerUserId.
coveted
This isn’t always ideal, of course, but in this case it gets the job fairly well done.
Plan Examiner
Understanding execution plans is sometimes quite a difficult task, but learning what patterns to look for can save you a lot of standing about gawping at irrelevancies.
The more difficult challenge is often taking what you see in an execution plan, and knowing what options you have available to adjust them for better performance.
In some cases, it’s all about establishing better communication with the optimizer. In this post, I used a small range (less than 3) as an example. Many dear and constant readers might find the idea that someone would write that over a two value IN clause ridiculous, but I’ve seen it. I’ve also seen it in more reasonable cases for much larger ranges.
It’s good to understand that the optimizer doesn’t have infinite tricks available to interpret your query logic into the perfect plan. Today we saw that it was unable to change < 3 to = 1 OR = 2, and you can bet there are many more such reasonable simplifications that it can’t apply, either.
Anyway, good luck out there. If you need help with these things, the links in the below section can help you get it from me.
Thanks for reading!
Going Further
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.
Why Read Committed Queries Can Still Return Bad Results In SQL Server
Thanks for watching!
Going Further
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.
