With new features I really love kicking the tires to see how they work with existing features, and if they fix existing performance troubleshooting scenarios. One issue that I see frequently is with local variables. I'm not going to get into Cardinality Estimator math here, we're just going to look at Adaptive Join plan choice…
I don't like it unless it's brand new
When new features drop, not everyone has time to jump on top of them and start looking at stuff. That's what consultants with nothing better to do are for.
I've been excited about this feature since talking to The Honorable Joseph Q. Sack, Esq. about it at PASS last October. My pupils dilated like I just found the bottom of a bottle of Laphroaig 18.
Cheeky
If you thought that title sounded familiar, you sure were right.
That's called a classical reference.
This probably won't seem like a big deal soon
But I just got the optimizer to pick an Adaptive Join! It took a few tries to figure out what would cause some guesswork to happen, but here it is.
And here's the tool tip info!
Disclaimer
I found this totally by accident, and it even surprised me.
You can probably guess the TL;DR on this is yes, huh?
Dude, where's my table variable?
I was trying to come up with a demo for something totally different. Don't ask. Seriously. It's top secret.
When you see a yellow bang in an execution plan, and you hover over it to discover that SQL Server spilled out to TempDB, your first reaction is to wail in pain and rend your clothes.
After that, though, it seems like people always ask the same question:
We've all been there Staring at a simple query that has a three mile long execution plan. After the first time, you learn to always look to make sure there's not a secret view involved. After looking at query plans for modifications involving indexed views, I decided to look at some other fairly common table…
When you're tuning queries
It's sort of a relief when the first time you get your hands on it, you get the plan and there's a missing index request. Even if it's not a super high-value one, something in there is crying for help. Where there's smoke, there's a bingo parlor.
But does adding missing indexes from requests always make things better?
Your server is underpowered
That's an understatement. Your server sucks.
It has four cores in a single socket, data outpaces RAM by a country mile, the disks have whiskers, and the network card still has a phone jack in it.
I recently saw a server with trace flag 2453 configured. I hadn't come across this trace flag before, so I did a little research. Microsoft says it allows "a table variable to trigger recompile when enough number of rows are changed". This can lead to a more efficient execution plan. Trace flag 2453 is available in SP2 or greater for SQL Server 2012, CU3 or greater for SQL Server 2014 and RTM or greater for SQL Server 2016.
What does memory do?
In SQL Server, just about everything. We cache our data and execution plans in it (along with some other system stuff), it gets used as scratch space for temporary objects, and of course queries use it for certain operations, most notably sorting and hashing. And of course, now Hekaton comes along to eat up more of our RAM.
Using the publicly available Stack Overflow database (the 100GB 2016/03 version), here's an execution plan for an update statement:
It's a pretty simple query - it just scans the Posts table looking for any post with PostTypeId = 8, and then updates a single field for the first 250 rows it finds.
People often don't give this thought
Which is a shame, because I see people sticking fairly large amount of data into temp tables. On the rare occurrence that I do see them indexed, it's a nonclustered index on a column or two. The optimzer promptly ignores this index while you select 10 columns and join 10,000 rows to another temp table with another ignored nonclustered index on it.
Oh, THAT SARGability
I realize that not everyone has a Full Metal Pocket Protector, and that we can't all spend our days plunging the depths of query and index tuning to eek out every CPU cycle and I/O operation from our servers. I mean, I don't even do that. Most of the time I'm just happy to get the right result back!
Don't make me spill Table variables get a lot of bad press, and they deserve it. They are to query performance what the TSA is to air travel. No one's sure what they're doing, but they've been taking forever to do it. One particular thing that irks me about them (table variables, now) is that…
Early adopters: if you're rolling on SQL Server 2016 SP1, you may have noticed that when you click on query plans in sp_BlitzCache or sp_WhoIsActive, they sometimes render as ugly XML rather than the beautiful graphical showplan.
Here's a 3-minute video showing the bug:
Using our good old buddy the StackOverflow demo database, let's look at the Users table. I want to get the first 10 users in the table ordered by Id, the primary clustered key, which is an identity field.
Here's the actual execution plan:
I'm going to use the dbo.Users table in the StackOverflow demo database and run a pretty simple query: [crayon-6a6cf2de1da30308277609/] First, hit Control-L in SSMS and get the estimated execution plan. Here it is: Click on the plan, and hover your mouse over the various operators. You'll notice that almost all of the fields are prefixed…
Say you've got an application that has tons of OPTIMIZE FOR UNKNOWN hints in the T-SQL, and you're getting bad query plans.
We're going to use the same StackOverflow query (and the same index on Reputation) that I demoed in the post Why Is This Query Sometimes Fast and Sometimes Slow? This technique produces a query that will produce two different execution plans depending on the Reputation parameter.
You swear you didn't change anything, but all of a sudden the SQL Server is going doggone slow. What happened? Parameter sniffing might be the problem, and to explain it, let's see how it works. I'm going to use the StackOverflow database - particularly, the Users table that I demo in How to Think Like the…
