SQL Server Consulting, Education, and Training
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.
Questions:
To ask your questions, head over here.
In this video, I dive into answering five user-submitted questions on various SQL Server topics, including troubleshooting font issues in SQL Sentry, the pitfalls of using `ORDER BY NEWID()` for large datasets, and the complexities of restoring query store databases to different servers. I also share some insights on modifying stored procedures with forced plans and even reveal a little secret about my constant, albeit invisible, dance moves. If you have any questions or want more details on these topics, be sure to check out the links in the video description for interactive ways to engage with me and other community members. Stay tuned for tomorrow’s session where we’ll continue our journey into T-SQL and SQL Server performance tuning!
Erik Darling here with Darling Data. And you wouldn’t believe it’s that time. Can you believe it’s just another Monday? And we have to do office hours, don’t we? This is where I answer five user submitted questions at a time, usually. Every once in a while I’m off by one. But let’s get on to that. Before I answer questions, we’re not doing anything. You are not contributing anything to this except questions, which hopefully you’ll do. And you can find the link to do that down in the video description. There are all sorts of helpful links down there for you to interact with me in all sorts of exciting ways. So I hope you will choose to peruse those links and excite me in some way. If you want to get excited in person, we can do that together. That’s it. That is definitely a wee thing. I will be on tour with the nice folks at Redgate. We have two mini Pass On Tour events coming up. One in Dallas, September 15th and 16th. And one in Utrecht. That is, in fact, the Netherlands, I’m told. October 1st and 2nd. And of course, all this is leading up to Pass Data Community Summit taking place in Seattle November 17th to 21st, where I will have two wonderful, mellifluous days of pre-cons with the lovely and talented Kendra Little. All about T-SQL and performance tuning a little bit, because it’s hard to talk about T-SQL without some performance stuff sneaking in there, isn’t it?
Anyway, let’s go get them. Let’s go do these here questions. Boy, I have a lot of stuff in the taskbar today. A lot of work going on. A lot of stuff flying around. A lot of moving pieces here. Hopefully, you can keep track of them all. All right. First question. Let’s see what we got here. I don’t understand why the font is a little wonky on that. Not my fault. I’ve just installed SQL Sentry. Any advice? Well, we should open it. Make sure it’s running. Make sure it’s collecting data. These are all good things to do. Assuming that you’re talking about the monitoring tool and not just PlanExplore. If you’ve just installed PlanExplore, I have bad news for you.
Use SSMS instead. But if you’re using SQL Sentry, that is one of my favorite monitoring tools out there. I would suggest maybe taking a look at the alerts section and making sure that the alerts that are set up and being sent out are ones that are of use and value to you. It does default to sending out a lot of alerts and not all of them are particularly useful or meaningful in every environment.
The other thing that I would suggest you do is when you go into the top SQL tab, there’s a little settings button somewhere. And there’s a little settings tab along with top SQL, procedure stats, query stats, stuff like that. And by default, SQL Sentry only shows you the top 15 queries.
It will show you a max of 5,000 queries. So I suggest changing the 15 in there to 5,000. Alongside that, way up at the top of SQL Sentry, there’s a little button that sort of looks like a Greek E, right?
Whatever that little epsilon thing maybe. I don’t exactly know what it’s called. I’m not Greek.
And it’s all to me. So you should push that button because that button will group the queries in the top queries and procedure stats and query stats tabs together by text pattern. So if you have the same query over and over again doing stuff, you can see sort of top-level grouped results for CPU duration, memory, things like that.
So that’s my advice. That’s where I spend most of my time in there. Don’t neglect the blocking and deadlocking tabs.
Those are also very useful. Aside from that, I would just say enjoy yourself because you have the best monitoring tool on the market despite SolarWinds’ best efforts to have it do nothing new or interesting. So I actually think that they should be arrested for how they’ve neglected SQL Sentry.
As a monitoring tool, DPA is, of course, a tire fire that no one should ever be forced to use. All right. Next question.
Order by new ID will impact on query execution on large data table. Yes. Don’t do that unless you hate yourself or someone else. Ordering by new ID is a great way to have a performance problem.
Demo is really well, but don’t do it. It’s avoid at all costs. If your question is about getting random data out, ask that question instead and we can talk about that. If you just really are not sure about what new ID is going to do with an order by, good luck.
All right. Here’s a question, I believe. Yeah, there’s a question, Mark.
Look at that. Are you aware of any issues related to restoring a query store database with forced plans to a different server with a different database name that the forced plans will no longer work? That sounds like a statement.
It sounds like something that you have found. It sounds like you have found an issue. And you are asking me if I’m aware of an issue. And it sounds like it’s an issue to me. I would think that that I would guess like this is without me going and, you know, looking at the looking at that and actually going through with all of the steps that you’ve outlined there.
I would guess that the plan forcing would have some difficulty because the database name stored in the query plan XML might throw things off. And you would probably end up getting new query IDs for the queries that had forced plans once the new database name comes in with the plan XML. So I would guess that that’s not going to work, but you know, probably not great.
So here’s another question. When you modify a store procedure that contains a query with a forced plan, it get a new query ID in query store, even if the query hasn’t changed. Are there any tricks for applying the already forced plan to the new query ID?
Well, no, you have to get that plan for that new query ID if you want to force it for that query ID. Otherwise, otherwise, query store throws an error saying that there’s no plan ID for that query. I would be interested because you’re saying modify.
And I think that if you alter or create or alter, actually, you know, I think if you alter a store procedure, it keeps the same object ID. I know that dropping and recreating will assign a new object ID. So I’m not sure that that would be it.
But it’s an interesting question. And I think that’s something that I would need to dig in on a little bit. But I don’t have a quick answer for that because I’ve just never looked at it.
So there we go. All right. Last question here. Will you do a jig or goofy dance?
Feel free to have fun with your green screen on this one. Well, I am dancing currently. So what you don’t know about me is that I am constantly dancing. You can’t see from here down.
But my legs are just in a constant flurry of dance. So maybe that’s just the level of skill that I have at the form of dance that I practice. Is that you can’t even tell I’m doing it because nothing up here moves.
So now you’ve learned something new about me. Anyway, that’s five questions, I think. Well, that’s five questions and some homework for me, I think.
All right. So I’ll have to go look at that. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will do some more T-SQL learning or something along those lines. And after that, who knows?
We’ll figure it out when we get there, won’t we? All right. Thank you for watching.
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.
In this video, I delve into some of the often-overlooked aspects of the `RAISERROR` function in SQL Server. While many have moved on to using `THROW`, I still find significant value and utility in `RAISERROR`. I explore its various substitution parameters, such as `%d` for signed integers, `%x` for unsigned hexadecimal, and `%i64d` for bigints, pointing out the absence of native support for bits or temporal data types. This can be a bit cumbersome but also opens up creative possibilities for error handling in dynamic SQL scenarios.
I also cover how `RAISERROR` can be used effectively within loops to print messages without waiting for buffer fills, and demonstrate how adjusting severity levels can change message appearance. Additionally, I show how you can dynamically assign values to parameters like severity and state based on your code logic, providing a more flexible error handling mechanism. The video concludes with practical examples of using `RAISERROR` in dynamic SQL contexts, highlighting its benefits over traditional error functions.
Erik Darling here with Darling Data. And today we’re going to be talking about some of the somewhat overlooked things about raise error. Because I still find value and use and utility in raise error. You know, some folks have graduated to throw or semicolon throw, as I often see in the world for no particular good reason. So you just terminate your previous T-SQL statement, you won’t have a problem. So there’s that. Anyway, we’re going to do that. In the meantime, if you feel so bold and you look a little bit further down in the video description, right? My face is up here, but I don’t care if you look down here for a minute. You’ll see all sorts of useful and helpful links for ways where you can interact with me further. You can hire me for consulting. I do that as a full-time job. It’s nice. You can buy my training, right? Including the Learn T-SQL with Erik course that this is teaser material from. It’s down there. You can become a subscribed member of the channel. You can give me a small amount of money every month to keep producing this wonderful content. You can ask me office hours questions for those episodes where I answer five user submitted questions. I get a bit of a backlog now, but you know, times are busy. So, we’re rolling these videos out as we can. And of course, if you enjoy this content and you feel that it would be a remarkable, it would be a remarkable improvement in someone’s life if they were to view this content on a regular basis, I would suggest that you not only like and subscribe, but you tell that person about this channel. Perhaps send them a link and tell them, encourage them to like and subscribe. So, with that out of the way, we need to discuss my travel plans.
For the remainder of the year, where I will be in Dallas, September 15th through 16th and Utrecht October 1st and 2nd for a couple of mini pass on tour events that Redgate is putting on. And of course, this all leads up to the greater, larger, past data community summit taking place in Seattle, Washington from November 17th to 21st, where I will be doing two days of T-SQL pre-cons with Kendra Little. So, we do have many things to look forward to there. But with that out of the way, let’s talk about some of the fun stuff with raise error.
Now, the first rule of raise error is to make sure that you understand how to use it. Now, if you look at things that you can use as substitution parameters or strings with raise error, or substitution wildcards or something with raise error, you’ll notice that you have %d or %i for signed integers.
You have %x or that should be % uppercase x. Let’s fix that. Let me don’t say zoomed in on that for too long. Got lost in the copy paste or something. We have %o for an unsigned octal. I’m not sure what that is.
I’m not a math person. I don’t know what an octal is. You have %s for string. You have %u for unsigned integer. Well, apparently the… I don’t know.
I don’t know why you wouldn’t sign an integer. And then you have %i64d for big ints. There are a couple notable things that are missing here.
Like say, bits. Right? Or say dates or times or date times. If you want to use bits, or you want to use some temporal thing in here, you have to store that as a different data type before you are allowed to use it with raise error.
So if you have a bit, you often have to store it as a tiny int. And if you have a date, you often have to convert it to a string in order to present it as a substitution in raise error.
So there are no native replacements for bits and strings with it, which is quite annoying. I find that quite obnoxious about raise error, because now I have to declare all these additional parameters and hang on to them, and it’s just annoying.
Why we couldn’t get bits and strings for raise error? I don’t know. It’s just the way it is. So there are some neat things with raise error. Or if you just want a normal printed message, you can use log if you want to send it to the error log.
I’ve never found a lot of use for this. Maybe people who do more dedicated SQL development work, who plow through the error log for various things, find this a useful addition to it.
I’ve never really used it all that much. Most of the code that I write these days is my store procedures, where I don’t need to send anything to the error log.
There wouldn’t be any use in that. And most of the client code that I work on, there’s no sense in polluting their error logs with anything that would happen in there. But if I were writing some sort of maintenance procedure, this might be useful.
But notice if we use 10 for a severity here. I am an error gets printed in, well, because I’m using dark mode. So this text is some form of white.
If I were using light mode, this text would be some form of black. So local factors will apply. But the most important thing is that this doesn’t look much like an error, does it? It just looks like a regular printed string.
That’s why raise error can be very, very useful when you’re working with things like loops. So if you are writing a loop of any variety or any sort of task where you want to print a message when things happen, this with no wait is very, very useful because you do not have to wait for these messages to buffer anywhere before SSMS starts sending them out.
If you just use print, you do have to wait for certain buffers to fill before the print messages come out. If you want your text to come out red, you can use severity 11 or higher. And now notice we get some additional stuff here along with the text being red.
This would be red regardless of dark mode or light mode or anything in between. System default, if you will. One thing that you do want to avoid though is not using an aggressively high error message.
So here I, the last one is severity 11. Here I’m going to use severity 20. And severity 20 is just about as high as you can get. But when I run this, well, we get a whole lot of stuff back.
I once worked with a client who was doing, was using raise error for stuff. And people like the one developer was putting a level 20 in there. And everyone thought that the database was corrupt and that there was some problem.
Like every time this, like this, the store procedure had like hit an error, it was like, it would spit all this stuff back. And people would be like, we got corruption run check DB there, run check DB. Nothing came back.
And it’s very confused until we looked at the code and I said, ah, well, guess what? We’re all safe. We all live another day. So what, what, getting to sort of what I was talking about first with a sort of wildcard replacement is that you can put whatever you want in here, as long as it’s not a bit or a date or a time or a date time, those you have to store differently.
But you can, you can substitute things in either as a string or as an integer. These are the replacements that I’ve chosen here with percent S and percent I. Well, I guess there’s already a little line underneath it.
So we’ll give this one a hat. But what’s kind of nice in here is that if we run this, we will see the replacements all worked very nicely. Now, one thing that people do miss sometimes is that you can substitute anything in raise error with a parameter or variable, right?
So in a lot of these, we were using just a literal value for severity and state. But you can, you can have, you can assign this dynamically based on whatever your code does. And you might, you know, assign some things a severity of 10.
If you don’t care about it being read and printing an error and maybe like halting execution, you, if you just want to like, you can assign things any way you want. So like, if you look at this, I’ve declared some variables in here in order to do a little bit of replacement.
So I’ve got error message. I’m just going to highlight this part. So you can add a little bit more, uh, severity, state, error number, error procedure, and error line. Now in real life, like if you were writing like real legit error handling, I don’t know, you would probably just use the various built-in error functions to assign these in your try catch block.
And that would be totally fine. You could also assign your own things to them. If you want to a little bit more control over what gets spit out to people, right? Like you might like, instead of like a full crazy error message, you just might say, retry your procedure or something, right?
You put whatever you want in there. It’s nice. It’s a good time. So what you can do is have any one of these, uh, replace things. So we have severity and state this time is, uh, well, obviously local variables, because I just declared them up there, but then we have, uh, all the replacement stuff that I would be using down here.
Right? So, uh, you’ll see number procedure line message, all the stuff that you would put in like a normal, good verbose error message to help you figure things out. And if we run all this, we will get all that back.
Uh, I’m going to put that on a new line just so we have things a little bit more friendly for zooming in and we have our number, right? Good for us.
Right. And we see, well, we used 11 and one up here, so that doesn’t really do anything, but we, uh, we did mess with our line number a little bit. Uh, and then we have, uh, the number that we wanted to replace, uh, the procedure name, right?
So like, if you write a lot of dynamic SQL and, uh, maybe, you know, you want to raise an error and you like, you know, procedure name is cool and all, but you know, you could say like dynamic SQL block 10 from whatever procedure, and you would be able to locate the dynamic SQL block that you, that you, uh, you like name or like label dynamic SQL block 10, like a comment in the code or something.
And, you know, I’m grant, you know, I write some long procedures, but that’s, that’s getting out of control. And, you know, we can put in whatever message we want here. So there’s all sorts of fun stuff that you can do with raise error to make things, uh, better suited to your environment, more descriptive and a little bit more dynamic than what you might get with sort of like just normal assignment to the various built-in error functions that SQL Server has.
Um, I like using this stuff, particularly, particularly with dynamic SQL, uh, because I can, uh, maybe like in the error message, I can do a preview of the dynamic SQL, or I can put the error procedure as, you know, whatever block that I like labeled in the procedure with a comment, something like that.
So there’s all sorts of neat things you can do to help you get exactly to the code that’s having a problem that maybe wouldn’t be so obvious if you were just using like, um, like the normal results of error functions.
So, uh, easy Friday video, nothing too crazy in here, but I hope you enjoyed yourselves. I hope you learned something and I will see you over in the next video, which will be on Monday, which will be office hours, reliable office hours, where I will answer five user submitted questions because I will, I will count carefully.
I will endeavor to count more carefully in the future. Anyway, thank you for watching.
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.
In this video, I delve into the nuances of temporary object comparison within SQL Server, focusing on table variables and temporary tables. I explore their respective strengths and weaknesses to help you choose the right one for your specific use case. Starting off, I highlight the downsides of table variables, such as preventing parallel query plans when modified and lacking column-level statistical information. In contrast, temporary tables offer more flexibility with full parallelism support and better handling of statistics. The video then dives into practical examples, comparing execution plans between a table variable and a temporary table to illustrate how cardinality estimates differ significantly. By the end, you’ll understand why temporary tables might be preferable for complex queries that rely on accurate cardinality estimates, making this content invaluable for anyone looking to optimize their SQL Server performance.
All right, Erik Darling here with Darling Data and continuing on the theme of learning T-SQL. I suppose it’s worth saying that all of this content is teaser content, of course, for my Learn T-SQL with Erik course. Today we’re going to be talking about temporary object comparison, not how to like compare data from one to another, because that’s dull. Intersect, accept, whatever. But more how to choose the right one. one for you and for each particular use case that you may have for a temporary object. So, before we move on to that, if you are saying, wow, a course where I can learn T-SQL with Erik. Well, yeah, there’s a description, in the video description, there’s a link to do that. The course is still currently at the presale price of $250. But as soon as the advanced content is all done, which probably has like maybe like two more months left on it, and that like fully recorded and produced, like a quarter of the way written, you will have to pay $500 for it. So I would strongly suggest buying it now. You can also do other things down there, like hire me for consulting. You can contribute money to the channel to support my work to bring you SQL Server content. You can ask me questions for my Office Hours episodes that I really enjoy answering. And of course, if you enjoy this content, but not in a monetarily productive way, you can always like, subscribe, and of course, tell a friend, because maybe your friend is secretly rich, wants to hire me for anything. Cool. I’ll be leaving the house a few times between now and November. I will be in Dallas September 15th and 16th. I will be in Utrecht October 1st and 2nd. And I will be in Seattle November 17th to 21st. What do all of these things have in common?
Well, they are all past events put on by the nice folks at Redgate, who were kind enough to decide to have me show up at all of them and talk at you about SQL Server performance tuning. So with that out of the way, let’s compare temporary objects here. Let’s make sure that I go to the right SSMS spawn because, well, I need multiple of them. So let’s talk first about table variables. They have some upsides and some downsides. Downsides would include preventing parallel query plans when you modify them. At base, you need to put data into a table variable via an insert. And of course, that will force whatever query populates the table variable to run fully single threaded. There is a non-parallel plan reason for that.
There is no column level statistical information. There are no histograms available for table variables. This is true even if you index them. You can get some benefit if you create a unique index on them for things, but you still don’t know what values are in there that are unique. You often get poor cardinality estimates with them when you start joining on them to other tables or filtering on them locally with a where clause.
There is no recompilation threshold with some exceptions. There are some trace flags out there and there are some newer SQL Server features out there where you may find a recompile does happen. Most notably, if you put a recompile hint on the query. There we go.
You cannot select into a table variable. Table variables use the collation of the local database that your procedure is executing in. They are only available in the current scope and batch, so you cannot declare a table variable out here and then reference it as easily as a temp table, either in dynamic SQL or in another store procedure.
Modifications to table variables are not automatically rolled back on error or explicit rollback. They can be used in some functions like a multi statement table valued function or a T-SQL scale R UDF. And of course, you can create indexes on them when they are declared.
Temporary tables have a bit of a different profile. There’s no inherent parallelism restrictions. You can insert, update, and delete. And as long as SQL Server thinks a parallel plan would be a good idea, you can get yourself a parallel plan.
They do allow for statistical information and histograms. They can sometimes suffer poor cardinality estimates, though, because statistics can be cached when they are used in store procedures. They do have a low compilation threshold, so you don’t often run into the cached temporary table statistics problem, but never say never.
You can select into them. Temp tables use the collation of tempDB. So if you might ever look through my store procedures that create temp tables, you might see a lot of things where I need to collate to the local database.
So like collate database default, and that is to prevent string comparison issues if the collation of tempDB does not agree with the collation of the local user database that the store procedure is running in. They are visible to child batches and scope, so they’re very easy to reference in Dynamic SQL or in other store procedures that you might call. Modifications are rolled back on error or explicit rollback.
They cannot be used in some functions, unfortunately. And you can create indexes with the table. When you say create table, you can make the index definitions inline, or you can add them on later, depending on which you find to be most beneficial. There are a lot of insert patterns where you want to insert into a heap temp table to get a fully parallel insert, and then you can create an index later on that.
And you also get the benefit of a full scan statistics histogram when you create the index later, rather than the sampled histogram that you would get if you loaded the data with the index already there, and then maybe use the column in a where clause or a join clause or group by or something. There are some similarities though, and these are non-negotiable. They both live in tempDB and take up space there.
Table variables are not in memory unless you are explicitly using the in-memory SQL Server feature and creating in-memory table variables, which do not look like the declaration of a normal table variable. They both log equally to tempDB’s transaction log. They are both session scoped, meaning that once the session that created or declared the temporary object is gone, they are gone.
Global temp tables have slightly different rules, but we’re not talking about those. Simultaneous sessions can use temporary objects with the same name without existence errors, but that, of course, does not apply to global temporary tables. Now, that’s a lot of talking, isn’t it?
We’re already seven minutes, and still, all we did was talk. But now we can get down to some of the nittier, grittier stuff with them. So what I’m going to do is create a table, or rather declare a table variable called user scores. And I want you to note that I have some check constraints on here, and I have a couple indexes on here.
Now, the point of these indexes is to aid later queries. So what I’m going to do is I’m going to run all of this stuff, and I’m going to talk about these two hints in a second. But let’s run all of these, and let’s wait a moment, and we’ll get the actual execution plans for those when this is all finished.
So because I am using SQL Server 2022, and my compat level is 150 or higher, keep in mind this is a feature that started in SQL Server 2019 with compat level 150 up, assuming you’re on Enterprise Edition, of course.
You have paid the Microsoft friendship tax, and they have decided to try to help the performance of your workload. Because of that, I have an intelligent query processing feature called table variable deferred compilation at play here, which is going to get me table level cardinality for like a full scan of the table or something, a full join of the table.
But you will see in a moment that this does not help us with cardinality when we have a where clause. So if I were to apply either one of these hints to either disable that feature or to use the optimizer compatibility level 140, the table cardinality guesstimate for table variables would disappear.
But this is all finished running, and I’ve got a few queries down here. I’ve got one query saying where owner user ID equals 22656. I’ve got one query here asking for where score is greater than zero.
And I’ve got one query down here asking for both. So this should be interesting because, like we went over, when I created this table, I had a check constraint for score being greater than zero.
And I have an index on owner user ID and an index on score. When I inserted data into the table variable, both of these things were written in a way to get us the same owner user ID for every entry in here, right?
Every row in this is going to be 22656. And of course, we have score greater than zero in order to not run into any check constraint errors. But the cardinality estimates that I get from this, I mean, the totals are all correct, but, you know, not much else.
Starting with the top query that does the insert, you will see that there is a clustered index scan here, a clustered index insert here, and then there’s an assert over here, which the check constraint uses to make sure that all of the scores are greater than zero.
If you hover over the clustered index insert, you’ll see the two additional indexes listed that were inserted into for owner user ID and score. Anyway, moving on.
If this query ran for 4.295 seconds, and you’ll notice that there are no parallelism indicators at all for this plan. This is because if we right click and hit properties, we will have this non-parallel plan reason over here, like I described earlier, where SQL Server will not be able to generate a parallel execution plan when we go and, what do you call it, run the query, do the insert.
There we go. Anyway. All right.
Moving on. Now, because I have the table variable deferred compilation intelligent query processing feature enabled here, and it happens for us, what SQL Server is able to do is when we just get a count of the entire table, where one equals select one will always mean true, but when we do that, SQL Server is able to guess that 27,029 rows will come out of here.
Great. But this is where things do start to fall apart. Why?
Well, because all SQL Server knows is that there are 27,029 rows in the table variable. It still has no description of what values ended up in there. So when I change the query a little bit, and I say where owner user ID equals 22656, SQL Server, well, we still return 27,029 rows, but SQL Server guesses that 164 of them will meet that where clause.
There’s no histogram. If there were a histogram, we would see that. Now, excuse me.
If we look at other places, we’re going to see where I say, hey, show me where score is greater than zero. Well, we know that every row in there is greater than zero because of that check constraint, and we have an index on score, but SQL Server says, well, I think you’ll get about 8,100 rows back.
And look at the check, neither the check constraint nor the index were of any benefit to cardinality estimation here. And going down even further, if we say where owner user ID equals 22656 and score is greater than zero, SQL Server says, I think you’ll get about 90.
But we still get back all 27,029. So it is difficult to prove a negative when looking at things like this. But if we look at the properties over here, there’s a thing missing from over here.
There’s usually, if you look at a query where statistics were in use, you will see an optimizer stats usage entry in the properties of the root operator in the query plan. But we do not have one of those here.
Again, it is difficult to prove a negative, but we can go on and use a temp table and prove a positive, can’t we? We can do something very scientific. So what I’m going to do is I’m going to create a temp table.
And I’m not even going to festoon it with all of the things that we had on the other one. I’m not going to do that. I’m just going to say we have two columns in this temp table.
They are both integers and they are both not null. There’s no check constraints. There’s no indexes. So that’s what we have going for us. So let’s drop this table if it exists, even though it doesn’t.
And let’s do that exact same insert. Now, if you look at the execution plan, you’ll note that we do get a partial parallel plan here. SQL Server does not use a fully parallel insert.
Why? I don’t know. Didn’t meet the cost. I think it was maybe the number of rows because SQL Server estimated 213 rows. And, of course, we got 27,000.
But, you know, I think there’s a cardinality estimation thing at play with if SQL Server decides that a parallel insert is going to happen or not. But anyway, we do not have, clearly do not have a non-parallel plan reason for the insert into the temporary table. And you’ll note that it does run in about 700 milliseconds.
I think the other one was, was it 3.4 or 4.3 seconds? I forget. But it was much longer than this, right? It was a long time. Now, we’ve got this table and we’ve got it populated.
So that’s our good first step. Now, let’s run the same queries, but now against the temp table that we had against the table variable before. We’re going to get back all our 27,029 results, which is a good first step for us here.
But if we look at these, of course, you know, table cardinality, very easy to guess. So this top one, you know, it gets a good, good grade here, right? 27,029 to 27,029.
Not much difference just yet. But now, boy, howdy. Look at, we get accurate cardinality for both of these. When we, when we said where owner user ID equals 22656, SQL Server looked at, generated statistics on the, on the, on the temp table, just on its own. Remember, there’s no indexes on this thing.
SQL Server just generated statistics and said, hey, that’s 27,029. All right. I know. I got this one. I’m your friend.
And when we say where score is greater than zero, SQL Server said, hmm, got your back, homie. 27,029 of 27,029. So now not only do we have accurate table cardinality, we have accurate column level cardinality when we start filtering on our table variable.
This becomes very important if we were to start joining, sorry, our temporary table. This becomes very important if we were to start joining a temporary object off to other larger tables where perhaps, you know, different, having statistics on columns that we’re joining to very large tables would be beneficial in SQL Server generating an optimal execution plan, don’t you think? And if we combine these two predicates where owner user ID equals 22656 and score is greater than zero, SQL Server will once again, remember last time it said like 90 or something?
Now we get the full number of rows that would actually come out of that temp table. So not to say that table variables do not have any uses, but for me as a performance tuner, generally I am very interested in not only materialized results, but getting either better or more accurate cardinality estimates when I start doing other things with those materialized results. And this is where I mostly want to sway people to use temp tables instead of table variables, but I still have to spend a lot of time going over things like this.
So perhaps this channel just does not have enough reach. Perhaps not enough of you have told a friend about this channel and perhaps you should because there are a lot of people out there who still need to know these things. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where we are going to talk about some of the fun and joys and quirks of Razor. It’s of course a much smaller portion of an error handling module, but you only get teasers because you didn’t pay for it.
If you buy the full course, you get the full content. All right. Anyway, goodbye.
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.
In this video, I dive into the exciting world of Dynamic SQL and why using `QUOTENAME` is far superior to simply adding square brackets in your code. I explore how `QUOTENAME` can protect you from potential SQL injection attacks and handle strange characters or names that might appear as column or object names. Additionally, I share some amusing bonus content at the end, including a clever workaround for dealing with very long dynamic SQL strings that are too big to print in one go. Whether you’re looking to enhance your T-SQL skills or just want a laugh, there’s something for everyone!
It’s me, Erik Darling with Darling Data. And today’s video, we are going to go over some of the kind of fun things with quote name when you’re using Dynamic SQL and of course why it is far superior to the old, I’m just going to stick some square brackets in my code because that doesn’t give you the same level of protection. And then there’s going to be some rather amusing bonus content at the end, which I do hope you’ll enjoy because without your enjoyment, I die. No pressure. Anyway, if you look down in the video description, there are many important links in there. And they’re very clearly labeled places where you can hire me for consulting, places where you can buy my training and of course at a nice discount because you watch my videos and you know, I think I think, I think you’re a nice person and I think you deserve a break in life, especially if your employer is not willing to pay for training. It is difficult to afford the very, very high priced, often not worth it training out there on the internet. You can also become a subscribed channel member where you actually give me a small monthly donation in exchange for all of the wonderful content that I produce. You can ask me questions for my Office Hours episodes where as long as I’m going to be able to get a little bit more information, I’m going to be able to get a little bit more information.
As long as I count correctly, I answer five user questions. And of course, if you enjoy this content, I would encourage you to like subscribe and of course, tell a friend. The more friends you bring, the more people I have to talk to and gosh, then I might bring joy to more people and I might live longer, which is hopefully good for all of us. Speaking of things that are good for all of us, I’m getting out of the house. Actually, I’m getting out of the country a couple of times. Once, once that, oh, I mean, is Texas another country yet? I don’t know. Hard to keep track. So much going on in the world. But the nice folks at Redgate are dragging me around to Dallas, September 15th through 16th. Utrecht, October 1st and 2nd. And of course, past data community summit in Seattle from November, from the 17th to the 21st, where I will be hog tying and spit roasting two days of T-SQL pre-cons with Miss Kendra Little. So I do hope you attend those. Can’t wait to see you there. Again, no pressure. Might die if you don’t come. Wouldn’t be good for either of us. And then, I don’t know, whatever. So with that out of the way, let’s talk about some of the joys of quote name here. Now, I am in such a busy state that I need multiple SSMS spawns open. So please don’t judge me.
No, I’m not, I’m not here to bring pain to anyone. Anyway, important reasons why you need to use quote name. Well, well, it is somewhat unlikely that you will encounter someone naming a column, the clown emoji out in the world. You never know. You never know what kind of rough scallions you’re going to see out there. So what you need to do is protect yourself. Cause there are other things that people might do, like put spaces or dots or dashes or put weird things in, in, in, as column names that, that really cause issues. If they are not properly quoted in a way where SQL Server can identify the full text as an object name.
So let’s, let’s create this table, which, which, which happens successfully, even though we’re not using quote name here, but we do have to use it while we’re constructing dynamic SQL. That is if we’re smart. Now, one thing that I like to do when I’m trying to write very safe dynamic SQL is maybe not depend on user input, uh, very much for things. Um, you know, I, I very much believe in, uh, maybe taking the user input, but using it to value, like, uh, especially when it’s, um, you know, when you’re using dynamic SQL and you need to take, uh, like sort of dynamic input for things like database name, schema name, table name, column name, all of that information is in your, your server’s metadata.
Uh, more, probably more specifically, well, I guess from server level down to database level. And so I’d like to take that input and not use that maybe directly in the dynamic SQL. I like to generate my own inputs based on that metadata that’s in the, in the server where I, where there’s no chance of SQL injection of anything malicious happening.
And I can sort of weed any of that out. So whenever someone, uh, will pass in a list of columns or a database name or a table name or a schema name, I always validate that stuff against server. Metadata.
And I, I always get, uh, like the, the stuff, like the parameters and variables that I’ll use in dynamic SQL from the metadata directly, because it’s much, much harder for someone to tamper with that maliciously. Of course, with, with the remote DAC, you can do many fun things. So like one thing that I’ll do.
And let’s like, you know, if, if, if let’s like, let’s pretend that column names was, uh, like a parameter, right. Uh, to a store procedure on someone could put in a CSV list of column names. I would even use, uh, go even further and validate those column names when the metadata for whatever table we’re looking at.
So there are all sorts of things that you can do to protect yourself from a SQL injection. Using quote name is a very good, not only good for helping you avoid SQL injection, but also for helping you deal with straight objects with strange characters in them. So let’s, uh, run this, right.
Let’s get this, let’s get a list of column names and let’s look at what dynamic SQL this generates because this is going to fail mightily. If we look at what happened in here, SQL Server tried to run this select query. And this select query is not going to run with any of these things as column names without square brackets.
And it’s certainly not going to be able to select anything from a table name with the space in it. If you’re like, if you keep in mind, there’s an absolutely no like underscore or anything here. It’s just a bare ass table name.
So we want to use quote name to fix these things in order to encapsulate them correct correctly. So if we rerun this and now instead of, uh, just taking the name from, uh, sys.columns, we’re going to say quote name, uh, c.name from sys.columns. And down here, we’re going to wrap these various things in quote name as well.
Now, if this were like a big old complicated store procedure, there’s a pretty good chance that I would, um, have maybe like an inner variable that I would set to being like quote name, database name. But of course, uh, after like verifying it and like sys.databases or something. So just here for sort of brevity, I’m putting quote name around these, but like in real, like big code, I probably don’t want to like have quote name everywhere.
Cause I may forget at some point. So it’s much easier to set it in one place and then just have the object name you care about quoted. But now, of course, when we run all this and we get down to the print and the execute, this works just fine, right?
But there’s no data in the table, but we at least run the select successfully and we get back our column names here. And the dynamic SQL that gets printed out would, uh, completely, uh, uh, avoid any of the terrible errors that we saw before, because now we have square brackets around everything. Now, the nice thing about quote name is that, um, it will produce a valid output for anything that is a legal valid object name, uh, where you have to be careful as if things get longer than that.
But we’ll talk about that in a moment. But the reason why, um, square brackets do not work the way that quote, like the, as well as quote name works is because if you were to do something like this, right? Like, uh, you could like, if you were just to put your own square brackets and can concatenate those into a string at some point in the dynamic SQL, they, they are not, they, they, they do act as an object identifier, but they do not quote out additional square brackets.
So quote name would, would fix this, right? Right. Quote name would actually protect you would like double quote these things. So you would get valid results back, but, or rather it would, it would escape those things.
So you would get valid results back just adding in square brackets does not do that. So if we run this whole thing, we will see that, uh, we get valid results, but we actually executed SP who right here, right? We got all this dumb information back that no one ever understands and is very confusing, but over in the messages tab, this is what we ran, right?
We executed SP who, and then we dropped a table, right? And then when we try to select from that table at the end, we say invalid object name, right? And the reason for that is because just adding in square brackets will not escape any additional square brackets in here.
So this does not have the same power. If we were to do this and use quote name instead, right? So like, rather than say like square bracket plus this string plus square bracket, if we just say quote name, that same string, well, all of a sudden SQL Server is very, very happy to, uh, say, well, I’ve, I’ve never heard of the store procedure SP who dropped table.
All right. That’s not a thing. So like notice that when, uh, when I made this string, there was no double square bracket here, but when I use quote name, look what happens here.
We get double square brackets here. So quote name does some extra stuff that just rolling your own square brackets doesn’t do. Now you could totally, you know, do something on your own and be like, well, I’m just, if there’s a square bracket, I’m going to replace it with double square brackets to just use quote.
Right. And unless there’s some strange reason why you can’t use quote name, you’re just allergic to the letter Q. Maybe I don’t know.
I don’t know. Use quote name. Very, very simple. But, uh, just, you know, there are some cases where quote name will return a null if you, uh, make the string too long. So please do try to avoid that.
Um, if you, if you do have a situation where you need to, uh, square bracket something that is longer than quote name will return a string for, uh, where if it would just return a null, you have my blessing to use square brackets and replace square brackets within the string with double square brackets. Okay. Okay.
You can do that. But for most cases, you know, database schema, object table view, store procedure, anything like that, any, like, you know, system, like, uh, system identified object, uh, a mix of sys name and, uh, and quote name is totally fine. Um, there, you can use different things for quote name.
Uh, so like you are not just stuck with square brackets. Uh, you can use, uh, single quotes. You can use parentheses.
You can use curly brackets. You can use double quotes. You can use those funny little ticks, but you, you cannot use tildes. I’ve learned. Uh, sorry, Tilda Swinton. I do apologize. But if we run this, you will see that the word Eric has been, uh, bracketed, single quoted, parenthesized, curly bracketed, double quoted, single ticked, but, uh, we cannot tilde Eric.
Tilda and Eric were not meant to be. I will not be one of her husband’s sad to say. Uh, but one thing that is, uh, useful to talk about is that if you, if you like, so when I was talking earlier about how I will often, uh, in my store procedures, I will have a, uh, like an inner variable that I’ll set to be like quote name.
Um, something like, so like, let’s just say it’s a, uh, someone, I like, I let someone pass in a database name. I’ll hit sys.databases. Right.
And I’ll, I’ll get the database name from there where the database name parameter they pass in equals like the database name and sys.databases. And I’ll put quote name around that in that internal local variable. Where that can mess things up is if you have to do anything with it later to validate other metadata.
So now you have a database name with square brackets around it, but that your, your database name probably isn’t stored like that in sys.databases. Your schema names aren’t going to be stored like that in sys.schemas. Your table names aren’t going to be stored like that in sys.tables and so on and so on and so on.
So if you need to locate other data with, um, something that you have already quote names, uh, it’s, it’s really helpful to use parse name. So parse name is another built in function that will actually remove those square brackets. So you see up here, I have, uh, declared this local variable DBO.
And if I run this, you’ll see that, uh, just adding quote name again, we’ll double. Well, actually, well, you can actually see there’s three there, right? So we actually add in another square bracket quote name tries to do us a solid, but when we use parse name, we remove the square bracket.
So sometimes if you, in any of my procedures, you see like where some object name equals parse name, some local variable. It’s because that local variable has a quote name quoted identifier in it. And it’s messing up me looking up other metadata.
But of course, if we pair, uh, parse name and quote name, we will go back to just having single brackets. So there are all sorts of neat things you can do. And, um, last but not least, as I promised, I’m going to show you some, some, a couple of amusing things.
Now, one thing that, uh, I run into a lot and that I run into people sort of, uh, having issues with a lot is when, if you’re writing dynamic SQL, or, uh, you have your, whatever string you’re building is very, very long. So, uh, you might not be able to print it successfully in one go. It’s not a good time.
It’s not a good time. Because now you’re looking at like having to chunk print statements with like sub string or something, maybe write like a while loop to produce like a, like continuously printing thing until you run out of characters. That’s not a lot of fun.
Now I think generally for me, it’s a lot easier to, uh, produce like an XML cookie column where I can just click on it and get the full thing somewhere else. That’s, that makes my life a lot easier. So, uh, let’s pretend that I have, this is a very long string.
One way of achieving that is to use, uh, this processing instruction alias. And I just have an underscore in here to avoid like having any, like, like, like I could put anything between these, uh, these parentheses. I could put like query text something, but I just have an underscore in here to sort of eliminate lots of strange XML stuff.
But if I select processing and processing dash instruction with parentheses, and I, again, I just have the underscore in here for brevity. Uh, and I say for XML path, well, I get back a clicky column. All right.
And if I click this, I get the, that’s where the underscore comes in. But now I have this thing that I can, I could copy and paste out and do whatever I want with, you know, however it is. But what’s kind of funny is, uh, you see that it produced a column with this name, which is XML and then sort of a funny looking pseudo gooey looking thing. And what’s really funny is if you, uh, maybe skip the processing instruction thing.
And you just, uh, alias a column with that same name, you will get an XML clicky column. And this one doesn’t have any of the sort of festive XML bracketing on it. So maybe, maybe that’s more to your liking.
And what’s, what’s also very funny is, uh, I don’t know if you’ve ever used like set statistics XML on, but if you use set statistics XML on, it produces like an XML clickable for a query plan. And if you, the alias of set statistics XML on is Microsoft SQL Server, 2005 XML show plan. And if you alias a column with that name, you also get an XML clicky that you can click on and open, have open in a brand new tab for you.
So that’s my amusing long print, long printing tricks. All right. Perhaps I’ve gone too far.
Thank you for watching. Hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video, uh, where we will talk about, let’s see, what is it? Ah, choosing temporary objects.
Well, we’ve got some work ahead of us, don’t we? Still, I still have to talk people out of table variables and they still make all the same excuses. So I’m going to ride that train till it dies.
I guess I guess this is coming up on the 25th anniversary of people being wrong about table variables. So happy anniversary table variables. Happy anniversary.
All right. Goodbye. Bye. All right.
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.
Questions:
To ask your questions, head over here.
In this video, I dive into some interesting questions and discussions from our recent Office Hours session. We tackled a slow delete statement that was taking 44 minutes to process just 2,000 records, which led us to explore the execution plan details and suggested looking at actual rather than estimated plans for more accurate performance metrics. Additionally, I shared insights on my upcoming advanced course content, emphasizing its in-depth nature and wide breadth of advanced topics, from T-SQL to data storage strategies. The conversation also touched on career goals like becoming an MVP and the realities of query tuning in cloud environments, highlighting both the benefits and challenges companies face when optimizing their SQL Server workloads for cost savings or performance improvements.
Erik Darling here with Darling Data and we have a very, very exciting episode of Office Hours today. We are joined by absolutely no one. Who would want to stand here? Have a bad time. So, if you look down in the video description, you’ll see all sorts of helpful links where you can hire me for consulting by my training, become a Supporting With Money member of the channel, and even ask me Office Hours questions. If you enjoy this content, and you want to support me in some other way that is not giving me money, you can always like, subscribe, and of course tell a friend if you think that this channel would help them and whatever they’re dealing with in their life. Usually it’s SQL Server, but of course people find lots of solace in this channel for other reasons. If you want to see me live out in the world, the nice folks at Redgate are dragging me, well, to a few different places, at least through the end of the year. We got Dallas in September the 15th to 16th. We got Utrecht, that’s the one in the Netherlands from October 1st to 2nd. And we have Past Data Community Summit in Seattle from November 17th to the 21st, where I will be doing two wonderful days of two T-SQL pre-cons with Kendra Little.
And we do look forward to seeing you there. All right. Anyway, let’s talk about Office Hours, because we’re going to have some stuff to go through here, don’t we? We have to answer five questions, not an accidental four like last time. So that’s one, two, three, four, five. I didn’t mess that up. I can count. Wonderful. We have passed the first test of Office Hours.
All right. I have a very slow delete statement. 44 minutes for 2,000 records. All right. The execution plan shows a painful clustered index delete is the culprit. Okay. Parent and child foreign keys are indexed on two associated tables. Where else to look? Merge join is feeding problematic operator, but associated sort isn’t causing pain. What would you check next? Well, I’m going to go out on a limb here, and I’m going to say that you are looking at the estimated execution plan and not the actual execution plan.
If you’re looking at the actual execution plan, you would see helpful things like operator times, unless you’re using like SQL Server 2012 or something, in which case, well, you have bigger problems than this delete statement. So what I would suggest is to either find a place, a development server, where you can run this query and get the actual execution plan, or profile your production server in a way where you can catch this slow delete running. So, you know, just between you, me and the wall, or the camera, or the thing behind me, there are some things that you should look at first.
First, make sure that this delete statement was not just blocked for 43 minutes and 59 seconds and then finished in one second, because 2,000 records is not really where I’m going to think, oh, this makes total sense that this would take 44 minutes. So, you could look at the underlying table. Who knows?
There could be all sorts of things there, like it could be way over indexed, have like 999 indexes on it or something. I suppose it could be like a, there could be a columnstore index involved. That might slow things down a bit, but not like 44 minutes slow it down.
So, my suggestion would be to, you know, get some contextual information, make sure, like, you know, maybe fire up the block process report and make sure that this delete isn’t just getting blocked for a very long time. And then, absent a development server with sufficient data to reproduce a long-running delete, I would profile the production server in a way where I can collect the actual execution plan and not rely on very misleading cached or estimated execution plans, either from the plan cache or query store. There are various ways of doing that.
That SP human events, my store procedure, does give you that ability. So, I would maybe do that. Otherwise, you know, really just stop looking at estimated execution plans and looking at operator costs as being a good source of durable performance metrics because they are not.
All right. Next question. What topics will be covered in the advanced material of your course?
A lot of them, and they’ll all be advanced. So, there’s a lot of stuff in the beginner content, which, of course, also has an advanced component. And then there’s also sort of maybe not directly T-SQL things, but things, you know, around performance, maybe some things around various, you know, ways of storing data that has upsides and downsides, things like that.
But there’s going to be a lot in there. It’s probably too much to really list and go through here, but it’s going to be a very, very in-depth and have a wide breadth of material of an advanced nature. So, keep your eyes out for that.
I am currently, I’ve written a lot of the starting, well, not starting. I’ve written a lot of the, you know, like early modules in it. And as soon as I have like a little bit more of that done, I’m going to get that tech reviewed and start recording it.
I’m going to release the material for the advanced course a little bit differently than I did for the beginner course. I think I’m going to piecemeal it out in some smaller batches. Whereas with the beginner stuff, I really like put out a lot discontinuously over like two or three weeks until it was done.
This one, I’m going to release some sort of smaller chunks, I think. Next question. I’ve been thinking about career goals.
Really? How old are you? You’re just thinking about them? All right. Becoming an MVP sounds good. Well, if it sounds good to you, don’t, why are you asking me? I already do a lot for the community.
Good idea or bad idea? I don’t know. I would personally never do it for various reasons that I will not go into here. But it would not be something that I would consider.
But if you already do a lot for the community and you feel that your contributions are ones that Microsoft would see sufficient free marketing labor in, then you should go ahead and become an MVP. You might make some good connections.
You might meet some nice people. You might, you know, get around the world a little bit in that way. But, you know, whether it’s a good idea or not sort of depends on, you know, how humbled and honored you are to carry marketing water for a, what is it, like $250 billion a year corporation? I don’t know.
I don’t know. I don’t know. Who knows? Maybe it would have some benefit for you. Don’t let me stop you. Anyway, let’s go on to the next question. You often mention pessimistic read committed locking holding locks for too long because of key lookups with unordered prefetch.
Well, it could be an ordered prefetch too. It could just, or it could just be an optimized nested loop. Does read committed always show with unordered prefetch and plans where it holds locks for too long?
Well, it’s not holding locks for too long. It’s just holding locks for longer than most people expect and even escalating those locks in a way that causes read queries to block write queries. So, like I said, you will either see unordered prefetch, ordered prefetch, or maybe even optimized nested loops in those query plans.
But more importantly, you should generally see the queries that generated those executable plans in a block process or deadlock report. So, don’t be afraid to use those to track things down as well. All right.
This is a long one. Oh, boy. I’m going to have to think about this one while I’m reading it, which means I might read it wrong. In theory, we do love theories here at Darling Data. With the Microsoft push of all things to the cloud, it’s been going for like a solid decade now.
Not just Microsoft. Lots of people who have cloud to sell want everyone to be in the cloud. Query tuning and optimization should be a major priority for companies because CPU time is money.
It sure is. A big component of my consulting. A little free marketing for me.
Not quite a $250 billion a year corporation, but I’ll do some free work for me. A big part of my consulting is tuning stuff in the cloud so that people need less cloud and spend less money on it. In your experience, hey, there we go.
Well, I led right into that one. Are companies actually lowering costs through query tuning? Well, they are when they work with me. I don’t know about anyone else, but whatever, you know. Or are they more focused on improving speed and reducing loads?
Well, query tuning is improving speed and reducing loads, at least in my estimation. I rarely hear stories of companies reducing Azure capacities or lowering licensing costs due to query tuning, but I don’t have much exposure to it. Well, it does happen, usually shortly after, you know, hiring me.
But, you know, if you’re not really trafficking in that world, you might not see a lot of success stories around it. Now, there is a flip side to that coin, because there are people I work with who went with the absolute cheapest things they could get in the cloud and have terrible performance problems because the hardware is just so, so underprescribed for the workload they’re expecting it to run. But, like, there are Microsoft salespeople out there who don’t know this thing from the thing behind them who will just try to do anything to get people started in the cloud, and they will forecast these terribly small resources and be like, oh, but it’s only going to be this much a month.
And then people get there, and they’re like, our workload doesn’t run anymore. What happened? And they’re like, oh, no, you might have to spend more money.
So, like, but, you know, I end up in some situations where, like, you know, you do have to, you know, hard truth people and be like, look, you, like, the hardware you came from was, like, let’s say, this big, and the hardware you’re on now is, like, this big, and it’s not going well. Like, you, like, there’s no automatic performance benefit just by right of being in the cloud. You still need to, like, do things to make things faster.
So, there are some cases where you do have to, you know, tell people, like, hey, you made a bad choice with this hardware, or some Microsoft sales rep gave you bad advice on this hardware. You really do need more, at least in the meantime, until we can tune stuff, until you can maybe shrink stuff down. But a lot of people have, you know, either perhaps, you know, oversubscribed a bit, or perhaps they’re just unhappy with the cloud build generally.
And they’re like, there’s got to be some stuff we can do to fix this. And that’s where, you know, I come in a lot, and I get queries and indexes and workload various bits and bobs tuned. And then they can reduce the size of those servers without causing any major workload regressions.
So, I hope that answers your question. And perhaps even gives me a nice sales pitch. So, thank you for that.
I appreciate you. Anyway, that’s about it for me. I’m going to go do something else. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you tomorrow where we will talk about some more T-SQL learning. We’re going to talk about, I think, quote name and dynamic SQL, and maybe some other little dynamic SQL tricks that I haven’t talked about 7 million times here already.
So, all right. Cool. Thank you for watching.
Goodbye.
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.
In this video, I discuss the merits of using cursor loops over while loops in T-SQL programming, sharing my personal belief that cursor loops are often easier to write and maintain. I walk through a basic example of how to use cursor variables in SQL Server, highlighting their self-cleaning nature and demonstrating how they simplify the process compared to manually managing row increments with while loops. By comparing this approach to yesterday’s video on while loops, I illustrate how cursor loops can be more efficient both mentally and for database performance, especially when dealing with non-contiguous data sets.
All right, Erik Darling here with Darling Data, as the sign indicates. And today we are going to talk about how I think, it’s my personal belief, that writing cursor loops is much easier than writing while loops. So we’re going to talk about cursor loops in today’s video.
This is of course part of my little itty-bitty teaser bits of my Learn T-SQL course. If you would like to buy that course, hire me for training, become a supporting channel member, or even ask me office hours questions, all of those helpful links are down in the video description right below this.
So I would encourage you to click on those, should you be interested in pursuing our relationship further. And, tilly You feel so kind.
If you’re feeling so kind, you could always like, subscribe, and tell a friend about this channel. Of course, I need to leave the house a few times this year for the Pass on Tour event and, of course, for Pass Data Community Summit. I will be in Dallas September 15th and 16th, Utrecht, October 1st and 2nd, in Pass Data Community Summit in Seattle, November 17th to 21st, where I have not one but two magnificent days of pre-cons with Kendra Little all about T-SQL.
We’ll be covering some of the material that I’m going over here. So with that out of the way, let our database party begin. We will get right into our cursor loops.
Now, like I said in the intro to this video, I really do believe that once you learn how to write a cursor, it simplifies a lot of things that you might have to consider and try to be smart about when using a while loop. So I think probably the best way to write a cursor in order to simplify even that part is to use what’s called the cursor variable.
And the nice part about cursor variables is that you don’t need to close or deallocate them. They automatically are self-cleaning, which I think is nice. So what I’m going to do is just run through a basic cursor loop to just populate some data and just kind of show you like the basic fundamentals of it.
And then I’m going to compare or rather I’m going to do a demo like in yesterday’s video with while loops, where things are sort of greatly simplified for us over needing to think about how to increment and how to find what to work on next. So I’ve got my cursor stuff declared up here.
Zoom, it would be so kind as to actually zoom. We’ve got this stuff up here. And then I’m going to be using a table variable to hold some of our data because I’m just putting a single row at a time in there. I don’t really care so much about the performance of this.
So the table variable is just fine for us here. And then I’m going to set the cursor variable with the options that I want and also tell the cursor variable what the query that populates the cursor looks like. That’s this portion down here.
I talk way more about the cursor options in the full class. So if you’re curious about these, well, please consider purchasing. And then we will open the cursor.
And while that cursor is open, we will do some fetching into the cursor. And then we will, you know, populate some stuff. And what we’re going to do is what I have in here is some additional stuff to kind of show you how the cursor makes progress through all of the values and stuff.
Because what I want you to see is how the fetch status changes as the cursor finishes. And then we will be looking at the results down here. So after I finish highlighting this gigantic loopy piece of code, we’re going to run this.
We’re going to get a result back very quickly with all of the data that we populated our table variable with right here. Over in the messages tab, though, this is where we’ll see sort of how the while loop, rather the cursor loop made progress. And how this is a little bit easier than the while loop equivalent.
See, we didn’t have to tell the cursor loop what row it was going to get next. Like, we didn’t have to increment some value and then go find that value. The cursor loop is based on this query.
And this query here just goes and finds the next thing for us to do anything with. Actually, the cursor keeps track of this position for us. So we got all of the stuff that we wanted.
And of course, while the fetch status was zero, which is for all of these iterations through, we went and we got a new row to work on, worked on what we needed to do. And then at the very end of the while loop, the fetch status changed to negative one. That means that the loop killed itself and we didn’t have to tell it to do anything.
So this is how, one way how cursor loops can be a little bit simpler to write once you understand the basic syntax than writing while loops. Now, to compare the cursor loop to the while loop in yesterday’s video, there were lots of things that I had to do in yesterday’s video in order to figure out like a starting and ending position for the while loop. How to increment data, how to increment to get the next row in the while loop versus like, not like doing a naive incrementing, just like adding one to the next ID to get, having to think about how to increment things so that I was making sure that I got the next value.
So I’m going to show you how this is simplified for the cursor loop. Now, in yesterday’s video, I showed you like both a naive and then a smarter way of doing things. But in the smarter way of doing things, of course, was more efficient.
But of course, in this case, we can get more efficiency without having to worry about thinking about stuff too much, which is nice because we can now think about other things like where to go for dinner and what sort of wine to drink. And I don’t know, like where we want to go on vacation next. We’ll become more efficient people.
And so we have better things to do with our minds than figure out what to do in a while loop, right? Which is not fun. Which is not making good use of time, right? It’s not a productive use of our brains, whereas thinking about food and wine and vacations, terribly productive use of our brains.
So what we’ve got from this table is, of course, the same non-contigious situation as we had yesterday, where like the IDs that we’re working off do not just count up like one through ten or whatever, the way that the row number function does. The row number function rows are over here. So we can see that we have some big gaps in a lot of this between what number is next or not.
So what we can do to write a cursor to do this is pretty much what we did before. But I want to show you just how like we don’t really have to do as much setup for this, right? We don’t have to find a min and a max ID.
We don’t have to decide how to increment. We don’t have to like do a lot of pre-work to figure out our cursor starting place, current position, and ending place. The cursor does that for us.
So let’s run this. And I am going to show you some of the sort of guts of what happens as we run through this. And just like in the last demo, we’ll see that the cursor does a great job of, you know, while the fetch status is zero, just going and finding the exact next row that we needed based on the query we gave it to populate the cursor.
So this is all very nice. And we go through and we don’t like double work anything. And we don’t have any sort of no ops where we like maybe incremented by one and found a row that didn’t exist.
And then at the very end, after we hit 100, we have our fetch status of zero. This does double print, but it does not double work 100 here. It’s just a double printing thing that I don’t know.
I’d say I’d fix it in post, but we’re already posted. So anyway, running through the cursor loop like that, we end up with no dupes, right? Every row has a count of one.
Whereas when we did the naive looping with the while loop yesterday, we had a lot of dupes where we were just like finding where we’re either working with the same number over and over again, or we would have double worked and sort of no opt things. So cursor loops, usually much more efficient mentally and I guess in the database physically than writing while loops, especially if you do not write while loops in a way that intelligently goes through your data and finds what to work on next. So I do hope that you will perhaps spend some time learning how to write effective cursors in T-SQL.
It can be done. I know that cursors get a lot of bad press for performance and that is true. Like cursors can perform poorly when compared to sometimes set-based operations.
But even sometimes cursor loops versus while loops, cursor loops are generally on the more efficient side, at least for us as the writer and maintainer of the code. And especially on the database side, where if we make a mistake with a while loop, we could pay quite a penalty in the database. Whereas if we make a, you know, we can’t, we don’t have the ability to make that mistake with a cursor because the cursor is keeping track of things for us.
And, you know, computers, pretty darn good at keeping track of things. Anyway, that’s it for me. Thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I will see you, well, this is going out on Friday. So the next video will be office hours. And I realized in the last video that I accidentally only copied four questions. And I’ve maybe lied about answering five questions.
So, I don’t know. Should I do six questions in the next one? Or should I just do the normal five? I haven’t decided yet. I don’t want to set a bad precedent here.
Start miscounting and playing catch up and all that. Anyway, thank you for watching. All right. Goodbye. Happy Friday, too. Because you should at least get one of those in your life. All right.
Goodbye.
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.
In this video, I delve into the world of while loops and share some insights that might surprise even seasoned database professionals. We explore how using `continue` and `break` can alter a loop’s behavior in unique ways—like skipping numbers or quitting early when bored with repetitive tasks. Additionally, I discuss the importance of proper incrementing to avoid inefficiencies and ensure your loop processes each value only once. This is crucial for maintaining database performance and avoiding redundant operations. The video also touches on cursors as an alternative that can simplify loop logic and handle increments automatically, making them a valuable tool in your T-SQL arsenal. Whether you’re just starting out or looking to refine your skills, there’s always something new to learn about effectively communicating with databases.
Erik Darling here with Darling Data, and we are going to continue our T-SQL learning because that’s what we like to do around here. We like to learn about databases, and more importantly, we want to learn how to communicate more effectively with our beloved databases. They are important things in our lives, and we should be able to talk to them openly and honestly about things. So that’s what we’re going to keep doing. That jumped way too far ahead. If you like this material and you would like to buy this full training course, you remember these are just little bite-sized snippets of things. Down in the video description, there are links for all sorts of things that you can do to interact with me, like hire me for consulting, buy this training that you’re seeing little pieces of, become a supporting channel member, ask me questions for my Office Hours episodes, wow! And of course, the usual like, subscribe, tell a friend if you feel like your friend is having me, and difficulty effectively communicating with your database partners in their lives.
And maybe they could learn a thing or two over here and have healthier database relationships. The nice folks at Red Gate are putting me in front of full public display via the Pass on Tour and Pass Data Community Summit events. We’ve just wrapped up the New York event, so now we’ve got Dallas in September, the 15th and 16th, Utrecht in October, the 1st and 2nd, and Pass Data Community Summit in Seattle, November 17th to 21st, where I will have two days of T-SQL pre-cons with Ms. Kendra Little.
And that’s going to be great because then everyone in Seattle is going to know SQL and maybe that’ll help clean up some of Seattle’s well-known maladies out there. But anyway, let’s, let’s, let’s get on with our database party here and let’s, let’s go talk about while loops. Now, there are a couple of things that you can do in while loops that I learned probably a little bit later in my data database career than I feel fully comfortable saying, or fully comfortable admitting to, but that’s okay. You know what? We all learn, we all grow. It’s fine.
So the, the two things that I learned about probably, I, I, no, like continue, I, I, I learned this one, this one came along way later. I didn’t realize this was a thing until I saw someone else use it and I was like, continue, what the hell? But instead of break, I would often just use return instead, but you know, six of one, half dozen of another.
But continue is neat because continue will bring us back to the first begin in the, in a while loop, which is, I think a neat thing to be able to just say, ah, I’m done here. Let’s go back to the beginning. And of course, break will end things early. So let’s say that we have our, our task is to count to 100, but like many people in the tech world, we do not have the patience for repetitive tasks.
So I don’t know, will we get to a hundred? Probably not. Are we, are we, are we going to skip some numbers? We probably are. But this is one way that you can sort of use continue and break in a loop to, you know, make the loop behave maybe a little bit differently than just cycling through and counting from one to one. 100. For example, we can use continue to, let’s say, count up to nine or every time, or every time we get to a new 10, rather a new number that with a modulo of 10 equals zero.
We can just add on 10 and skip ahead a little bit because it gets really boring. And then maybe we might decide that, I don’t know, we’ll get to around 89 or so and we’ll, we’ll just quit. And that’s what our loop will do. Every time we hit a new 10, we’ll skip a few because we got bored.
And then when we get down to the end of our loop, we will say, oh, I got tired. Sorry, not happening for us. So these are neat things, continue and break. And also, you know, I guess return and break would be pretty similar, but you can use continue and break or return to sort of control the flow of a loop a little bit in slightly different ways.
But one thing that is important to consider when dealing with a while loop is, of course, how to increment through things so that you maybe don’t get stuck on repetitive tasks in a loop. So this is something that I talked about in one of the other videos earlier this week, where if you assume that whatever task you’re doing within the loop is working with a, like a perfectly sequential number, then you might run into situations where either you don’t find a next value or something else you do in the loop means that you just keep like you might attempt to run the loop for values that aren’t actually there.
So like if you just say, if you’re just always incrementing whatever loop, whatever is driving the loop by one, you can end up in situations where your loop is sort of ineffective or doing a lot of repetitive work in places. So we’re going to create some tables here for a while loop to work off of. And we’re going to stick some data in these tables.
And this is the somewhat boring part, but that’s okay. We have, we have, we have more interesting things to think about. So we’ve got some data in our worker table. And if we look at the what’s in the worker table and we start looking at the gaps in here, keep in mind that like the ID values that we put in, like, like they do, they are not perfectly sequential.
This goes like where it should, like, you might be expecting it to go like from like one, two, three, four, five, six, seven, eight, nine. I guess I should have gone down to 10 there. Here we’re jumping around quite a bit.
It’s like one, four, six, seven, 10, 11, 13, so this is not a perfectly sequential set of numbers that we’re working off of. This is something that you need to think about quite a bit when you’re writing while loops. And for the reasons that I said before, you don’t want to either do repetitive work or do essentially a bunch of no ops while you’re running through things.
So if we go and we run this while loop doing, let’s, let’s call it naive incrementing, where all we do is set the value plus equal one, then this will run through and do things. But it’s not really going to want, but it’s not really going to do things the way that we want. Because now if we look at what we actually processed, we will end up having with a bunch of like having processed a bunch of dupes until we found other rows, right?
Like these are all values, all of these I values are values that we ran multiple times through the loop until we found a new value. So this is not a good time, right? We ended up like processing parts of this loop either too many times, or like, you know, if we maybe there was like a no op sort of thing, we would have just like been spinning in a circle doing like no work really.
And that’s, that’s not good, right? We don’t want to write inefficient loops because loops are already sort of inefficient generally. You know, like loops and databases, usually not the first thing you want to use. They’re generally set based things you would want to use instead.
There are of course perfectly good reasons to use loops, especially to like batch modifications, but like batch units of work. But you know, we don’t always want to just jump right to loops, right? We want to make sure that we are using the loop for the right reasons and then in the right way.
So if we were to change the way that we increment the loop, and rather than just saying like to increment the loop by one, if we were to set the, if we were to drive our loop by using whatever increment value we found for the last time around, then we would have a much tighter loop that actually does like significant meaningful work on each trip through.
So rather than just add one, we’re going to add whatever increment we find in there. And this loop will have done a less work because you wouldn’t be meaning, meaninglessly spinning through things. But we would have absolutely no dupes processed in here either.
Rather than having like all bunch of fours and threes and fives and stuff. We just ended up, we just, we only process each value once. So each time through the loop, we got a brand new value set to the value that we should be using for the next turn through the loop.
So just be very, very careful when you’re doing this. In the next video, we’re going to talk about using cursors for something similar, but also how cursors do a lot to simplify loop logic. If you are writing a very, like a traditional while loop like this, you know, cursors in these cases will do just about the same performance wise.
So if you’re already doing a loop where you’re incrementing through things and doing units of work, a cursors is not really going to be worse. But cursors actually do a lot to simplify the logic required to set up and run through the loop. And to even help you not have to figure out what you need to increment to because cursors will keep track of that for you.
So there are a lot, in a lot of ways, it pays to learn how to write cursor syntax over a while loop, because the cursor syntax will actually do a lot of the thinking for you. And you don’t have to get into some of the like, you know, nuances of while loop handling and how you want to go through the while loop, because cursors will will fix that.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video about our beloved cursors. So I look forward to seeing you there. Anyway, thanks for watching.
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.
In this video, I dive into the world of control flow language in T-SQL, explaining key elements like branching, looping, and error handling. I highlight common pitfalls such as null evaluation and improper use of `go-to` labels, emphasizing the importance of proper termination to avoid unintended code execution. The discussion is part of a series aimed at helping developers navigate these often-overlooked but crucial aspects of T-SQL programming. Whether you’re a seasoned SQL Server professional or just starting out, there’s always something new to learn when it comes to mastering control flow. So, if you enjoyed this content and want more insights like this, consider becoming a member of the channel or even joining me on my upcoming tour events with Red Gate in Dallas and Utrecht. Your support helps keep the knowledge flowing!
Erik Darling here with Darling Data. Sometimes I want to say Darkwing Duck Data, but that wouldn’t make any sense. We’re going to keep going with the Learn T-SQL sort of preview material. And today we’re going to talk about some elements of control flow language. We’ll get more into exactly what all that means when we get to the video, but for now, just understand that I am controlling the flow of information and that’s how I choose to do it. So if you look at the down in the video description, there are many helpful links where you can do things like hire me for consulting, buy my training with discounts included in there, become a member, a supporting member of the channel, ask me office hours questions, and all that good stuff. And of course, if you enjoy this content, but you don’t want to give me money for anything, that’s cool too. Just do all the usual like, subscribe, tell a friend stuff. And I guess we can consider ourselves maybe even in some way. You never know, right? The bigger your reach gets, the better chance you are that someone will maybe want to give you money someday. I am going to be going on a partial tour of the world. Just finished up Pass On Tour New York City, but the fine folks at Red Gate are going to be wheeling me out for events in Dallas, September 15th to 16th, and Utrecht. That’s in the Netherlands, a beautiful little hamlet, October 1st and 2nd.
And then of course, there will be the Pass Data Community Summit taking place in Seattle, November 17th to 21st, where I have two days of T-SQL pre-cons with Ms. Kendra Little. And I do look forward to all of that. So with that out of the way, let us do our T-SQL partying here. Now, SQL Server has a variety of control flow elements in it. ZoomIt is doing this weird thing. Like, what happens down there, ZoomIt? What are you doing? Why are you messing with me? So if you want to do some branching, there’s if and else. If you want to contain certain things within a little flow of control, you have begin and end.
You have navigational stuff like a go-to label, and then you have looping elements like while loops. And then of course, we have the usual things that we can do within a loop to make or break progress. And then additional control flow stuff is also error handling.
Now, one thing worth noting here is that even though this, I’m going to cover this more when we talk about performance stuff in the advanced material, is that control flow elements don’t necessarily control which query plans are compiled at all, ever. You have to do some extra work in order for that to happen. But branching queries within code often has very, very profound effects on cardinality estimation and query plans.
So be very careful if you are the type of person who does that. Anyway, we’re going to cover just some like basic control flow stuff in here that have sort of more specific modules that we’re going to get to over the next couple of weeks. few weeks to talk about things more in depth where it makes more sense.
But one thing that I see quite a few people mess up and be caught off guard by is exactly what branching really controls once you start sort of evaluating expressions. For example, if we look at this right here, you know, like I see a lot of stored procedures start with these if things. And I’m looking at the code and I’m like, I think there’s a bug here.
And they’re like, no, no, no, it’s fine. Why? And I’m like, well, because like this will only do this first thing. And by that, I mean, like if we look at this code branch right here, we have a local variable declared. It’s an integer and it has a value of 10.
And then we say if the value is greater than five, then we print a couple of things. And if the value is less than or equal to five, then we’ll print a couple other things. But look what happens when we run this and we look at the messages tab.
We get value is greater than five. Hello world. Goodbye world. So what happened was we printed the value was greater than five. Let me move this thing.
That little highlight line can get a little annoying. So because the value was greater than five, we printed this as an immediate consequence of that. But then we also printed this. And then the second if branch, which was the value is less than or equal to five because it wasn’t, we didn’t immediately print this, but we did print goodbye world.
Right. So we like this conceptually is difficult for a lot of people. And I, you know, I do run into bugs around this sort of logic quite a bit, where if you want to contain these things, you do need to have explicit begin and end around them in order to make sure that you only print, you only follow the correct path.
And in these sort of branched logic places, you like the when you have something like this, the if will only do the immediate will only do or not do the first thing immediately after it. So in this case, it’s the print, this first print statement. So when we ran this one, it didn’t print this, but it did go and print this, which, you know, that is something that I see people struggle with quite a bit.
Another thing that people will struggle with quite a bit is null evaluation or like what to do if you hit a null. A lot of people just don’t plan for it. So if we were to run this, what SQL Server just says the value is not greater than five.
Goodbye world, which, you know, is correct. Null is not greater than five, which I agree with. That’s not where that’s not where the thing is.
It’s just that if you were to go and try to do something with a null, well, that might that might upset whatever logic you’re working with to to figure things out or whatever logic you might have to you might be like, you know, running queries for a null value ending up in there might not be a good thing. So when you’re doing this sort of stuff, it’s best to explicitly handle nulls in some way and either like stop the execution of whatever like like like either take maybe take a different branch like this sort of like a no op that just says no, this was null. We can’t do anything with null from there and move on.
You know, you could like you could maybe like use is null or something, but it wouldn’t get you the same like like handling for something like a situation where you didn’t want to work with nulls further down. So always make sure to plan for nulls in your data and handle them accordingly, even in this sort of branching logic, because, you know, this make sure you like always test with a null at some point, because if you don’t and you end up with a null, even if you swear up and down, this could never be null. Well, I don’t believe you because I’ve seen too many people be wrong about this sort of thing.
Another thing that is worth talking about here a little bit, I don’t see these used a lot, but where I do see them used in code, people often do not terminate go to logic correctly. So if we were to run this whole thing, right, which we have the local variable declared, it’s an integer set to null. And we’re going to have these different go to units in here or go to labels in here.
And I have the these returns coded out just for a little bit of convenience. But this is where each one of these things would end up. Right.
And a lot of people think that when you go to a label, that just means the end of something happening. But that’s not true at all. If we run this, look what we get back. Value is null.
Unknown world value is greater than five. Hello, world value is not greater than five. Goodbye, world. Why are we here? So if you want sort of the termination of code after you have hit a label, you really do need to add these returns in or else you will just keep on going. You will just keep on running right through code.
I think that was yeah, that was all. So if we run this, then we get the correct termination of whatever block we entered, whatever label we jumped to after that. So be careful with if branching using begin and end properly.
Always test for nulls. And if you are going to use sort of go to labels and you’re going to jump around within code, make sure that you have a return to to like the stop execution after you have done whatever you need to do within that label. Otherwise, you will end up doing probably a lot more than you bargained for.
Anyway, next up, we’re going to be talking about while loops. So I’m generally far more excited about that. But I don’t know.
These are things that I find people struggling with. So, you know, I talk about them because I don’t want you to struggle. I like you. You seem like a nice person. You know, I don’t necessarily think we need to hug, but you seem OK.
So I want you to have a nice life. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
And I will see you in the next video about while loops where we will. Well, I guess it’ll it’ll be a surprise, right? If I tell you what I’m going to do, then ruins everything.
I’m going to keep you on your toes out there. Anyway, thank you for watching.
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.
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.