Building stuff
I sometimes really hate coming up with ideas, and much prefer someone to just say "I want this" so I can go out into the world and figure out how to do that. Occasionally though, I realize that I'm not going to get all that much direction. That's what happened with sp_BlitzQueryStore.
Mindless Self Promotion
I liked writing this blog post so much that I wrote an entire presentation on it. If you'd like to see it at GroupBy, click the link and vote.
Update: You can watch the video for this session here.
Phantom of the Nopera
Let's say you have a server. Let's go one step further and say SQL Server is running on it. Crazy, I know.
But hey, we specialize in crazy, here.
ONE TWO THREE FOUR FIVE SIX SEVEN WHO DO WE APPRECIATE?
No one.
Guess who
I'm going to give you three queries, and you have to guess what the output will be before you run them.
Here they are:
[crayon-6a379b843416d091933574/]
Go ahead
Feel free to sub in the compatibility level that your databases are actually in before running these on your own server.
Funny thing
The human body is pretty good at adaptation. When you get sick or injured, your body responds and (most of the time) heals itself up. When you stress your body in a certain way, over time, your body will feel less stressed by the same level of that activity. If you do 10 push-ups every day, after a week you'll barely notice you're doing them.
Staving off obsoletion
Brent has recently set aside "free" time to learn about stuff up in the cloud. I only put free in quotes because we do have to do something with a cloud theme, but we can pick whatever we want.
Naturally, I chose the hardest thing with the least practical use.
Facemaking
You know when you think you know something, and the obviousness of it makes you all the more confident that you know it?
That's usually the first sign that there's a giant gotcha waiting for you.
Because I always forget stuff like this
I have a confession to make, dear readers:
I am a horrible VM admin.
Before you get too excited
This isn't a dive into any of the new automated tuning features of SQL Server 2017.
I'm interested in them because Microsoft thinks they can put us out of business with them, but...
Oh, so you're the one While SQL Server 2016 adoption is still pretty low, and 2017 still quite isn't out yet, that's no reason to not invest a few days of dev time into a stored procedure that is only usable by the smallest portion of SQL Server users. Right? Look, someone around here has…
So many things happen in mid-Summer. Dreams. Death. Pit stains.
And FRK updates!
Is that a nice way of saying typo?
People will often tell you to clearly alias your tables, and they're right. It will make them more readable and understandable to whomever has to read your code next, puzzling over the 52 self joins and WHERE clause that starts off with 1 = 2. It can also help solve odd performance problems.
A commenter commented
That the "New AG Wizard" in SSMS 2017 had surfaced the Direct Seeding mode for AGs.
I was pretty psyched about this because I think it's a great feature addition to AGs that can solve for a pretty big hump that people run into when they create databases regularly.
I hate that you can do this
Here's the thing: I'm mostly writing this because I didn't know you could do it.
But it's cool, because it'll reinforce some other concepts, and I'll show you why you shouldn't do it.
The short story: if your view has a scalar user-defined function it it, any query that calls the view will go single-threaded, even if the query doesn't reference the scalar function. Now for the long story.
Quite often people will inherit and rely on views written back in the dark ages, before people were aware of the deleterious effects that scalar valued functions can have on performance.
I love Query Plans
No seriously, I love digging into query plans. Even writing XQuery to dig into them in sp_BlitzCache. I was both honored and horrified to take over development of it from Jeremiah.
It's a beast, but it's my kind of beast. At around 4500 lines of code, when you want to make a change, well, &^%$ happens sometimes.
It used to make sense
I think I've mentioned that the most fun part of new features is testing them with old ideas to see how they react.
It occurred to me that if Adaptive Joins didn't work with APPLY, I might cry.
If you're not aware of the performance problems scalar valued functions can (and often do) cause, well, uh... click here. We'll talk in a few days.
If you are, and you're worried about them crapping on Adaptive Joins, follow along.
But is it faster?
Now, I know. There are very few "always" things out there in SQL Server. This is also true for functions.
A lot of the time -- I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.
