SQL Server Performance Office Hours Episode 33
Questions:
* What are your favorite sandwich condiments?
* First off big fan. Also, Why does lt=1 on a query with row_number window function perform so different than = 1. I find its a trick that almost forces sql server to compute all row number data before doing something else like pushing down the predicate of a join if the window query is used as a join “table”. I tried in (1,null) and gt0 and lt2 without the same results. Thanks!
* If you could change something about the world, not related to Perf Tuning SQL Server, what would it be?
* What do we need to be aware of when enabling snapshot isolation in a large production DB? Should we expect any downtime? Thanks!
* I enjoyed hearing you beg a viewer to switch from DPA to SQL Sentry. Can I hear you say more about what DPA does wrong? I haven’t used it in years.
To ask your questions, head over here.
Video Summary
In this video, I dive into some fascinating questions from the community during our office hours session. We tackled topics ranging from sandwich condiments to SQL Server performance tuning and database management best practices. Specifically, we explored why less than or equal to one in a query with row number window function behaves differently compared to equals one, delved into enabling snapshot isolation in large production databases without expecting downtime, and discussed the shortcomings of DPA as a monitoring tool compared to SQL Sentry. If you have any more questions or topics you’d like me to cover, feel free to ask during our next office hours session!
Full Transcript
Erik Darling here with Darling Data, and boy oh boy do we have an exciting, exciting day for you planned. More exciting than you could imagine because we are going to, we’re going to have an office hours. We’re going to answer some questions, five of them, this many, and we’re going to, we’re going to see what we got in the old mailbag today. Before we go and answer questions, if you would like to give me money in some meaningful way, there are all sorts of helpful ways to do this. I’ll do that down in the video description below. You can hire me for consulting, buy some training, become a contributing member of the channel. Of course you can, you know, do some stuff for free too, like ask me office hours questions. That doesn’t cost anything. That is a, that is a free service. And if you enjoy any or all of what I do here, you can of course like, subscribe, and the most important thing is tell a friend. That is how communities work. That is how communities get built, isn’t it? You spread the good word. All right. Uh, I will be leaving the house a little bit over the next couple few months. Uh, well, I guess that’s, well, it is September, isn’t it? I mean, oh boy, I gotta, I gotta start working on some stuff. Uh, we have Dallas, September 15th and 16th. Uh, Utrecht, Netherlands, October 1st and 2nd, and past, and, uh, Seattle, November 17th to the 21st. These are all, uh, past events put on by the nice folks at Redgate.
Uh, and, uh, I look forward to hopefully seeing you at, uh, any or all of them. Uh, well, we’re, we’re, we’re on the subject of any’s and all’s. Uh, but with that out of the way, let’s do our office hours dance here. You know, let’s, uh, zoom on in and get things properly situated so that, uh, everyone can focus on these things correctly. All right. Uh, what are your favorite sandwich condiments? Well, this brings up an interesting question is what you define as a condiment. Uh, of course, you know, you have your traditional spreadable condiments like mustard, mayonnaise, and, uh, stuff like that. Uh, of which I, I only, I really just only have a preference towards mustard. Um, of course, my favorite sandwich would be a roast beef and Swiss with, with mustard. That’s about as good as it’s going to get. Uh, then you have your spreadables. You have your relishes and your, your, your hots and other things like that. They all have their place. Uh, well-made muffaletta sandwich is probably one of, one of the better things in life. Um, you know, my, my personal favorite sandwich is, of course, the peanut butter and onion on a, on a nice, nice, uh, potato bread. That’s, that’s about as good as things get for me.
But, you know, uh, I think, I think mustard would probably take the top, top spot there. Mustard is probably number one for me. Uh, good, solid deli mustard. We’re, we’re good to go wherever we go. Wherever we go, there we are. All right. That’s, oh, well, that was, that was a line, not a square. Thank you, Zoomit, for betraying me once again. Uh, first off, big fan. Well, that blows. Ha ha ha. Uh, why does less than or equal to one on a query with row number window function perform so different than equals one?
I find it’s a trick that almost forces SQL Server to compute all row number data before doing something else like pushing down the predicate of a join if the window query is used as a join table. I tried in one null and greater than zero and less than two without the same results. Thanks. Well, uh, so I’m not sure, uh, why, uh, other attempts at this, uh, did not work, uh, in the same way. But, uh, I, I, I, so two things here. One, uh, you, you have earned, I think you have earned yourself a full, uh, video explanation. So I’ll, uh, I’ll crack one of those out tomorrow.
But, um, really what the, the thing is, is that, um, when you use, uh, less than or equal to one, uh, you prevent SQL servers optimizer from, uh, trans using an any aggregate transformation, uh, in the query plan, uh, where you, if you use, so there, um, Paul White has, so I did a video about this, um, some time ago and, uh, I forget the title of it, of course, because, you know, there are a lot there, there are somewhere over 600 of these things. Uh, so it’s hard to remember the names of them all, you know, I love my children, but there are too many.
Uh, but, uh, Paul White also has a good post on any aggregate transformations, uh, where he talks about, uh, ways that SQL Server can take a windowing function and under certain circumstances, uh, can transform, uh, the windowing function into, uh, an aggregation rather than, uh, doing the full, uh, like segment sequence projector window aggregate, uh, typical query plan for a windowing function. So that’s probably what’s happening, but I will, I will do, uh, a demonstrative video about this tomorrow. So you have that to look forward to.
I will postpone all my other important work and do that because I care deeply about you. And, uh, you know, as, as much as I do want people to buy my training, I cannot, uh, I cannot bear to just tell you to buy it and hope that you stumble upon the correct thing. Uh, if you could change something about the world, not related to perf tuning SQL Server, what would it be?
Oh, I mean, there’s some obvious stuff isn’t there. You get rid of cancer, you know, you get rid of egg salad, you know, you Thanos snap government employees. Uh, I don’t know.
Some other, I don’t know. I think, I think, I think that would, that would just about clear everything up for me. Yeah.
Um, yeah, that about does it. I don’t know. Protein should be free. Socialized protein, I guess. Or alcohol or cigarettes.
I don’t know. One of those things. Whatever makes you happy. You get to pick one thing in life and that one thing is free for you. Everything else you have to pay extra for. All right.
Uh, what do we need to be aware of when enabling snapshot isolation in a large production database? Should we expect any downtime? No, you shouldn’t expect any downtime. Uh, enabling snapshot isolation is of course different from enabling read committed snapshot isolation in that you do not require, uh, an exclusive, exclusive access to the database.
Uh, when you enable read committed snapshot isolation, uh, it is most beneficial to use the with rollback immediate, uh, addition to the command in order to do that. But with regular snapshot isolation, uh, you do not require any such thing. Uh, there could of course be some hiccups along the way.
Um, you know, you, you might see increased CPU if you have a busy system and you start generating a lot of row versions, uh, right off the bat. Uh, if you have more of a slow moving system, you probably wouldn’t see much of that. Um, if you’ve enabled accelerated database recovery, you may see some, uh, growth in your user database.
Uh, also in the transaction logs, since the, uh, persistent version store is fully logged. Um, so you might see some transaction log growth in there. Uh, if your error, if your, uh, transactions suffer a lot of errors or, uh, aborts, or, um, um, if you have, uh, certain types of transactions that, uh, do not qualify for, uh, accelerated database recovery to work on them, uh, then you might see, uh, some additional, uh, transaction log growth in there.
Uh, but no, I wouldn’t expect any, anything like downtime. You might just see some increased usage and utilization and other stuff like that. But, uh, usually those things settle out over time.
Uh, if you leave it, leave it, if you leave things, uh, to their own devices for a bit. But of course we are working with databases and there is no such thing as a risk-free change. All of these changes that you make to your database, uh, involve trade-offs.
It’s up to you to make sure that the trade-offs that you are willing to make are fair and square for your workload. So, uh, always test in a development environment first and then beat the tar out of your development environment. Really smack it around.
All right. Last but not least here. Uh, I enjoyed hearing you beg a viewer to switch from DPA to SQL Sentry. First off, I don’t beg.
I, I instruct, I command. Uh, no, I’m kidding. Uh, I, I, I do really wish that people would, you know, step away from DPA, but, uh, that’s a personal thing. Uh, can I hear you say more about what DPA does wrong?
I haven’t used it in years. Well, you’ve had some happy years then, haven’t you? Um, so, you know, all monitoring tools probably collect roundabout the same set of information. Some, you know, more than others.
I do think SQL Sentry collects, uh, a bit more since it is very specific to SQL Server and DPA is more of a generalized database monitoring tool. Of course, you can use it on other platforms, uh, which may appeal to some people, but does not particularly appeal to me. Uh, and, uh, I think my biggest gripe with DPA is in the web interface.
It is very difficult to navigate around and isolate actual problems. Uh, you find yourself, like, you know, just twiddling around through snapshots and everything’s in a different place. And, like, it’s, it’s just very, I can, I don’t know, like, the way it displays blocking and deadlocks, it, it just doesn’t, it doesn’t work for me.
It doesn’t, like, it just doesn’t mentally jive with the way that I, the way that I prefer things. Uh, and, you know, not to say that SQL Sentry is a perfect monitoring tool either. Um, you know, certain aspects of the way that it, uh, displays, uh, blocking and deadlocks are very, you know, sort of unfortunate Vizio weirdness kind of thing.
It’s like, I don’t, like, there’s too many arrows to click through to see stuff. And, uh, you know, having the, the deadlock graph constantly at the bottom is not terribly helpful for me. Um, you know, I, so, like, the reason why, uh, I originally wrote SP Blitzlock and the reason why I wrote SP Human Events Block Viewer is because a lot of the information about blocking and deadlocking, uh, really does, um, make more sense when it’s sort of laid out in front of you rather than you having to click through a million different things.
And, like, the visual representations of things are just, like, I don’t know. They, they, they, it doesn’t, it doesn’t work well, uh, for me, for, for those things. For things like query plans and other stuff like that, it works very well.
Uh, DPA uses, uh, some, uh, strange interpretation of Justin Peeling’s, uh, HTML Plan Viewer, which, um, is, uh, woeful. I’m not saying Justin’s thing is woeful. I’m saying DPA’s weird, uh, implementation of it is quite woeful.
Um, things are mislabeled and, uh, things are not shown, uh, in the tool that can be very useful, uh, when looking at execution plans. Often you have to, you know, download whatever, uh, execution plan DPA has saved off and open it in another tool of your choosing to, uh, view the query plan with, uh, is, with more details. Uh, and of course, you know, neither of these tools capture, uh, what I would say is an actual execution plan.
SQL Sentries, Trace will capture some execution details, uh, about long running queries. So, like, sometimes you’ll be looking through, like, the sort of Plan Explorer-esque, um, uh, query plan, like, stuff when you open up a query plan in there. And, like, some of the steps in there will have, like, uh, CPU and duration and stuff assigned to them, which is helpful, but you’re still not getting, like, the actual execution plan with runtime metrics in there.
Um, you just have some additional information about, like, at the trace level, what, like, how long that query ran for and CPU and stuff. So, there are helpful things in there, but it’s still not as good as getting an actual execution plan. Um, you know, uh, I think, I don’t know, uh, yeah, just, it’s really mostly the way that, uh, DPA presents the data and the steps that you have to take to dig in further, uh, with DPA in order to, um, in order to find, uh, problematic things.
Uh, you know, be able to come to some, some reasonable conclusion about, um, what, what went wrong and what you can do to fix it. Uh, also, um, if, if there’s one thing that I, I really hate about what DPA does, uh, it’s the sort of canned advice that’s in the tool. Uh, whoever wrote that, I don’t think, knows anything about SQL Server.
It doesn’t seem like they have ever actually worked with SQL Server. Uh, it, it seems like they maybe got most of their information from very old blog posts and just sort of, uh, copied and pasted some, uh, inherited old wisdom, uh, into, into the tool, which, uh, doesn’t really make much sense these days. So, that’s, that’s about it there.
Uh, so, I don’t know. Uh, I, I am, I am quite anti-DPA as a monitoring tool. Um, you know, I, I still haven’t found anything that, uh, does as well as SQL Sentry with the ease that SQL Sentry does. Uh, and again, I do think that, uh, uh, whoever is in charge of SQL Sentry at SolarWinds, uh, should be sent to some sort of federal prison for, uh, their, their neglect and malfeasance.
Uh, and, uh, keeping that tool up to date with more modern SQL Server, uh, information that is available. But, uh, that’s just me. Anyway, uh, that’s about it for this Office Hours.
Thank you for watching. I do hope you enjoyed yourselves. I hope you learned something, and I will see you tomorrow, where I will do a full, uh, uh, full, uh, example of why you might see some of this stuff get weird. So, with that out of the way, I’m gonna go do something else.
Uh, mainly, uh, record that second video while it’s on my mind, so I don’t forget. And I will see you next time! Alright, thanks for watchin’, bye!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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.