ICYMI
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Video Summary
In this video, I delve into the exciting world of performance tuning and database management, sharing insights from recent experiences and upcoming changes. I discuss how new features like Windows Server 2019’s Perfmon, which redesigns the user interface to look more modern and interactive, are poised to revolutionize monitoring tools for SQL Server administrators. Additionally, I reflect on my recent speaking engagements at SQL Saturday Portland and the anticipation of returning to SQL Bits in February, where I’ll be presenting new material relevant to the latest database technologies and challenges.
Full Transcript
I apologize for being mildly late. I was trying to figure out how to get the thumbnail set up exactly how I wanted it. I have this strained mirror. I have fun with the mirror. It’s a telescoping thing. I can point it at all sorts of stuff. I have fun. Enjoying myself. The only thing I’m terrified of is that someone might see what’s on the wall behind my monitor. It’s largely pornographic posters. I have fun with the mirror. I have fun with the mirror. I have fun with the mirror. Just kidding. Just kidding. Just kidding. What do I do with that mirror? I don’t know. Nothing. You can hear me, right? This isn’t like one of those things where I’m talking and no one can hear me and everyone’s having a bad time. That’s going to happen. That’s going to kill myself.
That’s going to kill myself today. That’s going to be the end of it. The end of it. Sound is good. Wonderful. Wonderful. So who has questions? Someone better have questions. Maybe doing this during pass was a bad idea. I assume people who show up here are there. I would expect low attendance. All right. Someone.
I’m going to count to 100. I’m going to count to 100 in my head. If we don’t have any questions, I’m going to get a haircut. Because this is a bit much. Bum, bum, bum, bum, bum, bum, bum, bum, bum, bum, bum, bum.
All right. All right. Well. There are no questions.
There are no questions. There are no questions. Oh, there is a question.
How lucky. Varus says, I’m talking with people who want to use GUIDs instead of identity columns because they’re afraid of outages from either running out or having big int conversion errors. Why not just use a big int?
Here’s your identity column. There’s very little downside to that. It would take you until, I don’t know, like the heat, death, and rebirth of the universe to really run out of those. And if the argument has ever been that big ints are 8 bytes and ints are 4 bytes, well, GUIDs are, what, like 30-something?
I mean, what’s the point there? And then you have to do all sorts of weird juggling with your indexes. All right.
Because unless you’re generating sequential GUIDs, then we don’t want a clustered index on that GUID column. Even if it is sequential, if you restart, you might have some weird artifacts when the server comes back up. But if you’re generating random GUIDs, then it can be especially difficult on a clustered index.
So I would probably just say, how about this lovely big int? Big ints are wonderful. All ints should be big ints.
There should be no medium int. Big ints are nothing. Bites be damned. These sort of design questions are funny because people think GUIDs are magical.
And I guess for uniqueness, they are. But they make so many other things. Quite obnoxious.
Non-clustered primary keys on GUIDs. Clustered indexes on other columns, which usually end up being integers anyway. I don’t know.
It’s all weird to think about. Like, I wonder what keeps someone up at night thinking about these things to the point where they have to fall into the GUID trap. SQL Server 2019 is out, huh?
How about a hand for SQL Server 2019 where all of my demos except a few still work? Problem not solved, I guess, is the bottom line there. I don’t know.
It’s cool, though. It’ll be fun seeing how getting batch mode on the reg changes stuff for people. So, Farah says, and what if you’re trying to convince someone who’s afraid of running out of big ints?
How do you communicate this with very non-technical people? I mean, I guess I would say… So, like, the usual thing to do would be to…
What do you call it? What’s that word? Estimate.
I knew it was in there somewhere. Estimate the transactional activity of this application that you’re building. Say, how many applications do you… Or how many…
How many transactions do you expect a day in this application? And then divide that out and, like, see if you can figure out how long it would take. How many days it would take to run out of big ints.
And then, like, okay, if you were wrong by a factor of two, if we have twice as many, if we have four times as many, this is how long big ints will still last for. And I’m willing to bet, I’m willing to throw this out there, that perhaps by the time you approach running out of those big ints, we might have an even bigger int available.
Or we might be quantum computing where integers have no meaning or something. That’s a thing, right? Lee asks, from a fan of window functions.
Yes, from time to time, when they’re necessary. Performance of window functions usually comes down to having an adequate index on your partition by and order by elements. Usually.
At least for me, anyway. Some people can get away. Some people who do demos on AdventureWorks can get away with whatever indexes they want. Other people who have real databases or, you know, databases that are bigger than, like, 20 megs or something, they have different problems.
Yeah. Using that index, they’re wonderful. Otherwise, you better hope that… So if you really want to play with something cool, I don’t know what version of SQL Server you’re on, but if you really want to play with something cool with windowing functions, see what happens when you involve a columnstore index and you get a window aggregate.
Those are super fun to get. If you think windowing functions with the partition over by covering index is fast, wait until you get a window aggregate.
2017. So yeah, what I would do, if you’re on 2017, create a temp table with a clustered columnstore index on it, and then left join to that temp table on 1 equals 0, and you may see a window aggregate function show up, and when you do, you’ll wonder how you ever lived without them.
I think the thing to be careful with with window functions is that by default… Is this a trick to enable set-based operators? It’s an interesting question.
Batch mode operators is what it enables. Batch mode operators. But the thing to be really careful with with windowing functions is that by default, they will work on the range of rows rather than a set number of rows.
You have to set… You have to tell that windowing function to work on rows unbounded proceeding, yada, yada, versus range unbounded yada, yada, proceeding, because the range, when you work with ranges, you can get this crappy on-disc spool of data.
It can be very slow. Whereas with using the rows specification, you can… You get the in-memory spool or something like that.
So that’s another thing to be careful of with the old windowing functions. Watch out for that. It’s a big deal sometimes. Also, they act differently.
Ranges and rows. Much different. It’s always fun to revisit this stuff, because I don’t think about it terribly often.
Because it’s stuff that I’ve… It’s been settled in my head. For so long that I don’t think about it too much. It’s like what I’m going to order at a bar, because I just don’t go to bars that much anymore.
It doesn’t matter. What do you want? The drink. The drink you make. Yeah, look up the syntax for windowing functions. Make sure you understand the full difference.
I actually… Blah, blah, blah, blah, blah. Let’s see here. Window functions.
I bet that post is still up, because Brent seems to like my SEO. So I’ll stick that link in chat.
Forrest asks if I’ve seen much of a difference in performance due to underlying OS, e.g. server 2012 versus 2016. God, you know…
This is going to sound obtuse of me, but when I was a DBA, everything was mostly server 2008.
And then as a consultant, most servers seem to go from 2008 to 2016. I didn’t see a whole lot of people on server 2012.
So I’m not sure that I would be able to give you like a specific performance difference between the two. I’m sure there’s stuff.
I’m sure there’s improvements to Windows in the way that like CPU and memory and IO and everything is handled by the operating system. I’m sure that comes into things.
But gosh, I don’t think I’ve ever… I don’t think I’ve ever looked at someone’s performance problem and been like, ah, shucks. If only you were on server 2016, 19 or something.
That reminds me. I have to start downloading Windows Server 2019. You know what I’m really excited about? The new Perfmon. New Perfmon looks like a cool video game. It’s all like 3D and bejazzled and stuff.
It’s fantastic looking. I can’t wait. I can’t wait for that new Perfmon. Let’s see if I can find a link.
New Perfmon. Yeah, buddy. Yeah, buddy. Stick that link in chat. In case anyone’s interested in looking at the new Perfmon.
It looks so cool. It’s all like Power BI. There are like charts and graphs and stuff. I think you can search for like which object you want instead of having to like just like scroll through that absurd list.
It looks so good. I can’t wait for the new Perfmon. I might start using Perfmon. I’m so bad at using Perfmon.
Like when people are like, I have a problem with blah, blah, blah. I’m like, we’re not using Perfmon. Because A, I’m not good at it. B, I get really annoyed at how all the measurements can be like different scales. Like one being at 100 can mean one thing.
Another one being at 100 can mean another thing. And then none of it makes sense. I get annoyed at trying to figure out which collectors I need. I’ve just been really, really bad at Perfmon.
So I quit Perfmon. Until I saw that Perfmon. That new Perfmon. And now I’m excited about Perfmon again. I’m very excited about Perfmon.
I can’t wait for new Perfmon. It’s exciting times for Perfmon. Very excited.
It’s going to be a good time. Better or worse than PSS-Diag. You know, I’ve only ever run PSS-Diag when it’s been asked of me.
I’ve never, like, experimented with it as an application, like, on my own. And it seems like it’s, like, for some reason it just feels like it’s too late in the game for me to do that. Like, I’m not going to spin up PSS-Diag today and be like, ooh, look what I can do.
I feel like, you know. Like, those blog posts would have been great in 2008. Right?
Or, like, something like that. Now, if I’m like, look at this cool thing I can do with PSS-Diag. Someone’s like, yeah, but, you know, I have a monitoring tool. And it looks bad.
It’s just, it looks ugly. And I don’t know. Like, spits out a text file or something. And start, like, using, like, SQL Pal or SQL Nexus or whatever those things were that weren’t, like, the old troubleshooting tools.
Stuff that people use then. I don’t know. My dear, or rather, our dear friend Sean Gilardi seems to enjoy PSS-Diag.
So, maybe there is something. Maybe if someone starts blogging about PSS-Diag today, like, how do you use PSS-Diag? Damn it.
Maybe he’ll give it a shot. Maybe he’ll see what happens. I’ve heard various rumors that other consulting companies are big fans of using PSS-Diag as a data collector. So, I don’t know.
Maybe there’s hope for it. Maybe there’s hope for me. Maybe I’ll become the king of PSS-Diag, the prince of PSS-Diag. I don’t know.
We’ll see what happens. But I’m really excited about Perfmon. I think I might start using Perfmon, like, casually. The way it looks now.
Whoever redesigned that was a genius. A genius! Pure genius. I was out in Portland last Friday. Doing a pre-con for SQL Saturday Portland.
That was a lot of fun. I forget how many people were there. Good chunk of people. Good chunk.
Good chunk of happy learning people. I had someone join late, too. So, there was, like, one person who couldn’t get a seat in the room. They had to, like, use two chairs as a desk. So, I appreciate their tenacity in the matter.
Yeah. It’s a good time. Yeah. Total server performance. I like that material a lot.
I generally like the flow of the day. I like teaching people about when hardware sucks. And how queries look when hardware sucks. And then kind of getting into how queries can still be bad, even when hardware is good.
You know? I think it’s a good set of lessons for the day. I am working on… Yeah, you were there at SQL Bits.
Which I… Even more exciting. Holy cow. Simon tweeted earlier today that he has a contract for SQL Bits on his desk. I thought it wasn’t going to happen.
I was, like, nervous. Because February… Like, usually I know if I’m going to be at SQL Bits in, like, August. And this year there was nothing. I, like, emailed them. I was like, what’s going on?
I didn’t hear anything. And I was just like, man, is this not happening this year? Like, am I going to have the saddest year of my life in which I don’t go to SQL Bits? And then there was a tweet today about he has a contract for it. So, can’t wait.
Can’t wait. I would even be happy if it was in Manchester again. Like, if SQL Bits is in Manchester, I will be there. I will go to the Britain’s Protection. I will hang out.
That was a good bar. That was a fun place to get drunk. With Penal. So, yeah, I would happily do that. You know, it’s a fun session.
You know? I’m working on new stuff, of course. You know, I got to keep the material rolling. I got to keep it fresh. Especially with 2019 out, I got to make sure that I’m teaching people about not just, you know, stuff that can go wrong today, but stuff that still isn’t fixed in the future. You know?
It’s a… People keep saying that, like, you know, performance tuning is dead. I’m not going to need performance tutors anymore. I’m like, yeah, okay. Okay. I believe you.
I believe you. Me and the… Let’s see. I don’t know. I don’t have a count right now, but I would bet that I have had about 60 clients this year. And that’s not bad for a fella just starting out in his first year of consulting all by himself.
I would bet that performance tuning is not quite dead. Thoughts on accelerated database recovery. So accelerated database recovery is a feature that makes rollbacks very fast.
Rollbacks used to be very, very slow, single-threaded duty head operations. Now they are very fast. They are nearly instant because of something in the database called the persistent version store, which I hope will be used to get rid of spools and execution plans.
But that’s besides the point. But yeah, it looks cool so far. It looks like a fun time.
It’ll be interesting to see what that does to a few different things, like database sizes. It’ll be fun to see what it does to… What do you call it?
Like, when I played with it, cleanup was, for some reason, really slow. It took, like, 17 minutes to clean up 7 gigs or something like that. I was unhappy with that. Lee says, oh, boy.
Lee, yes. Have you ever had query store refuse to force a query? No error, and it says it’s forced, but it won’t use it. Yes.
Yes, I have. In fact, in my blog post today, I talked about how a query that I wrote with a hint and said, SQL Server use this version of the execution plan for all of these queries, and it refused to do it. Outright refused.
I would use a query, like a use plan, like if I set up a plan guide for it, it would use that. But it would not… Query store refused to do it.
I was very upset about that. Kendra Little… Actually, the link might be in the post today. But Kendra Little has a blog post about morally equivalent plans, which I find fascinating because… It isn’t in there.
Okay. So I’ll… Little… Little… Morally… And…
Uh… Uh… Uh… There we go.
There we go. Yes. Kendra is fantastic. So there’s a good link in there. Um…
Yeah, it’s… Uh… So I had… I had query store refuse to… So the problem that I was facing was I had an entity framework query that had, like, a whole bunch of left joins inside, like, a derived join. And then that derived join joined back to, like, a base table.
And it was, like… It was pretty… Pretty crazy what was going on inside there. And, um… The query had a 70 second compile time.
7-0. 70… 7-0 second compile time, meaning that query… It took that query a minute and 10 seconds to get an execution plan. And…
Uh… When it was done, it finished in, like, 600 milliseconds. And the… Since it was entity framework, there wasn’t, like, a lot of rewrites we could do. But I could use a, uh…
I could use a force order hint on the query. And that would get it to finish instantly. Because rather than spend a whole lot of time trying to rewrite join orders, we could…
Or I could tell the optimizer to just join the tables in the order that the query is written. And when I did that, it finished instantly. And when I tried to force that plan in Query Store, Query Store did not honor that plan.
It didn’t say it failed. It didn’t say, no, thank you. It just said, cool. I appreciate the advice. And it just kept on doing what it was doing. Taking 70 seconds to compile this plan.
So… Made a plan guide. Plan guide worked. Query finished instantly. Every single time after that. Happily ever after.
Oh. Suppose that’s why they paid me several of the bucks. Get query…
I get query optimization. I optimize query optimization. From 70 seconds to 0 seconds. It’s amazing. It’s amazing.
Right? That was probably one of the more fun problems that I’ve run into recently, too. You know, is… When you’re a consultant and you work with enough people, you kind of see… I would say, like, if you took a bingo card, you could probably win it once per client if you put, like, the most common issues on there.
It’s very, very rare that someone has, like, a new, exciting, dangerous problem. No, it’s… No, it’s…
I would imagine… So, like, query store is one of those funny things where, A, you have to have enough people on 2016 who, B, turn it on, and C, look at it, and then D, try to solve a problem with it, and E, have that problem be forcing a query plan to really get adequate feedback on if it’s going to be a new problem. And, like, the future is working well or not.
And… And… Not enough people get all the way to E. So, I think there’s probably some bugs and some issues in there that Microsoft has yet to, you know, fully flesh out just because not enough people are using it. It would be like, for example…
An example that’s very close to home for me. An example of the writing of SPBlitz Query Store. Query Store enabled where they would run…
They would run those things. Look at it. Boy. You know, there might be a million bugs in there that I don’t know about because not enough other people have looked at stuff. I mean, I’m mostly annoyed that the way the Query Store tables are designed, it makes it impossible to get reliably fast queries from them.
But that’s another matter. How much consulting do you do for Azure SQL DBs? Not a lot.
Not a lot. Done some. Done a few. Even… I even consulted for someone using Azure… What do you call them? Elastic pools where they scale up as query traffic gets more intense.
And they were using a whole lot of columnstore and still having some issues. But, yeah, not a lot. I don’t hear a lot from those people.
Which is, you know… You know, I won’t say that’s fine with me because I don’t want to, like, you know… Act like I don’t want to consult for people who are using Azure SQL DB. But it is rather more difficult to, you know, run some basic checks and queries against Azure SQL DB.
Because of how it might change and because cross-database queries and everything are awkward. And, you know, there’s a lot of stuff that’s different. There’s a lot of stuff that, like, you can’t do or change with Azure SQL DB that would make my recommendations useless.
So, I don’t know. I’m happy if people are happy. Managed instances, though.
I can’t wait until I start getting some people on those. Because those are cool, fun, exciting, sexy new… I don’t know.
It’s like new lingerie for servers. I’m excited about those. Very excited about those. I can’t wait to hear what Microsoft ends up rebranding managed instances as. Azure SQL data warehouse is now Synapse or something.
Sounds vaguely like a gaming keyboard. So, I’m not excited about that. Whatever.
Anyway, we’re about at the half hour mark. You lovely few have kept me company for long enough. You can stop desperately trying to think of questions to ask. I should be here next week.
Maybe, probably, hopefully. We’ll see. Thanks for showing up. And I will see you next time. Goodbye. Goodbye.
Goodbye. Goodbye. Goodbye. Goodbye. Goodbye. Goodbye. Bye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.