This post is admittedly short, but since we’re short of First Party Solutions™ to gripe to Microsoft with… I need to save this stuff somewhere.
You get 12 tempdb files, even if your instance has 16, 24, 32, 40, 64, or 80 cores
There’s no way to turn on in-memory tempdb
death of auto tune
If your workload is tempdb-heavy, and you have a special configuration for it on-prem, you might have a tough time moving it to Managed Instance. Also consider that disk performance (even though tempdb is “local”) is garbage, and you can’t do anything about it.
I think the lack of in-memory tempdb is odd too, since many features are cloud-first. One may wonder aloud why a performance feature that’s a couple years old now still isn’t available in flagship business critical cloud offerings.
It was only recently (September 2021) that scalar UDF inlining became available up in Azure SQL DB and Managed Instance.
Who’s really on the cutting edge? All those stodgy holdouts hanging about in their earthly garb.
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 performance problems quickly.
Right off the bat, I want you to know that this is not a solution, and I’ll get to why in a minute. I’m writing this mainly because every once in a while I’ll try something different to get this working, and it always ends up disappointing.
I wish I had better news for you, here. Hell, I wish I had better news for me here. But alas we’re at the mercy of parameters.
And yeah, I know, recompile, recompile, recompile. All the live long day. But I’ve seen some weird stuff happen with that too under high concurrency.
So what’s the point? Let’s talk about that.
Dot Dot Dot
CREATE INDEX p1 ON dbo.Posts(OwnerUserId, CreationDate);
CREATE INDEX p2 ON dbo.Posts(Score, LastActivityDate);
We need some indexes. That’s a fact. I’m intentionally creating them in this way to show you that SQL Server can sometimes be smart about catch all queries.
And here’s the inline table valued function we’ll be working with:
CREATE OR ALTER FUNCTION
dbo.kitchen_sink
(
@OwnerUserId int,
@CreationDate datetime,
@Score int,
@LastActivityDate datetime
)
RETURNS table
AS
RETURN
SELECT
c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE
(p.OwnerUserId = @OwnerUserId OR @OwnerUserId IS NULL)
AND (p.CreationDate >= @CreationDate OR @CreationDate IS NULL)
AND (p.Score >= @Score OR @Score IS NULL)
AND (p.LastActivityDate >= @LastActivityDate OR @LastActivityDate IS NULL);
This pattern usually eats the optimizer alive, and there’s a lot of posts about using dynamic SQL to fix it.
But when we call this function with literal values, it does just fine.
SELECT
ks.c
FROM dbo.kitchen_sink(22656, '20130101', NULL, NULL) AS ks;
SELECT
ks.c
FROM dbo.kitchen_sink(NULL, NULL, 100, '20130101') AS ks;
SELECT
ks.c
FROM dbo.kitchen_sink(22656, NULL, NULL, '20130101') AS ks;
SELECT
ks.c
FROM dbo.kitchen_sink(NULL, '20131225', NULL, '20131225') AS ks;
SELECT
ks.c
FROM dbo.kitchen_sink(22656, NULL, NULL, '20131215') AS ks;
Das Plan
You can run those all yourself and look at the plans. I’m just gonna throw a couple of the more interesting examples in the post, though.
The first two queries do exactly what we’d hope to see.
sparkling
We use the right indexes, we get seeks. Cardinality estimation is about as reliable as ever with the “””””default””””” estimator in place 🙄
And at one point, we even get a really smart index intersection plan where the optimizer uses both of our nonclustered indexes.
units
Parameter Problem
The problem is that no one really makes database calls like that.
If you’re using an ORM, you could intentionally not parameterize your queries and get this to “work”, but there are downsides to that around the plan cache. Being honest, most plan caches are useless anyway.
Long Live Query Store, or something.
Most people have their catch all code parameterized, so the query looks like what’s in the function. I’m going to throw the function in a stored procedure now.
CREATE OR ALTER PROCEDURE
dbo.kitchen_wrapper
(
@OwnerUserId int,
@CreationDate datetime,
@Score int,
@LastActivityDate datetime
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
SELECT
ks.c
FROM dbo.kitchen_sink
(
@OwnerUserId,
@CreationDate,
@Score,
@LastActivityDate
) AS ks;
END;
If we execute the proc like this, everything goes to hell rather quickly.
The first execution uses the “right” index, but we lose our nice index seek into the p1 index.
barfbag
We also end up with Predicates on the Key Lookup, just in case they end up not being NULL. And boy, when they end up not being NULL, we end up with a really slow query.
me one too
We re-use the execution plan we saw before, because that’s how SQL Server works. But since we don’t filter any rows from p1 since those parameters are NULL now, we pass all 17 million rows to the key lookup to filter them there, but since it’s a Nested Loops Join, we do it… one row at a time.
Fun.
Floss Too Much
There’s no great fix for this, either. This is a problem we’re stuck with when we write queries this way without using dynamic SQL, or a recompile hint.
I’ve seen people try all sorts of things to “fix” this problem. Case expressions, ISNULL and COALESCE, magic values, and more. They all have this exact same issue.
And I know, recompile, recompile, recompile.
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 performance problems quickly.
This was true before COVID, too. You’d be hard-pressed to not find “nerd flu” trending around them, and they were gross for a lot of the same reasons that are being calling out now.
At the first SQL Saturday I ever went to, the janitorial staff had to tape signs to the bathroom wall asking people not to pee on the floor, and that was before lunch. For the record, my aim is impeccable. You just try me.
That SQL Saturday was also the first time I learned that you should always leave for lunch. Always.
Exposed finger food or not, the eating situation at conferences is historically horrible. You have one big room full of the smell of what’s essentially cafeteria food and stale coffee and the farts everyone has been saving up since breakfast.
Always avoid the food hall at conferences. Always.
Why Bother?
What I liked most about conferences was traveling to new places and socializing with people. Getting away from conferences with the dozen or so people I consider friends, and maybe some new ones I made along the way.
And always to get food somewhere that wasn’t in the fart hut. Always.
You couldn’t pay me to eat food 1000 other people had been breathing on, regardless of any demics: Pan or Epi or Aca. That’s foul, and I’m not anywhere close to having a germ phobia. My only phobia these days is getting blog comments.
Despite being a regular precon speaker, I never got rich off that. I’ve made good money — no complaints at all — but only at bigger conferences like SQLBits.
Played correctly, they were break-a-little-better-than-even scenarios, once you factor in travel and hotels and time away from client work and all that.
I don’t teach precons for the money, I teach because I love teaching.
And traveling. And seeing my friends. Always.
Personable
Now, look, I’ve got all my shots, and I don’t sweat wearing a mask when required. I do what I do in the hope that it will help things get back to how they were. I do not have strong political convictions.
In that way, if a conference wanted that stuff for entry, I’d be able to go, but… What it really comes down to is that I’m not likely to go to a conference that my friends aren’t going to. And I don’t know if just those two things are enough to get people going back.
As much as I love being followed into the bathroom by someone asking me how to set MAXDOP, I hate eating alone (drinking alone is fine, though).
Gotta have balance, here.
I suppose it depends on what the world looks like when the bigger conferences start up in person again. I’m sure some people will never go to another conference, and I totally understand that point of view.
Just like some people will never go back to working in an office. And I’ll never eat in the food hall at a conference.
I do hope that I can go back to teaching in person again soon, though. I miss it, and teaching online classes didn’t scratch the itch in the same way.
Plus, my wife really wants me the hell out of the house, and I want to see my friends again.
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 performance problems quickly.
Somewhat curious was that there’s stuff in dm_xe_map_values for them.
SELECT
map_value
FROM sys.dm_xe_map_values
WHERE
name LIKE '%forced_param_clause_skipped_reason%';
SELECT
map_value
FROM sys.dm_xe_map_values
WHERE
name LIKE '%forced_param_statement_ignored_reason%';
Unfortunately — and I’ve confirmed recently with support — these events don’t actually fire for anything.
They’re just empty shells, but at least there’s some interesting details in the DMVs about what mightwork someday, and the reasons that just might fire.
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 performance problems quickly.
I had a RABID FAN ask me an interesting question about a query. I can’t use theirs, but I can repro the question.
The question was: if using ISNULL in a where clause isn’t SARGable, how come I can use a FORCESEEK hint in this query?
Formatting and linking my own, of course.
The query looked something like this:
SELECT
c =
COUNT_BIG(*)
FROM dbo.Users AS u WITH(FORCESEEK)
WHERE ISNULL(u.Id, 0) = 22656;
GO
seekable!
What Gives?
The first thing you should notice is that the optimizer throws out ISNULL, here.
Why? Because the Id column isn’t NULL-able, and since ISNULL is Microsoft’s special non-ANSI baby, it can do this one special thing.
If we use COALESCE instead, we’ll get an error.
SELECT
c =
COUNT_BIG(*)
FROM dbo.Users AS u WITH(FORCESEEK)
WHERE COALESCE(u.Id, 0) = 22656;
GO
Msg 8622, Level 16, State 1, Line 8
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
And if we try to use ISNULL on a NULL-able column like Age, we’ll get the same error:
SELECT
c =
COUNT_BIG(*)
FROM dbo.Users AS u WITH(FORCESEEK)
WHERE ISNULL(u.Age, 0) = 22656;
GO
Coacase? Caselesce?
Under the covers, COALESCE is just a crappy band CASE expression.
Without the FORCESEEK hint, we can get the query to actually run.
SELECT
c =
COUNT_BIG(*)
FROM dbo.Users AS u
WHERE COALESCE(u.Id, 0) = 22656;
GO
southa
And ISNULL is just… ISNULL.
SELECT
c =
COUNT_BIG(*)
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 0) = 22656;
GO
ribs
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 performance problems quickly.
they just don’t love it enough to spell check their copy
I mean, unless they’re making Azure Consumee. Michelin Star, I’m sure.
If you’re looking to move your current database to Azure, but you’re not sure:
Which offering to choose
Which machine size you’ll need
If your current workload can be tuned to reduce machine size
Which services your code is compatible with
That’s the kind of stuff I can help with as part of your migration efforts. If you’d like to find out more, you can get in touch with me here.
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 performance problems quickly.
I was helping a client troubleshoot a scalability issue recently, where during one point in their workload tempdb went out of control.
What was puzzling them was that nothing in the stored procedure did anything with temporary objects, cursors, or any of the other obvious things that go to el baño público. And the query plan itself was a pretty simple little loopy thing.
Despite the fact that the query was only passing around 7 rows, we were getting a one row estimate, and there was a spilling Sort operator.
Chance Of Cloudballs
There were a couple compounding issues here, of course, like that their database is hosted in the cloud.
It doesn’t matter which cloud you talk about, this is a problem they all have and I see people face constantly.
I/O sucks, up there.
It was a lot harder to get this to repro on my local hardware, because I have very nice drives.
Let’s take a look!
Bleak
If I run this query, it’ll spill a little bit. I have the MAXDOP 1 hint on there to artificially slow it down. Without that, the fast query and I/O make the contention harder to hit.
SELECT TOP (1000)
u.Id
FROM dbo.Users AS u
ORDER BY u.Reputation
OPTION(MAXDOP 1);
just like me
I stuck it in a stored procedure to make calling it from ostress a little bit easier.
No Return
After running for a bit, eventually we hit GAM contention:
tuesday
We can see all the classic signs of tempdb contention, arising from spills.
It’s worth noting that enabling in-memory tempdb seems to alleviate this issue for the most part. I’d show you a picture of that, but it’s not worth 1000 words.
Fixing The Spill
Normally, I wouldn’t bother fixing a spill of that size, but in this case it was worthwhile because it was harming concurrency.
The quick fix was to add a MIN_GRANT_PERCENT hint. The long-term fix is to add an index to support the Sort operation.
There are many reasons why a spill may occur. Poor estimates are quite common, but the reason for that poor estimate has about a billion potential root causes.
Even if your query gets enough memory, sometimes you may still see spills because certain operators had to share memory.
It’s a wild world.
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 performance problems quickly.
As a counterpart to yesterday’s post, I have a list of Great Ideas™ that sometimes it’s hard to get people on board with, for some reason.
Don’t get me wrong, some people can’t jump on this stuff fast enough — I’ve had people take “unscheduled maintenance” during engagements to flip the right switches — but other times there’s a hem and a haw and a whole lot of’em to go along with the plan.
Somehow people who have auto close and Priority Boost turned on and haven’t checked for corruption since 30 Rock went off the air want a full-bore fisking of every change and every assurance that no edge case exists that could ever cross their path.
Okay pal. You go on with your bad 2012 RTM self.
Lock Pages In Memory
“Please don’t pop my balloon animals.”
To say that this setting only lets SQL Server hang onto memory is a grand injustice. It also lets SQL Server use completely different APIs to access memory through Windows, including skipping over virtual memory space. That can be an awesome benefit for servers with gobs of memory.
What are people worried about, here? Usually some article they read about balloon drivers in 2011, or something.
But the same people aren’t afraid to set min server memory to max server memory, and then wonder why they have no plan cache.
I love this setting though, and if you can also get away with turning on trace flag 834, there are some nice additional benefits.
DBCC CHECKDB
“Well, our index maintenance job runs for 9 hours, so we don’t have time for this. Besides, won’t it cause a lot of blocking?”
Lord have mercy, the foot dragging on this. Part of your job as a DBA is to keep data safe and backed up. Running CHECKDB is part of that.
No DBA got fired over fragmented indexes. More than a few have for data going corrupt.
Granted, this can get a little more complicated for really big databases. Some people break it up into different steps, and other people offload the process.
Some third party backup tools from vendors like Quest and Red Gate allow you to automate processes like that, too. Full backup, restore to new server, run CHECKDB, tell us what happened.
How nice, you get a tested restore, too.
Query Store
“Won’t this catch my server on fire and leak PII to hackers?”
If you’re too cheap to spring for a proper monitoring tool, Query Store makes a pretty okay pseudo replacement. Especially in 2017 and up where you can track wait stats too, you can get some pretty good insights out of it.
Microsoft has also gotten pretty smart about better default settings for this thing, and in 2019 you have more knobs to set smarter standards for which plans get tracked in there.
It’d be really nice if you could choose to ignore queries, too, but you know. Can’t always get what you want, there.
I’d much rather look at Query Store than that unreliable old plan cache, too.
Read Committed Snapshot Isolation
“Why do I want tempdb to be full of old data?”
Remember yesterday? Me either. Nothing good happened, anyway. Do you remember what that row looked like before the update started? No?
Read Committed Snapshot Isolation does. And it wants you to, too. This setting solves so many dumb problems that people run headlong into because Microsoft committed to a garbage isolation level.
One complaint I hear all the time is that a particular application runs a lot better on Oracle with no other changes. This setting is usually the reason why: It’s not turned on.
Once you turn it on, reader/writer blocking and deadlocking goes away, and you don’t need to add a SSMS keyboard shortcut that inserts WITH NOLOCK.
Changing Indexes
“They’re fine the way they are, trust me. That burning smell is another server.”
Index tuning needs to be a process that starts with cleaning up indexes, and ends with adding in better ones.
What makes an index bad? When it’s unused, and/or duplicative.
What makes an index good? When it gets read from more than it’s written to, and it’s a usefully different way for queries to access data.
There are other index anti-patterns that are good to look for too, like lots of single key column indexes, but they usually get cleaned up when you start merging duplicates.
There’s a near fully eclipsed Venn Diagram of people who are worried about having too many indexes and people who have never dropped an index in their career.
Talk, Talk
These are the kinds of changes and processes people should be comfortable with making when they work with SQL Server.
Sure, there are a ton of others, but some of them have become part of the installer and get a little more leeway — parallelism settings, instant file initialization, tempdb etc. — I only wish that more of this stuff would follow suit.
One wonders quite loudly why setting MAXDOP made it into the installer, but setting Cost Threshold For Parallelism did not.
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 performance problems quickly.
Consulting gives you a lot of opportunities to talk to a lot of people and deal with interesting issues.
Recently it occurred to me that a lot of people seem to confer magic button status to a lot of things that always seem to be If-I-Could-Only-Do-This features that would solve all their problems, and similarly a Thing-That-Solved-One-Problem-Once turned into something that got used everywhere.
Go figure, right?
Let’s talk about some of them, so maybe I won’t have to talk this over with you someday, dear reader.
Partitioning
How this ended up being everyone’s top unexplored performance feature is beyond me. I always seem to hear that someone really wants to partition dbo.tblSomeBigTable because queries would be faster if they could eliminate partitions.
Maybe if you’re using clustered column store indexes it would, but for the rest of us, you’re no better off with a partitioned table than you are with a table that has decent indexing. In a lot of cases, partitioning can make things worse, or just more confusing.
Few people seem to consider the amount of work that goes into partitioning a really big table, either. It doesn’t matter if you want to do it in place, or use a batch process to copy data over.
Even fewer people talk about Partitioning for what it’s good for, which is managing partitions. Just make sure all those indexes are aligned.
Fill Factor
At this point, I’d expect everyone to understand why Flash and SSD storage is better than old spinning disks. Lack of moving parts, less pushing random I/O patterns, etc.
And yet, without a single page split being measured or compared, fill factor gets dropped down to 80 (or lower) just in case.
I call this Spinning Disk Mentality, and it hurts to see it out in the wild, especially when:
You’re on Standard Edition
You already have way more data than memory
You’re intentionally making data less compact
Your entire workload is stalled out on PAGEIOLATCH_XX waits
I truly appreciate the problem that lowering fill factor used to solve, but let’s join the CURRENT_CENTURY on this one.
Unless you have a good reason to add physical fragmentation to your indexes, how about we skip that?
In-Memory OLTP (Hekaton)
This is a hugely misunderstood feature. Everyone thinks it’s gonna make queries faster because tables will be in memory without reading the fine print.
If you have problems with throughput on very hot data, this might be a good solution for you.
If you’ve got a bunch of run-0f-the-mill queries that get blocked sometimes and performance generally stinks on, this isn’t really what you need to focus on.
I think the most common useful pattern I’ve seen for this feature is for “shock absorber” tables, where things like event betting, ticket sales, and online ordering all need to happen very quickly for a very small amount of data, and after the initial rush can be shuffled to regular disk-based tables.
If your table is already cached in memory when queries are hitting it, using this feature isn’t gonna make it any more in memory.
You’ve got other problems to solve.
Dirty Reads
Getting blocked sucks. It doesn’t matter if it’s in a database, at a bar, in traffic, or an artery. Everyone wants their reads instantly and they don’t wanna hear a darn word about it.
I’m not here to trample all over NOLOCK — I’ve defended people using it in the past — but I am here to ask you nicely to please reconsider dousing all your queries with it.
In many cases, READPASTis a better option, so your query can skip over locked rows rather than read a bunch of in-flight changes. This can be the wrong choice too, but it’s worth considering. It can be especially useful for modification queries that are just out looking for some work to do.
We’ll talk about my favorite option in tomorrow’s post.
Recompiling All The Things
Look, you wanna recompile a report or something, fine. I do, too. I love doing it, because then I don’t have one less random issue to think about.
Weirdly sniffed parameter? No mas, mon ami.
Magick.
Especially in cases where bigger code changes are hard/impossible, this can be sensible, like dealing with a million local variables.
Just be really careful using it everywhere, especially in code that executes a ton. You don’t wanna spend all your time constantly coming up with query plans any more than you wanna get parameter sniffed.
Plus, since Query Store captures plans with recompile hints, you can still keep track of performance over time. This can be a great way to figure out a parameter sniffing problem, too.
Gotcha
Basic understanding often is often just camouflage for complete confusion. Often, once you dig past the documentation marketing materials, you’ll find every feature has a whole lot of drawbacks, trade-offs, blind spots, and interoperability issues.
Databases being databases, often just getting your schema to a state where you can test new features is a heroic feat.
No wonder so many millions of hours have been spent trying to replace them.
Duplicating data for reporting, outside of moving it to a data warehouse where there’s some transformations involved, can be an expensive and perilous task.
Your options come down to a native solution like AGs, Replication, or Log Shipping. You can brew something up yourself that relies on native stuff too, like Change Data Capture, Change Tracking, Temporal Tables, or triggers.
Outside of that, you’re getting into the world of SSIS/ADF, other third party vendors, etc. to get data where it ought to go. That’s none of my business, and good luck to you.
As soon as you let people read that data, you have to fully license the SQL Server that it’s sitting on.
Alt Rock
I’ve talked to a few dozen people about this over the years, too. Most were shocked to learn about the licensing implications, thinking that having Software Assurance from Microsoft covered their needs.
The most frequent desire for offloading reads is real-time reporting, and the most frequently cited reason is that reporting queries put too much load on the current server hardware.
You can see where the conflict sits, here. People think they’re getting a free SQL Server to report off of, so they don’t need to up their current hardware and pay for it. Microsoft may be dumb, but it ain’t that dumb.
Once you get people past the licensing issues, tuning the current hardware becomes a more approachable subject.
Gestalt Block
Of course, the idea of tuning the reporting queries has occurred to most people, but the attempts at tuning are usually flailing attempts to solve problems that aren’t there.
Maybe this local variable will do a thing for parameter sniffing
NOLOCK is better for reports, anyway
Don’t forget to recompile, too
Add a DISTINCT, there’s too many rows
Throw up hands, rebuild indexes
While everything has its place, it’s rare that this combination will get you past all of your performance issues. Reporting queries are especially interesting to me, because of how few people embrace batch mode to any degree for them.
The cost of tuning queries is far cheaper than the cost of licensing a whole other server, especially for Enterprise Edition customers. If you’re on Standard Edition, you have many other problems. Many, many other problems, standing in the way of scaling and performance issues.
If this is the kind of thing you need help with, drop me a line. This is exactly the kind of thing I do.
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 performance problems quickly.
