Deadlock Archives | Page 15 of 138

SQL Server Performance Office Hours Episode 34

Posted on October 27, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 34

Questions:

* Why do parallel batch mode sorts only utilize 1 thread?

* With all of the recent content frequently mentioning no locks and read uncommitted isolation level, Is there ever a situation that you would recommend using nolock or is it always avoidable?

* What’s required for my query to use batch-mode on my rowstore indexes? Like what version or compat level is required? I remember a while ago, a fake join to a columnstore index was required. Are there any hints I can use?

* Hi Erik, i admire all the work you do for the community, thanks a lot! You are one of the great minds of SQL Server.

* what happened to the old sqlperformance.com website? they just suddenly stopped posting. but the authors are still around and authoring on other places on the interwebs

To ask your questions, head over here.

Video Summary

In this video, I dive into answering five user-submitted questions during my Monday office hours session. Whether you’re curious about parallel batch mode sorts or the nuances of using NOLOCK hints, there’s something for everyone. I also take a moment to thank those who support my work through likes, subscriptions, and sharing with friends—every bit helps! Additionally, I share some exciting upcoming events where you can catch me live in person, including the Redgate Pass-on-Tour in Dallas and Utrecht, as well as the Pass Data Community Summit in Seattle. If you have any questions or want to see me at one of these events, make sure to check out the links provided in the video description for more details on how to get involved.

Full Transcript

Hey, it’s me, Erik Darling here with Darling Data. And in today’s video, we are of course, it is Monday, so we do office hours and I answer five of your wonderful user submitted questions. And that’s fun. Before we do that, if you want to ask me a question for office hours, the link to do so is down in the video description. If you would like to hire me for consulting, buy my training, or become a paid channel member to support the effort that I put in the video, and then I put into all this SQL Server content for you. All of those links are available there as well. If you are uninterested in a monetary exchange with me, you can do free things that make me just glow with happiness. You can like, you can subscribe, and you can tell a friend or two or 20 or a thousand, assuming you know that many people. I’m not sure how full your Rolodex is, but… Hopefully it’s more… Hopefully it’s not just your mom. But your mom is important.

If you would like to see me out on the road, live and in person, I will be in Dallas, September 15th to 16th, and Utrecht, October 1st and 2nd. These are the pass-on-tour dates that Redgate is putting on this year. Aside from that, I also have Pass Data Community Summit in Seattle, from November 17th to 21st, where I will be doing not one, not three, but two days of T-SQL performance pre-conference. I will be doing the pass-on-t-cons with Kendra Little, and that’ll be great for everybody. Especially you. Because you’ll finally learn T-SQL, and that’ll be great. Anyway, let us office hours away here.

All right. First up, why do parallel batch mode sorts only utilize one thread? Well, I’m gonna… So there are two parts to this. One, your question is correct about one part, but parallel batch mode sorts, of course, do the actual sorting work on multiple threads. They do not do the sort on one threads. The sort only produces one thread.

The best explanation that I’ve ever seen in detail of why is in a Paul White blog post that I will put in the show notes about why batch mode sort spills are so slow in SQL Server. It has to do with the sorting algorithm that SQL Server uses internally and the complexity of producing parallel output from a batch mode sort. There is, of course, one operator in all of SQL Server that currently does support receiving parallel threads from a batch mode sort, and that is the window aggregate operator that gets used when window functions are processed in batch mode.

So there’s that. But I’ll put the link to Paul’s post in the video description and everything, because anything further that I said about why that is would be pure plagiarism, because the technical details there are best left up to the best SQL Server consultancy in all of New Zealand. Outside of New Zealand, it’s, of course, me, but within New Zealand. That’s Paul White.

All right. So next. With all of the recent content, frequently mentioning no locks and read uncommitted isolation level, is there ever a situation that you would recommend using no lock or is it always avoidable? Well, there are. So, you know, if it’s a query that you don’t particularly care about and it’s causing problems and no one seems to really care about what the results of that query are, you are free to put no lock hints on it.

You know, that would be that would be the first place. A second way of thinking about it would be a situation where. So one thing that’s interesting about the no lock hint.

is that it allows for SQL Server to use what’s called an allocation order scan, which basically just reads like data file contents in the order that the files were created. And in a nutshell, there’s there’s a little bit more to it, but, you know, I don’t have days to talk about these things, which can be faster in some situations, especially if you are reading pages from disk, I think. And so if you have a data warehouse type workload where you have a lot of data that gets loaded, say, at night or in the morning or something, and then like the data is fairly static during the day, you could use no lock hints on your big tables to sort of get encourage the optimizer to use an allocation orders order scan versus an index order scan, which can be faster.

So that would be one use case for it there. It would be very similar to if you changed your data warehouse to be read only once data loads were finished. That’s another way to achieve a similar result.

So that would be when I might use no lock. Aside from that, you know, I will judge you for using it. What’s required for my query to use batch mode on my rowstore indexes?

Like what version or compat level is required? I remember a while ago, a fake join to a columnstore index was required. Are there any hints I can use?

So there are no hints that you can use that I’m aware of. And that would extend into saying there are no supported hints that I am currently aware of that would allow that would force batch mode to occur. The fake join to a columnstore index will still get sometimes.

So the the the the the tricks you can play on SQL Server, one of them is, of course, the fake join to a columnstore index. The other would be creating a non clustered columnstore index on one of the involved tables with a filter that cannot possibly contain a row like say where ID equals one and ID equals negative one. No ID can be one and negative one at the same time.

So there would be no rows in the filtered index, but you would still have a columnstore index on the table that might encourage the optimizer to use batch mode in some places that will that will not. So like the tricks that you can use, I’ve found are not baked as deeply into the optimizer is when batch mode on rowstore happens. Like like something like some like sometimes you’ll see when you do one of the tricks, you’ll still read from tables in row mode.

But when batch mode on rowstore happens, you can read from rowstore indexes using batch mode. So there are some subtle differences that can affect how effective the batch mode is in the execution plan. But to get to your actual question, you need to be on Enterprise Edition.

It does need to be SQL Server 2019 or better. And you do need to be in compat level 150 or higher. If you have control of your queries and you meet most of those most of those requirements, you can use the option use hint.

And you can say the option use hint, the compatibility level 150 or 160 or if you’re in the cloud or something 170 in order to get a higher compat level for the query than what your database is currently set to, which can allow for additional batch mode stuff to kick in. Batch mode on rowstore itself has a bunch of heuristics. It looks at like the size of the tables, the complexity of the query, the joins and everything like that.

And it makes a runtime decision about whether to use batch mode on rowstore for your query when that happens. Like I said, you can use various tricks to encourage it, but those various tricks often do not get batch mode across the breadth of operators that batch mode on rowstore does. All right. Next up.

Hi, Eric. I admire all the work you do for the community. Thanks a lot. You are one of the great minds of SQL Server. That’s very kind of you. This is I’m going to.

This is not a question. I just want to point that out, but it is very kind of you to say that. And I appreciate I appreciate the sentiment there. So thank you. I think you are.

I think you’re also a great mind, but I’m not sure in what community you are a great mind. But I am sure someone benefits from you somewhere. So good job. All right.

Oh, here’s a fun one. What happened to the old SQL performance dot com website? They just suddenly stopped posting, but the authors are still around and authoring on other places on the interwebs. Well, this is another SolarWinds special.

You see, when SolarWinds purchased SQL Sentry and the or whatever Sentry one and the SQL Sentry monitoring tool was part of that package, of course, two things happened. Both the SQL performance site that that SQL Sentry had hosted and paid people to blog on for years suddenly stopped being a monetary priority, as did the SQL Sentry monitoring tool. So again, as I’ve said before, someone at SolarWinds should be in jail for neglect of these resources for the SQL Server community.

But in a nutshell, that’s what happened. You know, I guess the authors there were not interested in blogging there for free and all sorts of things on the site have deteriorated. Code examples are no longer formatted well.

Everything is a nightmare. I do know that Aaron Bertrand has migrated the I think if all. Well, oh, no, that was from SQL blog that he did that.

I don’t know if Aaron has migrated any of his posts from SQL performance elsewhere, but Paul White has moved all of his posts to his his personal website, SQL dot Kiwi. That’s SQL dot Kiwi. Any posts that Paul had on the SQL performance site is hosted there with a very similar URL just at the SQL dot Kiwi domain.

And I believe for some of them, he has updated the contents to reflect newer changes in SQL Server where were applicable, I suppose. Anyway, that’s about what happened there. It is a shame.

You know, that’s no longer an up to date resource for things and it’s just sort of withering on the vine. But, you know, there’s still some good stuff there from back when people cared about 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. I think can never be quite sure. I think I think that’ll happen.

But, you know, maybe maybe I’ll just get drunk and decide to retire. Who knows? That’s still early.

All right. Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Learn T-SQL With Erik: Inline Table Valued Functions

Posted on October 24, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Inline Table Valued Functions

Video Summary

In this video, I dive into the world of inline table-valued functions in SQL Server and explore why they might be a better choice than scalar or multi-statement table-valued functions for certain scenarios. I explain that these functions are essentially queries wrapped in a function call, much like views but with parameters, which can lead to more efficient execution plans due to their ability to be inlined directly into the query. I also highlight how inline table-valued functions avoid some of the performance pitfalls associated with scalar UDFs and multi-statement TTVFs, such as preventing parallel execution and automatic inlining restrictions. By walking through examples and comparing them with other types of functions, I demonstrate their potential benefits and when they might be most useful for improving query performance.

Full Transcript

I don’t want to alarm any of you, but I have taken 10 milligrams of creatine today and I can now see the future. So what I predict is that we are going to talk about inline table valued functions today. Crazy, huh? Inline table valued functions are, of course, the one kind of function that is really only as bad as the query you put in it and the indexes that you have to support that query. There are many side effects of using scalar user defined functions and multi-statement table valued functions that we talked about over the last couple of days that would hopefully dissuade you from trying to use them in any way where performance matters to your queries. Inline table valued functions really don’t have those side effects. They are just queries. They’re like views that can accept parameters. So that is an important distinction there. You know, you know, you can put, you know, you can put, you know, you can put, you know, you can put a query in there, but what people tend to put in views is a whole other matter. Inline table valued functions share that similarity where, you know, you can, you can put a query in there, but the quality of that query depends on the author. Anyway, we’re going to talk more about that in a moment.

But before we do, we know it’s pause for this commercial break where if you look down in the video description, you will find all sorts of ways that you can give me money and interact with me for free. If you would like to hire me for consulting, great. I’m available. I am a consultant. I consult. There’s probably all sorts of cool Latin words for that that I’m not aware of. You can also buy my training, performance tuning training, or if you wanted to, you could buy the full course. That encapsulates all of the material and more that we have talked about in these YouTube videos. You can also become a subscribed member of the channel where you can give me a little tiny bit of money every month just to support these videos. You can, for free, ask me office hours questions. And of course, if you enjoy this content in any way, shape, or form, you may like, subscribe, and tell not one, but all of your friends at once.

Send out a mass email and say, Hey, everyone. This is the best SQL Server channel on the internet. Wild, right? Anyway, things I am going to be doing outside of the home this year. Well, I gotta go away a little. The nice folks at Redgate were kind enough to say, Hey, Eric. I forget what they said. Anyway, I’ll be in Dallas September 15th and 16th. Utrecht, the Netherlands October 1st and 2nd.

I’ll be in the past data community summit in Seattle, November 17th to 21st, where I will have two joy-filled days of pre-cons with Kendra Little, talking about a lot of T-SQL matters and all the T-SQL that matters. I’m going to work on that pitch line a little bit. Still workshopping things.

Anyway, let’s talk about inline table-valued functions here. We must get to SSMS. So, where scalar UDFs, they would say something like return a data type here, and multi-statement table-valued functions would return a defined table variable here. This just says return table, right? And this is not a physical table.

One thing that’s important to understand about queries in SQL Server is that any query really returns tabular results. So, that’s why when you, say, use a derived table or a CTE or some other kind of table expression, including like a values clause, once you’ve sort of selected from that, you can nest that from in another from, and you can like manipulate the data or filter the data further with further froms and where’s and joins and whatnot.

So, this just returns a tabular result. Of course, saying returns tabular result is a bit too verbose for even Microsoft’s team of developers. So, it just says returns table.

But keep in mind, it’s not a physical table. It’s just a tabular result. And what we can do with these functions is get away with things that we could not maybe get away with if we were to write a scalar UDF. So, if you remember the video from a couple days ago where I talked about scalar UDFs and how things like these non-deterministic built-in functions, like sysdate, time, get date, and that whole family of things, would prevent the automatic inlining of a scalar UDF.

We do not have that restriction when we create an inline table-valued function. Why that is a restriction, I’m not sure. But what I can tell you is that this is perfectly capable of being inlined when it is written as an inline table-valued function that just returns a table.

So, if we look at the estimated plan for this. Now, if you remember, with scalar UDFs and multi-statement table UDFs that return a table variable, when we ran those queries, there was this separate execution plan like down below, right?

So, this is our non-inlinable scalar UDF. If we were to get the estimated execution plan for the inline table-valued function, which is this thing, and we were to say, give me this estimated execution plan, there’s no separate execution plan for this, right?

It’s just the body of the function is inlined into the statement itself. There are, of course, going to be differences here where we turn on actual execution plans and run these. We’re going to see that, you know, this query spent 196 milliseconds scanning this and this.

And, you know, this compute scalar now has some time assigned to it where before it didn’t. But, like, because this was inline, this compute scalar actually, like, ran a function, so there’s time assigned to it. Now, of course, to compare these a little bit further, if we come over here, this top one will, the non-inlined function has this non-parallel plan reason where the inline table-valued function does not have that.

So, inline table-valued functions do not have a couple of the properties that scalar UDFs do, which is the once-per-o execution and the prevention of parallel execution plans by the query that invokes the UDF. So, inline table-valued functions do not have some of the immediate baggage that multi-statement table-valued functions do.

And if we run, well, we already ran that query. Now, another important distinction between the two is when you have an inline table-valued function that touches data, right? So, rather than just, like, let’s just, for the sake of expediency, let’s just call this, what this thing does, processing in memory, right?

Let’s just say that, because you’re not really touching anything physically. You’re not touching any physical objects on disk or in memory, right? I mean, memory isn’t really physical.

I mean, RAM is a physical object, but the stuff in memory isn’t a physical object. But let’s just call this in memory, because all this is doing is accepting some input and doing a little bit of math on it, right? So, this is, like, a lot of just, like, CPU processing.

We don’t have to go out and, like, read data pages from a table or index and, like, bring them into memory and do anything with them there before we start handing them out to queries. This function does, though, right?

This function goes out and touches two tables that live in our database posts and comments here. So, I think I already created this function, but we’ll do it once more for good luck. So, if we look at the scalar UDF from a couple days ago that does the same thing, remember, the estimated plan will show us what happens inside the scalar UDF, but the actual execution plan will not show us what happened inside the scalar UDF, right?

So, we’re running this scalar UDF plan, and it is taking its sweet time. And now we finally have an execution plan, but all we see is that we spent about 200 milliseconds here, but then on this compute scalar, we spent nearly 10 seconds, or a little over 10 seconds, actually, because it’s 10.288 minus 0.190, right?

And, of course, SQL Server remains telling us that we must index the user’s table to make this query faster, which we know is a bunch of hokum, because what happened in here was the bulk of the execution time.

Even though it was 0% of the cost, all the time in the plan was spent here, right? 10-something seconds. But an inline table value function is, of course, a lot more honest with you about what it does.

If we just get the estimated execution plan here, we will see that we did indeed touch the post table, and we did indeed touch the comments table, right? We see all the work involved here.

And if we run this query and get the actual execution plan, we will see, not only is this query a lot faster, granted it was allowed to go parallel, which is part of the help, but even if we ran this at max stop 1, it would be faster than 10 seconds.

But, because it’s allowed to go, well, sorry, if we ran this at max stop 1, it would still be faster than 10 seconds. I’m not sure how much faster.

But we are allowed to get a fully parallel plan here, and we do get a lot more honesty about what the execution plan did along the way, touching all these tables and indexes over to the side here. So, again, inline table valued functions are just like a view that accepts a parameter.

They are really only as terrible as the query that you put in them, and the indexes that you have to support that query. Remember, queries and indexes do tend to go hand in hand in that way.

So, we always do need to consider indexing when we are considering query performance, sometimes more than others. Anyway, that’s about it for inline table valued functions.

I do recommend, if you are in need of encapsulating some code in a functional way, that you do your best to choose these over scalar or multi-statement table valued functions, because they do not have the built-in performance baggage that those two types of functions do.

The inline table valued function does not force your query to run single-threaded or necessarily execute per row. Though, if you get a nested loops join plan, it is sort of per row anyway, but not in the same sort of ugly procedural way.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. This is getting published on a Friday, so I hope you have a great weekend. And, yeah, that’s about it for me.

All right, goodbye.

Going Further

Learn T-SQL With Erik: Multi-Statement Functions

Posted on October 23, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Multi-Statement Functions

Video Summary

In this video, I delve into the world of multi-statement table-valued functions in T-SQL, explaining their structure and behavior while highlighting key differences from scalar UDFs. I explore how these functions operate within SQL Server 2017 and later versions, focusing on concepts like interleaved execution and deferred compilation. By walking through detailed examples and query plans, I aim to demystify the intricacies of multi-statement table-valued functions and their impact on performance tuning. If you’re interested in diving deeper into this topic or exploring other advanced T-SQL topics, be sure to check out my full course content available in the video description.

Full Transcript

Erik Darling here with Darling Data, finest SQL Server consultancy outside of New Zealand. I just got my BeerGut Magazine Magic Quadrant notification that I am a leader in SQL Server performance tuning consultancies. So we are proud to announce that. So, you know, we just can’t be stopped. We cannot be beat. We cannot be beaten. Even though there’s only me, we cannot be beaten. All right. Team effort. Anyway, we’re going to keep talking about T-SQL today. We’re going to expand our T-SQL horizons with, by talking about multi-statement UDFs. Multi-statement UDFs, of course, return a table variable. If you’ve been watching the content on my channel for any amount of time, you’ll know exactly what my feelings on table variables are. They are good for a few niche use cases. Aside from that, they are not of much use or value. But, you know, we find people using them in all the familiar ways and all the familiar places. And we are able to remedy that situation when we come across them. But multi-statement UDFs really have no choice but to use a table variable. So we need to talk about these today.

If you would like to buy this course, which, of course, all the videos that I’m doing here are just small nuggets and morsels from the full course content. And if you would like to buy the full course, down in the video description, where this helpful array of fingers is pointing, you can find links to buy the full training, hire me for consulting, become a contributing via money member of the channel. You can also do things that are free, like ask me office hours questions. And of course, if you if you enjoy this content, please do like subscribe and tell a friend or two. It doesn’t have to just be one. Tell one you’re not maximizing your efforts. And this MLM is never going to get off the ground. If you don’t maximize your efforts, is it? All right. Anyway, if you would like to see me out live and in person, the nice folks at Redgate have decided to pull me from my wine cellar and drag me out to various and sundry locations across the world, Dallas, September 15th to 16th, Utrecht, Netherlands, October 1st and 2nd, and Seattle, Washington, November 17th to 21st.

Quite notably, in Seattle, I will be doing two days of T-SQL pre-cons, some of it based on this material with Ms. Kendra Little, and it will be the best two days of your lives. All other days will pale in comparison to those two days. So you can, you can come see me and we can compare how pale we are together. Anyway, let’s talk about multi-statement table valued functions in T-SQL. We must go to the correct spawn of SSMS in order to engage with this material. So let’s do that. And let’s talk a little bit about multi-statement table valued functions. Now back in 2017, imagine where the time goes. SQL Server 2017. It feels like it was just yesterday. It was just one plague ago. SQL Server 2017 was around, and there was sort of a precursor feature to a later feature called interleaved execution for multi-statement table valued functions, where when you invoked a multi-statement table valued function, rather than just guessing like one row or a hundred rows, depending on which cardinality, estimation model you’re using. SQL Server would wait until the function had executed and then use table cardinality to drive, you know, some of the cardinality estimates for the rest of the query.

Not a bad start, but, you know, we still don’t get column level statistics histograms with table variables, no matter if they are used in multi-statement table valued functions, if they are used normally as declared tables, or even if they are used as table valued parameters. I suppose we should talk about table valued parameters at some point, but not today. Not today, buddy.

But the precursor, the feature that this was a precursor to was, of course, table variable deferred compilation, where SQL Server would now wait for a table variable to be fully populated, and then it would use table cardinality for the table variable in order to inform various plan choices. But again, still no statistics, still no histograms.

So even though we knew how many rows were in the table variable, we still did not have any idea of the contents of them. But this is what a pretty typical multi-statement table valued function looks like. It returns a table variable with a set definition, and that’s about it.

Between the begin and end, you can put a lot of different stuff in there. There are some usual set of restrictions on functions where you can’t do a lot of side-effecting stuff, execute store procedures, yada, yada, yada. But today we’re just going to look at a very basic single insert into a table variable and some things about the query plans.

All right. So the first one is just like scalar UDFs. If when you get, say, an estimated execution plan for a multi-statement table valued function, the query that calls it will only have some sort of hidden information about the user-defined function itself, right?

You’ll see the table valued function with the name and then a table scan where data is pulled out of the function. The query plan won’t always look exactly like this. We’ll talk about why in a moment.

But then down in the estimate, down when you call the estimated plan, you’ll also have one additional estimated execution plan for the function body itself. You can see this is where we hit the post table, do some stuff with the row numbers, some filtering, yada, yada. But up here, we did not see any direct reference to the post table.

This is all happening within the body of the function. If we look in the properties here, we will see the reason why the query plan looks like this is because we have this contains interleaved execution candidates true attribute in the plan XML. That will not be there for all multi-statement table valued functions, of course.

Some of them, we will be denied access to that feature. But when we run the query and get the actual execution plan, well, this query does take a little bit to run. But the actual execution plan just shows us this.

And this is a little funny because we don’t see any time in here at all, even though the query took about four seconds to run. If we rerun this, I believe this is where we’ll see the operator times show up. And here we’ll see 4.6 seconds here and 4.6 seconds here.

So this is a very tricky thing, is when interleaved execution is used in your multi-statement table valued function, the first execution where you gather an actual execution plan will have no operator times for it. The second execution will.

So a little bit of a tricky troubleshooting thing there. So just be very mindful of that when you are query tuning. But notice that for this, we don’t get that separate execution plan down here where we had one before. This is the exact same thing like with the scalar UDFs.

All the work is, all the things that happen inside the function are hidden inside these operators in the plan. Well, mostly this operator in the plan. But this one is where data was pulled out of the multi-statement table valued function.

We see the two rows leave here. So that’s where, like, the work is all sort of in here, but the stuff that gets pulled out is here. The table scan is, of course, just the definition of the table that we have in here.

So that’s fun. And this gets, of course, particularly tricky when we want to do something with a query that calls a UDF and joins it to something else. Where, you know, now we just have this very simple looking execution plan up here with absolutely no references to all the work that occurs in here.

Now, since this type of function at some point has to insert into a table variable, what we’re going to see is some sort of non-parallel plan reason. The non-parallel plan reason, if you are on SQL Server 22-ish bits or better, will be table variable transactions do not support parallel nested transaction, which is a nice way of saying you can’t use a parallel execution plan when you modify a table variable that includes inserts. That is true even inside of these functions.

Of course, you can’t see that when you get the actual execution plan because the query plan for the function body is neglected from being shown in there for the very similar reasons to that of the scalar UDF where for every row that this thing has to process, we do pass data in and get data out. So if we get the actual execution plan for this thing, it’ll take a little bit longer. How long?

Who knows? Who can ever tell? Right? Could be a very long time. This function doesn’t seem very quick to me. Perhaps there is some indexing work that we could do with this function to speed it up.

The world may never know. Maybe an index on the post table would help us out here. Right?

Maybe this is just too painful. Oh my God. 27 seconds. But if we go and look at this. Now notice that we did not, for this query, we don’t have that sequence operator in the query plan, which means that we do not have an interleaved execution function in here. Notice that over here, we just don’t have that, what do you call it?

We don’t have the contains interleaved candidate attribute. It’s just not there. Right?

It’s not there in saying false. It’s just not there. Now we just get this generic non-parallel plan reason. Could not generate valid parallel plan. But even though in the function body we had, like when we had the estimated plan for the function body, we could see it was because of the table variable. So now we just see that as the higher level query plan for it there.

And we can see that we spent 27 seconds inside of here. Of course, this doesn’t look like very much of a big deal in the plan itself. Like if you were to be, if you were looking at, say, an estimated execution plan or, you know, you were like, you know, something like you got it from the plan cache or from query store where the actual runtime metrics aren’t included in the plan.

You would see this 0% cost and you would think nothing of it, wouldn’t you? You would say, my goodness, we scanned this cluster. 97% of the cost is scanning the clustered index of the users table.

We have to do something about this. We must, we must remedy this. But no matter what indexing you do up here, it is not going to help the 27 seconds that we spend in here. It’s really only by getting the plan for the function body that we can make any sort of reasonable assessment of what we might be able to do in order to tune this query using the multistatement table valued function.

Sometimes it is good enough to, say, add an index to support whatever the body of the function is doing. In this case, even just an index on the owner user ID column would be enough to get us somewhere. But, you know, we’re not going to do that here.

This isn’t necessarily about query tuning. This is teaching you about the T-SQL that makes up these various modules and how they might mess with your T-SQL query plans and queries. So, multistatement table valued functions, generally, don’t want to use them.

Again, they have some niche use cases just like T-SQL scalar UDFs. But generally, one should attempt to avoid them at all costs if one cares deeply about performance. So, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video, maybe. You never know. Tomorrow’s never promised.

All that stuff. Anyway, thank you for watching. Thank you.

Going Further

Learn T-SQL With Erik: Scalar UDFs

Posted on October 22, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Scalar UDFs

Video Summary

In this video, I delve into scalar UDFs in T-SQL, discussing their potential pitfalls and how they can impact query performance. I explore the differences between non-inlinable scalar UDFs and those that are inlined, using practical examples to illustrate the hidden work these functions can do when not inlined. By walking through an example with a non-inlinable scalar UDF, I demonstrate how it executes once per row, leading to inefficiencies that can be mitigated by adding parameters or rewriting the function as an inline table-valued function. The video also includes links for those interested in my training courses and upcoming events where you can interact directly with me.

Full Transcript

I’ve been listening to a lot of Tom Petty lately. I don’t know why. Anyway, Erik Darling here with Darling Data, joined today by Bats Maru, who has some thoughts and feelings on what we’re going to talk about. We are going to, of course, be doing some more T-SQL learning. These are all little tidbits and morsels from my larger course called Learn T-SQL. I think with Erik might just be Learn T-SQL. I forget how much I involve myself in the course name. But that’s okay. There are more important things like content, accuracy, you know, polarity, things, other things that I care deeply about. But before we go talk about Scalar UDFs, if you would like to buy any of my training, including the full Learn T-SQL with me course, all these links are available down in the video description. You can find all sorts of useful ways to interact with me, both by giving me money for things and by getting things for free from me, like asking office hours questions or liking, subscribing and telling a friend. So look down and you’ll find links for all the useful stuff in the world.

If you would like to have some useful interactions with me in person, the fine folks at Redgate are yanking me from the comforts of home and dragging me to various business class hotels in Dallas, Texas, September 15th and 16th. Utrecht, Netherlands, October 1st and 2nd and Seattle, Washington, November 17th to 21st, where I will be attending various past events and talking about SQL Server stuff, including, but not limited to, two days of T-SQL pre-cons with me, hosted by myself and Kendra Little. So you should come to those because you won’t be disappointed like you would be if you went to any other pre-con.

It’s the tremendous disappointment, not just fear, but the knowledge of missing, the como, the knowledge of missing out that you would feel by going to a different pre-con would just ruin your life. You would be a complete dark downward spiral for you from that day on. You would be able to just mark it on the calendar.

Life, graph, down. If you were making a tapestry of your life, it would just be black thread from that moment onward. Frayed at the edges.

All right. Anyway, let’s talk about T-SQL. Let’s talk about something. Well, we are in dark mode here, aren’t we? Coincidence?

I don’t know. But let’s talk about scalar UDFs. Now, scalar UDFs, of course, if you have spent any amount of time in your life dealing with T-SQL and perhaps Googling, or let’s just say Googling. There’s no one using Bing.

Let’s not lie to ourselves. You will undoubtedly have come across many articles, maybe even some articles by yours truly, about the perils of scalar user-defined functions in T-SQL code. And you may have even read something added to SQL Server 2019 called scalar UDF inlining.

Of course, scalar UDF inlining has many restrictions and limitations put on it that make it so it is ineligible for certain forms of functions. If you would like to explore the full list of that, Microsoft has just about, I think, just about all of it documented. There may be some stuff that we don’t know about, we don’t see.

But, you know, look up scalar UDF inlining. You’ll see the Microsoft Learn or Docs or whatever they’re calling it these days page. And you’ll be able to see the full list of restrictions and limitations.

So what we’re going to do is, since my copy of the Stack Overflow database is currently in compatibility level 160, I am currently using SQL Server 2022. I need to play a slight trick on SQL Server in order for this function to not be eligible for scalar UDF inlining. The trick that I am playing on SQL Server is incorporating one of these non-deterministic date functions in the function body.

So sysdate time, get date, any of those things would have the same effect on disallowing inlining for this code. And what I want to show you first is the estimated execution plan for this. So when we run the estimated execution plan, when we collect the estimated execution plan, we get two things back.

We get back the query plan for the query. And then we also get back an additional query plan that shows us what the scalar UDF would have done. We will not see that with the actual execution plan for this query.

Because SQL Server does two things with non-inlineable scalar UDFs. The thing that immediately affects this is that the scalar UDF is not run once per query, since it is not inlined. It is run once per row that the query has to process.

If we look at this query plan, we’ll see an index scan, we’ll see a sort, and then we will see a compute scalar. The compute scalar is where the scalar UDF is… Rather, the compute scalar is the operator responsible for executing our scalar UDF.

You can see that right here. These are the properties of the compute scalar. And we will see the name of the UDF in there.

So that is where the UDF was executed once per row that the query projected out. All right. So we do not see the actual execution plan for the scalar UDF because it executes once per row.

And if we executed that UDF for a thousand rows, we would not want to get back 1,000 execution plans, would we? No. No siree, Bob.

The second thing that scalar UDFs will do to us is prevent a… Rather, non-inlinable scalar UDFs will do to us is prevent the calling query from using a parallel execution plan. Not that one is warranted here, but it is restricted from using one even if one were warranted.

So those are the two big downsides of non-inlinable scalar UDFs. To prove out that this UDF does, in fact, execute once per row, that query returned six rows. And we have six executions of the scalar UDF in the sys.dm exec function stats view, which only collects data about scalar UDFs.

Ah, I didn’t want that. One way that we can work around that situation with the UDF not being inlinable is rather than have sysdate time in the body of the UDF, we can just add a third parameter to the function. And the third parameter will get used here in place of the sysdate time function.

And if we do that and we pass in sysdate time from outside of the function call, then it will be perfectly inlinable. So if we run this query and look at the execution plan, this will look a little bit different. Rather than seeing the compute scalar with the scalar UDF in it, now we have some extra stuff in here.

And this is where the function was inlined into the query. All right, these constant scans and the nested loops join and stuff. This is where the function, this is where the body of the function ran, but it was inlined into the execution plan.

So now we have this little bit of additional information. If we get an estimated execution plan for this, we no longer have that second plan down here, right? We can only prove a negative so much, but there’s no second execution plan that shows us what the body of the scalar UDF would have done.

And if we go and interrogate the sys.dmexec function stats dmb, it will be empty for the inlinable UDF. All right, so that is one sort of peril of the UDF is when they are not inlinable, they hide some work for you. Of course, the work that they hide from you is only obvious if we look at the use of a function where user tables are touched.

Right, so here you can see in the body of this function, we touch the post table and we touch the comments table. But if we get, and we’ll only, rather, let’s start here. We’ll only see that if we get the estimated execution plan, right?

So like the query plan up here with the UDF in it just shows us clustered index c compute scalar select. The estimated execution plan for the function, right? This is our non-inlinable scalar UDF here.

This shows us two index scans of posts and comments, some parallelism, some stream aggregates, some compute scalars and so forth. And when we execute this query, we get the actual execution plan. We will once again not have a, we will not have the benefit of the execution plan for the UDF.

We will have absolutely no information that it went out and touched the post table and the comments table and scanned the clustered index. That will not be visible to us. All we’ll see is that we scanned the clustered index of the users table, which took zero seconds.

And then we spent 1.2 seconds in the compute scalar. And of course, the 1.2 seconds that we spent in the compute scalar was executing our user defined function once per row that came out of the original query, which for our results was just once, but that one execution took 1.2 seconds.

So think about that if we returned 10 rows, we’re looking at 10 something sec, maybe 12 seconds at that point. If we executed this 20 times, well, what’s 1.2 times 20? It’s a higher number, isn’t it?

So when scalar UDFs are a particularly nasty piece of work, if you are, you know, given the number of limitations and restrictions, we will, we will have work to do rewriting these as inline table valued functions for many years to come. So a really smart fellow named Sam Arch wrote a paper a little while back about scalar UDF inlining and how, I think it’s called like, scalar UDF inlining first start with outlining or something.

And it was actually implemented in DuckDB. And what it did was it took cases where scalar UDF inlining broke like queries completely and fixed them. So it was a very, very cool paper.

So it’s Sam Arch, A-R-C-H. If you have the wherewithal to read a white paper about query optimization, I highly suggest checking that one out. Very, very cool thing.

Anyway, scalar UDFs, you know, you can pretty much tell when someone has used one because they just return a data type like integer or date or date time or varchar max or something. And when you see these in your queries, you better hope that they’re simple and that SQL Server can rewrite them. Otherwise, you’re going to have to hire a young and handsome consultant with reasonable rates to come and rewrite them for you.

Isn’t that a shame? Isn’t that something there? All right.

Anyway, thank you for watching. Hope you enjoyed yourselves. Hope you learned something. And I will see you in tomorrow’s video. I believe we’re going to be talking about multi-statement table-valued functions. And we’re going to learn a little bit about how the T-SQL that goes into those interacts with the queries and query plans that you see presented to you.

When you go trying to tune queries and whatnot. Anyway, thank you for watching.

Going Further

A Little About ROW_NUMBER Filtering Performance in SQL Server

Posted on October 21, 2025January 23, 2026 by Erik Darling

A Little About ROW_NUMBER Filtering Performance in SQL Server

Video Summary

In this video, I dive deep into an intriguing SQL Server performance question that came up during yesterday’s office hours. Specifically, we explore why a `ROW_NUMBER() = 1` query performs differently from a `ROW_NUMBER() <= 1` query by analyzing their execution plans in detail. You'll see how the choice of operators and execution modes can significantly impact performance, with one query finishing in just over five seconds compared to another that completes in less than two seconds. I walk you through the nuances of windowing functions, batch mode vs. row mode operations, and how SQL Server optimizes these queries. Whether you're a seasoned DBA or just starting out, this video offers valuable insights into optimizing your T-SQL code for better performance.

Full Transcript

Erik Darling here with Darling Data and I’m very excited. Very excited today. Hugely excited. Probably the most excited I’ve ever been. I’m going to be answering in longer form a question from yesterday’s office hours about why row number equals one. It has a different performance than row number is less than or equal to one. And of course, we will be looking at query plans. We’ll be looking at performance and we will be answering this question in as much detail as I can muster off the top of my head. So good stuff there, right? All right. I was going to say something else funny here, now I forgot it. See, this is the problem with the top of my head or things just fly off it. It’s like frying an egg. Little bubbles. Anyway, if this sort of stuff is interesting to you and you think, gosh, this Erik Darling guy just might know a thing or two about SQL Server, maybe he could help me with SQL Server. Maybe he could do it. Maybe he can be the one to finally change my life. Well, there are all sorts of ways for me to accomplish that. And if you look down in the video description, you will find all of these ways for me to change your life. You can hire me for consulting by my training. You can hire me for consulting by my training. You can hire me for consulting by my training. You can hire me for consulting by my training. You can hire me for consulting by my training.

You can hire me for consulting by my training. You can hire me for consulting by my training. You can hire me for consulting by my training, oversee this monthly consultation, and then for free, you can ask me office hours questions. And also for free, you can like and subscribe and tell a friend about the magnificence and glory of this channel and its content. And of course, the young and handsome consultant behind the majority of the content therein. If you would like to see a young and handsome consultant live and in person, well, golly and gosh. Won’t I just be a good friend? Here’s an awesome consultant. everywhere you want to be. Dallas, September 15th and 16th. Utrecht, the Netherlands, October 1st and 2nd. And Seattle, the Washington, November 17th through the 21st. These are all past events put on by the magnificent, lovely, also glorious, also magnificent and glorious folks at Redgate who were kind enough to say, hey Eric, we like you a little bit. Why don’t you come talk about these things? And I said, yes, yes, please. Yeah, I’d love to go places, talk about things because you know, there’s only so much time one can spend trapped in a PowerPoint or in an SSMS tab. It starts to jar the brain a little bit. So with that out of the way, let’s talk about this any aggregate thing. Let’s make sure that I go to the right spawn of SSMS. Now, it took me a little, it took me a little while to get this demo to the place where there was a nice noticeable difference in performance between these two queries. And part of it was having a slightly imperfect index key to support the windowing function that we’re going to be using. So that was the first thing. The second thing was getting this index to create in a reasonable amount of time by filtering out all these zeros, because there are a lot of zeros in the comments table.

And then of course, writing the query in a way where there would be a noticeable difference in performance between a query where the row number function is filtered to equals one, and a query where the row number function is filtered to less than or equal to one. All right, so we’re going to, well, we’re not going to create this. We’re not going to go do all this stuff over again, because that’s just silly. We are going to make sure that we have actual execution plans enabled, which we do. We can tell that via the lovely purple highlighting there. That is a nice touch. I do, I do like purple. If Erin Stellato had as much to do with picking the color purple for that highlight as I think she did, my kudos go out to Erin Stellato for her choice in purples, because that is a great purple. There’s a nice, there’s like a grimace purple. And that is, that is the perfect purple for me. That is purple rain to me. So let’s run these queries. Now what these, now just to get rid of the query results, because I hate waiting for query results. And the discard query results thing is a bit too heavy handed for me, because then I have to remember to turn it on and back off and on and back off. It’s all too much. We are going to be dumping the results of these queries into different, slightly different temp tables, because we don’t want to overcrowd a single temp table.

And let’s do this and let’s look at the performance difference. We’re going to execute these and we’re going to wait, oh, around about seven seconds total, I think, if I, if I remember things correctly. There we go. All right, look at all those rows affected. See, sometimes having, having no count off is nice, right? Make sure that the same number of rows got changed. Look at that beautiful symmetry. Wow, we’re so good at this. All right. So that was indeed about seven seconds.

And here is the first query plan. Now you’ll, if you’re familiar at all with SQL Server execution plans, you will immediately recognize that for a query with a windowing function in it, we are missing some potential operators. Usually when there is a windowing function involved, you will see some combination of segment and sequence project. You may even see a window spool for some of them. And here we do not have those. Here all we have is a stream aggregate. What this means is that SQL Server transformed the row numbering into a grouping operation. So we did not actually generate a row number. We just grouped the whole thing. Now there are some rules that SQL Server follows in order for this to happen. You must filter the, I think it’s, you must filter the windowing function to an equality predicate. I think that’s one of them, but more, but other more important stuff is like you can’t be presenting the window function column and the results, right? So if I had the column X dot N in here, that’s what the windowing function is, um, is alias to, uh, then we would not see this transformation get applied. But, uh, you, you can tell from this that we just did a stream aggregate. Now this all took around, around about five and a half seconds, right? 5.356 seconds, which is close enough to five and a half for me. Part of why this query, uh, took the five and a half seconds is that this query happened entirely in row mode, right? So we processed quite a lot of rows all in row mode. We did not use batch mode for this. Part of the other signs that we use batch mode for this, rather that we did not use batch mode for this, are that we have, uh, some parallel exchanges in the plan. Often these are, uh, omitted to some degree when you have a, uh, when you have a query plan that uses batch mode, usually fully omitted. If you have a query plan that is just about fully batch mode, sometimes only partially omitted. If you have a query plan that is a mix of row mode and batch mode. Another good sign is that we have a stream aggregate operator. The stream aggregate operator, of course, does not support batch mode, right? Uh, only the hash, uh, only the hash, uh, aggregate and, uh, join support batch mode. Nested loops and merge joins also do not support batch mode. Now, if we come down here and look at the second query, the first, well, let’s, let’s, let’s actually make this a little bit easier on the eyeballs. What do you say? I say that’s a great idea. Uh, this query finished in 1.6 seconds.

Hmm. It’s a meaningful improvement, isn’t it? I would say so. From about five and a half to about one and a half. That’s, that’s pretty good, right? We saved about four seconds there from, my math is correct. And, uh, if you look at the various operators in here, you might notice that, uh, SQL Server, uh, used, you might, you might have some visual indicators that SQL Server used batch mode. And, uh, uh, the visual indicators are, well, the main, the prime visual indicator is that we have a window aggregate function, right? So the window aggregate is a batch mode operator that SQL Server uses when you have a windowing function and batch mode is chosen as the execution engine for the query.

So if we look at this, uh, the details of this operator, we will see that this executed in batch mode. We will see that this sort executed in batch mode. We will see that the window aggregate, of course, executed in batch mode and even the filter operator filtered in, uh, yeah, even the filter operator filtered in batch mode. So, uh, if you, so to answer the question from yesterday’s office hours, where, uh, why is performance different? Well, this, these are the types of things that you would want to look for in your query plans in order to ascertain why performance is different for you locally.

Like most things, the answer is in the execution plan. So, uh, you should look at them and maybe take, take note of some of the details, take note of some of the operator times. Uh, I realize that there is nothing in the query plan that’s going to tell you that your, uh, windowing function was transformed to an any aggregate, uh, by SQL Server’s query optimizer. But, uh, this is just a piece of knowledge that you will have to stick on the shelf in your brain where you store your SQL Server knowledge and come back to when you see this sort of thing happening. Anyway, I hope that answers your question in a little bit more detail. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where I reckon, I do reckon, I do hereby reckon, uh, we will talk more about learning T-SQL. So, um, hope to see you there.

Anyway, thanks for watching.

Going Further

SQL Server Performance Office Hours Episode 33

Posted on October 20, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 33

Questions:

* What are your favorite sandwich condiments?

* First off big fan. Also, Why does lt=1 on a query with row_number window function perform so different than = 1. I find its a trick that almost forces sql server to compute all row number data before doing something else like pushing down the predicate of a join if the window query is used as a join “table”. I tried in (1,null) and gt0 and lt2 without the same results. Thanks!

* If you could change something about the world, not related to Perf Tuning SQL Server, what would it be?

* What do we need to be aware of when enabling snapshot isolation in a large production DB? Should we expect any downtime? Thanks!

* I enjoyed hearing you beg a viewer to switch from DPA to SQL Sentry. Can I hear you say more about what DPA does wrong? I haven’t used it in years.

To ask your questions, head over here.

Video Summary

In this video, I dive into some fascinating questions from the community during our office hours session. We tackled topics ranging from sandwich condiments to SQL Server performance tuning and database management best practices. Specifically, we explored why less than or equal to one in a query with row number window function behaves differently compared to equals one, delved into enabling snapshot isolation in large production databases without expecting downtime, and discussed the shortcomings of DPA as a monitoring tool compared to SQL Sentry. If you have any more questions or topics you’d like me to cover, feel free to ask during our next office hours session!

Full Transcript

Erik Darling here with Darling Data, and boy oh boy do we have an exciting, exciting day for you planned. More exciting than you could imagine because we are going to, we’re going to have an office hours. We’re going to answer some questions, five of them, this many, and we’re going to, we’re going to see what we got in the old mailbag today. Before we go and answer questions, if you would like to give me money in some meaningful way, there are all sorts of helpful ways to do this. I’ll do that down in the video description below. You can hire me for consulting, buy some training, become a contributing member of the channel. Of course you can, you know, do some stuff for free too, like ask me office hours questions. That doesn’t cost anything. That is a, that is a free service. And if you enjoy any or all of what I do here, you can of course like, subscribe, and the most important thing is tell a friend. That is how communities work. That is how communities get built, isn’t it? You spread the good word. All right. Uh, I will be leaving the house a little bit over the next couple few months. Uh, well, I guess that’s, well, it is September, isn’t it? I mean, oh boy, I gotta, I gotta start working on some stuff. Uh, we have Dallas, September 15th and 16th. Uh, Utrecht, Netherlands, October 1st and 2nd, and past, and, uh, Seattle, November 17th to the 21st. These are all, uh, past events put on by the nice folks at Redgate.

Uh, and, uh, I look forward to hopefully seeing you at, uh, any or all of them. Uh, well, we’re, we’re, we’re on the subject of any’s and all’s. Uh, but with that out of the way, let’s do our office hours dance here. You know, let’s, uh, zoom on in and get things properly situated so that, uh, everyone can focus on these things correctly. All right. Uh, what are your favorite sandwich condiments? Well, this brings up an interesting question is what you define as a condiment. Uh, of course, you know, you have your traditional spreadable condiments like mustard, mayonnaise, and, uh, stuff like that. Uh, of which I, I only, I really just only have a preference towards mustard. Um, of course, my favorite sandwich would be a roast beef and Swiss with, with mustard. That’s about as good as it’s going to get. Uh, then you have your spreadables. You have your relishes and your, your, your hots and other things like that. They all have their place. Uh, well-made muffaletta sandwich is probably one of, one of the better things in life. Um, you know, my, my personal favorite sandwich is, of course, the peanut butter and onion on a, on a nice, nice, uh, potato bread. That’s, that’s about as good as things get for me.

But, you know, uh, I think, I think mustard would probably take the top, top spot there. Mustard is probably number one for me. Uh, good, solid deli mustard. We’re, we’re good to go wherever we go. Wherever we go, there we are. All right. That’s, oh, well, that was, that was a line, not a square. Thank you, Zoomit, for betraying me once again. Uh, first off, big fan. Well, that blows. Ha ha ha. Uh, why does less than or equal to one on a query with row number window function perform so different than equals one?

I find it’s a trick that almost forces SQL Server to compute all row number data before doing something else like pushing down the predicate of a join if the window query is used as a join table. I tried in one null and greater than zero and less than two without the same results. Thanks. Well, uh, so I’m not sure, uh, why, uh, other attempts at this, uh, did not work, uh, in the same way. But, uh, I, I, I, so two things here. One, uh, you, you have earned, I think you have earned yourself a full, uh, video explanation. So I’ll, uh, I’ll crack one of those out tomorrow.

But, um, really what the, the thing is, is that, um, when you use, uh, less than or equal to one, uh, you prevent SQL servers optimizer from, uh, trans using an any aggregate transformation, uh, in the query plan, uh, where you, if you use, so there, um, Paul White has, so I did a video about this, um, some time ago and, uh, I forget the title of it, of course, because, you know, there are a lot there, there are somewhere over 600 of these things. Uh, so it’s hard to remember the names of them all, you know, I love my children, but there are too many.

Uh, but, uh, Paul White also has a good post on any aggregate transformations, uh, where he talks about, uh, ways that SQL Server can take a windowing function and under certain circumstances, uh, can transform, uh, the windowing function into, uh, an aggregation rather than, uh, doing the full, uh, like segment sequence projector window aggregate, uh, typical query plan for a windowing function. So that’s probably what’s happening, but I will, I will do, uh, a demonstrative video about this tomorrow. So you have that to look forward to.

I will postpone all my other important work and do that because I care deeply about you. And, uh, you know, as, as much as I do want people to buy my training, I cannot, uh, I cannot bear to just tell you to buy it and hope that you stumble upon the correct thing. Uh, if you could change something about the world, not related to perf tuning SQL Server, what would it be?

Oh, I mean, there’s some obvious stuff isn’t there. You get rid of cancer, you know, you get rid of egg salad, you know, you Thanos snap government employees. Uh, I don’t know.

Some other, I don’t know. I think, I think, I think that would, that would just about clear everything up for me. Yeah.

Um, yeah, that about does it. I don’t know. Protein should be free. Socialized protein, I guess. Or alcohol or cigarettes.

I don’t know. One of those things. Whatever makes you happy. You get to pick one thing in life and that one thing is free for you. Everything else you have to pay extra for. All right.

Uh, what do we need to be aware of when enabling snapshot isolation in a large production database? Should we expect any downtime? No, you shouldn’t expect any downtime. Uh, enabling snapshot isolation is of course different from enabling read committed snapshot isolation in that you do not require, uh, an exclusive, exclusive access to the database.

Uh, when you enable read committed snapshot isolation, uh, it is most beneficial to use the with rollback immediate, uh, addition to the command in order to do that. But with regular snapshot isolation, uh, you do not require any such thing. Uh, there could of course be some hiccups along the way.

Um, you know, you, you might see increased CPU if you have a busy system and you start generating a lot of row versions, uh, right off the bat. Uh, if you have more of a slow moving system, you probably wouldn’t see much of that. Um, if you’ve enabled accelerated database recovery, you may see some, uh, growth in your user database.

Uh, also in the transaction logs, since the, uh, persistent version store is fully logged. Um, so you might see some transaction log growth in there. Uh, if your error, if your, uh, transactions suffer a lot of errors or, uh, aborts, or, um, um, if you have, uh, certain types of transactions that, uh, do not qualify for, uh, accelerated database recovery to work on them, uh, then you might see, uh, some additional, uh, transaction log growth in there.

Uh, but no, I wouldn’t expect any, anything like downtime. You might just see some increased usage and utilization and other stuff like that. But, uh, usually those things settle out over time.

Uh, if you leave it, leave it, if you leave things, uh, to their own devices for a bit. But of course we are working with databases and there is no such thing as a risk-free change. All of these changes that you make to your database, uh, involve trade-offs.

It’s up to you to make sure that the trade-offs that you are willing to make are fair and square for your workload. So, uh, always test in a development environment first and then beat the tar out of your development environment. Really smack it around.

All right. Last but not least here. Uh, I enjoyed hearing you beg a viewer to switch from DPA to SQL Sentry. First off, I don’t beg.

I, I instruct, I command. Uh, no, I’m kidding. Uh, I, I, I do really wish that people would, you know, step away from DPA, but, uh, that’s a personal thing. Uh, can I hear you say more about what DPA does wrong?

I haven’t used it in years. Well, you’ve had some happy years then, haven’t you? Um, so, you know, all monitoring tools probably collect roundabout the same set of information. Some, you know, more than others.

I do think SQL Sentry collects, uh, a bit more since it is very specific to SQL Server and DPA is more of a generalized database monitoring tool. Of course, you can use it on other platforms, uh, which may appeal to some people, but does not particularly appeal to me. Uh, and, uh, I think my biggest gripe with DPA is in the web interface.

It is very difficult to navigate around and isolate actual problems. Uh, you find yourself, like, you know, just twiddling around through snapshots and everything’s in a different place. And, like, it’s, it’s just very, I can, I don’t know, like, the way it displays blocking and deadlocks, it, it just doesn’t, it doesn’t work for me.

It doesn’t, like, it just doesn’t mentally jive with the way that I, the way that I prefer things. Uh, and, you know, not to say that SQL Sentry is a perfect monitoring tool either. Um, you know, certain aspects of the way that it, uh, displays, uh, blocking and deadlocks are very, you know, sort of unfortunate Vizio weirdness kind of thing.

It’s like, I don’t, like, there’s too many arrows to click through to see stuff. And, uh, you know, having the, the deadlock graph constantly at the bottom is not terribly helpful for me. Um, you know, I, so, like, the reason why, uh, I originally wrote SP Blitzlock and the reason why I wrote SP Human Events Block Viewer is because a lot of the information about blocking and deadlocking, uh, really does, um, make more sense when it’s sort of laid out in front of you rather than you having to click through a million different things.

And, like, the visual representations of things are just, like, I don’t know. They, they, they, it doesn’t, it doesn’t work well, uh, for me, for, for those things. For things like query plans and other stuff like that, it works very well.

Uh, DPA uses, uh, some, uh, strange interpretation of Justin Peeling’s, uh, HTML Plan Viewer, which, um, is, uh, woeful. I’m not saying Justin’s thing is woeful. I’m saying DPA’s weird, uh, implementation of it is quite woeful.

Um, things are mislabeled and, uh, things are not shown, uh, in the tool that can be very useful, uh, when looking at execution plans. Often you have to, you know, download whatever, uh, execution plan DPA has saved off and open it in another tool of your choosing to, uh, view the query plan with, uh, is, with more details. Uh, and of course, you know, neither of these tools capture, uh, what I would say is an actual execution plan.

SQL Sentries, Trace will capture some execution details, uh, about long running queries. So, like, sometimes you’ll be looking through, like, the sort of Plan Explorer-esque, um, uh, query plan, like, stuff when you open up a query plan in there. And, like, some of the steps in there will have, like, uh, CPU and duration and stuff assigned to them, which is helpful, but you’re still not getting, like, the actual execution plan with runtime metrics in there.

Um, you just have some additional information about, like, at the trace level, what, like, how long that query ran for and CPU and stuff. So, there are helpful things in there, but it’s still not as good as getting an actual execution plan. Um, you know, uh, I think, I don’t know, uh, yeah, just, it’s really mostly the way that, uh, DPA presents the data and the steps that you have to take to dig in further, uh, with DPA in order to, um, in order to find, uh, problematic things.

Uh, you know, be able to come to some, some reasonable conclusion about, um, what, what went wrong and what you can do to fix it. Uh, also, um, if, if there’s one thing that I, I really hate about what DPA does, uh, it’s the sort of canned advice that’s in the tool. Uh, whoever wrote that, I don’t think, knows anything about SQL Server.

It doesn’t seem like they have ever actually worked with SQL Server. Uh, it, it seems like they maybe got most of their information from very old blog posts and just sort of, uh, copied and pasted some, uh, inherited old wisdom, uh, into, into the tool, which, uh, doesn’t really make much sense these days. So, that’s, that’s about it there.

Uh, so, I don’t know. Uh, I, I am, I am quite anti-DPA as a monitoring tool. Um, you know, I, I still haven’t found anything that, uh, does as well as SQL Sentry with the ease that SQL Sentry does. Uh, and again, I do think that, uh, uh, whoever is in charge of SQL Sentry at SolarWinds, uh, should be sent to some sort of federal prison for, uh, their, their neglect and malfeasance.

Uh, and, uh, keeping that tool up to date with more modern SQL Server, uh, information that is available. But, uh, that’s just me. Anyway, uh, that’s about it for this Office Hours.

Thank you for watching. I do hope you enjoyed yourselves. I hope you learned something, and I will see you tomorrow, where I will do a full, uh, uh, full, uh, example of why you might see some of this stuff get weird. So, with that out of the way, I’m gonna go do something else.

Uh, mainly, uh, record that second video while it’s on my mind, so I don’t forget. And I will see you next time! Alright, thanks for watchin’, bye!

Going Further

Learn T-SQL With Erik: Bad Trigger Habits

Posted on October 10, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Bad Trigger Habits

Video Summary

In this video, I dive into the world of triggers in SQL Server, highlighting common pitfalls and best practices to avoid them. Starting with some practical advice, like setting your database compatibility level to 2022, I emphasize the importance of understanding how triggers can behave unexpectedly when dealing with bulk operations or external data modifications. The video then delves into specific examples, such as a trigger that incorrectly assumes single-row updates and demonstrates the issues that arise from using cursors in triggers, leading to inconsistent results. Throughout the discussion, I encourage viewers to consider more efficient methods for handling batch operations, like joining directly with the `inserted` or `deleted` tables, to ensure reliable and consistent outcomes. If you’re interested in diving deeper into T-SQL and mastering these concepts, be sure to check out my full Learn T-SQL course before the price increases!

Full Transcript

What am I doing? Recording? Really? More? Alright. Erik Darling here, Darling Data. Today we are going to do some T-SQL learning about triggers because it’s important to know about triggers. Of course, all of this content is small little itty bitty morsels from my full Learn T-SQL course. If you would like to purchase this course at the pre-sale price before the price doubles, after the advanced material is finished in tech review, you can do that down in a link in the video description. You can also do all sorts of other things. You can hire me for consulting, ask me office hours questions, contribute some money to the channel, four bucks or something. And if you don’t want to do any money stuff, you can do some nice open source free things, some FOS things with this channel, like like, subscribe and tell a friend. If you want to see me in person, or maybe, maybe not me, maybe you look at the lineup for any of these events and say, look at all these other great people. Maybe I’ll go harass them in the bathroom about what I should set MacStop to. You can catch me and all these other wonderful people who can tell you about MacStop in the bathroom at these various events. Pass on Tour in Dallas and Utrecht coming up very soon. Dallas, September 15th and 16th. Utrecht, October 1st and 2nd. Then we have Pass Data Community Summit taking place in Seattle from November 17th to 21st, where I have two, not one, but two days of T-SQL pre-cons with Kendra Little, where we will be dancing with the T-SQL stars or something. Anyway, let’s go talk about triggers. Now, the reason I bring this up, and I end up spending an inordinate amount of time dealing with client code that involves triggers in some way, shape or form, because people do all sorts of terrible, awful, no good, very bad things when they write them. And strange things happen, right? So we’re going to look at some of the terrible, awful, no good, very bad things that can happen. We can’t go fully into that, obviously, because sometimes, you know, I hit like, you know, right click modify on a trigger to like script it out. And the next thing I know, there’s like a 4000 line business application in a trigger. And I’m like, Oh, we have handled every use case under the sun here, haven’t we?

Good job. So let’s start simple. Let’s start with some things that you can do very early on to not deal with trigger problems in some sort of terrible way. Okay, I need to put my database back into 2022 compatibility mode, don’t don’t I stack overflow 29 set compatibility level equals 160. There we go. I guess I was working on something else in the meantime. Let’s go try this again. There we go. Beautiful. We have 255 rows in our table. Don’t tell anyone that happened. Everything else that I do is much more professional. And now we are going to look at a trigger with a very bad bug in it. This is something that I see when people make funny assumptions. Like I’m only ever going to like do something to one row at a time could be insert could be update could be delete. And they later find out someone maybe worked around the application and did their own insert or update or delete. And their trigger malfunctioned.

malfunctioned. So the main thing here is that this trigger makes that makes the bad assumption that we will only ever be able to update one row at a time, let’s say that we all of our updates usually in the application anyway, generally work off a primary key. But of course, what do we have? What do we have? And whenever we have a database in an application, we have people who use the database outside of the application. or people who want to special access to do their own weird little things in there. And what happens when we let users use a database? Hell breaks loose, right? Never let users in a database the first rule of database administration, make it so awful and painful for them to even get access to the database that they give up, right? Easiest way to have a quiet database. No users. It’s a good way to not have a job or money, but great way to have a nice quiet weekend. So our trigger does the bad thing and says we’re only going to get one ID from this inserted table. Let’s just update the one thing that we get it from that, right? So our update is based on whatever ID we pull out of inserted.

Now, if we update all of the rows in our table, we will of course, you know, update 255 rows, right? We say 255 rows affected. So good for us. We did that. But only one row actually gets hit by the trigger, right? If we look at the results of this, all of the data that we loaded in from all this down is still looking at from the year 0001, but only that first row got updated to the end of time, right? So that was what we did here. We said you all changed to that, but in the trigger we say you changed to that. And guess what? One row got affected.

So obviously that’s not good. That is not how we want things to go when we run our update. Another wacky weirdy thing that I see people do in triggers is say, you know, I’d like a loop to run in my trigger. I would like to take each one row at a time out of the inserted or deleted virtual tables and I would like to do something with that one row.

So why they do that? Lord knows, but I’ve seen it many times. So I’m going to use a cursor in this case. It could be a while loop. It could be any other type of loop you want to write. I’m just using a cursor because I like writing cursors. Deal with it. And the cursor has nothing to do… Well, I mean, the cursor doesn’t have anything directly to do with like a performance problem.

And actually the cursor is so fast that I have to put a small wait for delay in at the end of the trigger to just to simulate a very small weight. So we can see what happens when we process things in a trigger based… Sorry, in a cursor based trigger. Right. And the reason why this is a big deal for us when we’re looking at this example is because if we were to update all of these rows at the same time, we would expect all of these rows to get sort of like, let’s say that we were having, we had like a last modified column or something.

We might expect the last modified column to have like a standard time for everything, which it’s not going to have if we do this. Right. So if we have a trigger go through, sorry, a cursor go through and do things in a loop, we’re not going to get the time that we want from all this. So if I go and I update all of the rows in the table and this will take a second because that cursor has that two millisecond delay.

So we have to wait for a few milliseconds every time the cursor runs and we go look at the result of this. We’ll see that there is a little bit of a gap between all of these right now. So if that cursor were doing anything more intense or advanced and there were a, we like if I, or if I cranked up the wait for on that, I don’t have the wait for cranked up on this.

Cause I don’t want to spend a lot of time waiting for it to finish and for us to get something out of it. But you can see that all of these numbers ended up different. Right. So we, we like, like we, we ended up with a very sort of inconsistent update output from this.

And this is probably not what we want. So what you’re much, much better off doing whenever you are dealing with triggers is really to just join to the either inserted or deleted tables. Right. So we can join, but you know, okay. We can use exists too, right? Cause all we need to know if a row is there or not, we don’t need to know if every row matching is there or not. Right.

We just need to, is it there? Yeah, cool. Got it. We don’t need a one to many relationship for this to work. So if we do this and we just base our update off of some sort of correlation to the inserted table, we will end up with a much more reliable final sort of date on that. So if I update this now and we look at the results here, we will get back a consistent number across all of these, which is probably much more what you would expect.

And if you are doing any sort of auditing on a last modified thing, you would probably want to find, you would probably want to know if things were all like all done sort of like in a batch. Right. Because that would be important to know. Cause that might, if something is done in a batch, that might mean that someone went outside of your application and did something naughty or nauseous or something that they maybe shouldn’t have been doing, like changing lottery numbers or I don’t know, bills or money things, you know, just all sorts of unsavory stuff thing.

And then thinking, oh yeah, of course I placed a bet on someone to do something. And they did that thing when no one expected them to do that thing. Crazy.

All right. Anyway, that’s about it for triggers. Of course, there’s more in the course. So this, this sort of, this sort of thing interests you. It would behoove you to purchase the full course and get the full spate of knowledge from it.

Wouldn’t it? Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video. Uh, where we will, I think, I think we start talking about, is it functions?

Maybe it’s functions. If it’s not, then I’m sorry for lying to you. Should have, I should, I should, I should edit this video later. Maybe like an unsavory batch update.

Anyway, goodbye.

Going Further

Learn T-SQL With Erik: Indexed View Ups and Downs

Posted on October 9, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Indexed View Ups and Downs

Video Summary

In this video, I delve into the intricacies of indexed views in SQL Server, exploring both their advantages and limitations. I discuss how indexed views can significantly improve query performance when used correctly but highlight that they come with a set of constraints, such as restrictions on certain functions and clauses. I also cover practical examples and best practices for creating and maintaining indexed views, including the importance of schema binding, using count big in grouping elements, and understanding the implications of indexed view maintenance. Additionally, I provide insights into when SQL Server matches or fails to match queries with indexed views, emphasizing the role of hints like NOEXPAND and the necessity of statistics on these views for accurate query optimization.

Full Transcript

…things work better when you turn the microphone on. I’m Erik Darling with Darling Data, nothing if not professional recorders. Today we’re going to talk about more T-SQL learning, where we’re going to talk about indexed view ups and downs.

This is, of course, bite-sized portions, little morsel-sized portions of my full T-SQL course, which, if you go down into the video description, there is a link to buy at the pre-sale price. Well, the advanced material is polished up and tech-reviewed.

So, you can buy it for half price now or full price later. I can tell you which one I prefer, but… If we pit what I prefer versus what you prefer, you would probably end up just buying it now.

You can also do all sorts of other stuff with the links. You can hire me for consulting, become a contributing with money member of this channel, not with content. You can ask me office hours questions for free, right?

Ask me privately, I answer publicly, we all have a nice time. And if you don’t want to do any of that stuff with me, you can do the usual like, subscribe, tell a friend, yadda yadda yadda. Of course, I will be leaving the house a little bit, way more often than I usually do.

I’m going to Dallas, September 15th and 16th. I’m going to Utrecht, Netherlands, October 1st and 2nd. And of course, I’m going to Seattle, November 17th to 21st.

These all have something in common. And they are all pass related events. The two in Dallas and Netherlands are of course, Utrecht rather, are pass on tour events. Those are little mini events.

And of course, pass data community summit is the big one. At the big one in Seattle, I have two days of T-SQL pre-cons with Kendra Little, where we will light up the stage in your lives and your minds with T-SQL goodies. I lost my train of thought on that one.

Anyway, let’s talk about indexed views here a little bit. And let’s figure out the right SSMS spawn to open and let’s get into things. So teaching beginners about indexed views is a little bit difficult, mostly because once you’ve talked, once you’ve talked through all the limitations and once you’ve talked through all the sort of, you know, weird stuff between standard edition and enterprise edition, you’re left with people who are just completely blank eyed and slack jawed.

And it’s like, what is the point of any of this doesn’t make my life easy. How long has this feature been on this V1? Happened, right?

You got fabric. Oh, God. Regret. Anyway, so there’s some stuff that you can’t do in indexed views that has work arounds, right? Like you can’t use the average function directly, but you can do a sum divided by a count big.

All right. You can’t do a sum on a nullable column, but you can use is null on the nullable column to replace nulls with zeros. So you can get around that.

And you can’t do distinct, but you can group by, you know, any columns that you need to and have that get matched. So that’s all cool. Right? Great.

Here’s the stuff you can’t do. And to me, this is like a few of these are like table stakes, right? Like min and max. Like, how dare you? It’s like 99% of why I’d want a filtered or why I’d want an index view is min and max. Right? Aggregates.

How do we live? We can’t use window functions. So that includes anything with an over clause. We can’t use table expressions. That means common derived, anything like that.

We can’t use sub queries. This includes exists and not exists. Why you cannot use exists and not exists. I don’t know.

That’s a bit strange to me. You can do all the, I mean, you can do all the inner joins you want, but exists and not exists. Don’t work. Order by. I guess that makes sense. That would be a little weird.

Top and offset fetch. I also probably get that. No outer joins. That means left, right or full. You cannot use cross apply or outer apply. That is befuddling in ways, but okay.

No inline or multi-statement functions. I guess that makes sense. Inline actually probably doesn’t make a lot of sense, but whatever. No pivot, unpivot.

Having union, union all or accept or intercept. You know, I guess, I guess some of that, I mean, I’m not going to say it makes sense, but I will say, I could live without this stuff. Right.

The other stuff, some of this other stuff is like, we got fabric. Okay. Okay. So this list usually kills off most of the bright ideas people have. Especially once you take out min, max and any form of outer join, the use and utility of index views in SQL Server gets thrown directly out the window.

So then once you throw in the fact that all of your client options need to exactly match this magical incantation of things in order to match automatically with index views. The blank eyed and slack jawed developers. obrigadosellers.

Bears forget the functions… карт you to always view of that. Yeah.

Blackysle. Study does increase! Hey, well, you know what, you know what, I mean the limit it is essentially and send them to your exact object or just like this, my gosh that DM lid wascend to text. Let the survey just tell us that, you know. Even if I would remember this, you know what, I’d say to all ask us about these identifiers, but where are my mirasins?

Maybe you’d have to tell us our time I’m going to do that. So let’s create an index view that works, right? We’re going to create our alter this view. We got fabric.

Great. So now we have an index view. So stuff that you need to have in an index view, if you are going to, well, if you’re going to have an index view, it must be created with schema binding. That’s a pretty easy rule to follow.

And if you are going to have any sort of grouping elements in your index view, then you must have a count big in there. There are good reasons for this that the wording for always escapes me when I have to talk about it, but I’ll have dreams about it sometimes.

Now, because we have an index view properly, or rather we have a view set up with all of the required bibs and bobs necessary, we have done the incantations.

In order to index it, we are now free to do that. We can create a unique clustered index, and we can create a nonclustered index on our indexed view. And this all happens pretty quickly.

Now, a lot of people, when you’re talking about indexed views, will get, you know, worked up about indexed view maintenance. Ideal world, under, you know, most circumstances, you will be lucky enough that your indexed view maintenance will not be any more expensive than the maintenance of maybe an additional nonclustered index on the table.

This does change a little bit if you get any bright ideas about joining tables together in your indexed views, because then you start to get into stranger locking scenarios. But anyway, move on a little bit.

In some cases, a SQL Server can match some expression that you write to an indexed view, right? Let’s make sure actual execution plans are enabled here.

And if we run this, we will see that we do indeed do a seek into our indexed view, right? Look at us go. We have indexed view matched.

We did a good job there. We are using our post scores index view. That’s this thing. And we have hit the nonclustered index on total score in that index view. So good job, us.

100%. If we were to run this query and have it not work, one way that I can show you what it would look like, because, like, getting indexed views to not match is, like, I would have to, like, do something obviously stupid.

But, like, sometimes you might find yourself writing a query that you would expect to match the indexed view, and the query plan will end up looking like you just touched the base table anyway, which would be the case here.

Now, this happened because I used the option expand views thing. Little weird that that’s a thing, but okay, you know, we expanded our indexed view and got a worse query plan, right?

We had to do that whole calculation where we have that calculation basically stored in the indexed view. So, like, why would we want to expand that here? I don’t know, but we have the option.

I can show you that sometimes SQL Server will not adhere to our indexed view requests. And one thing that is important, and this is something that you will almost definitely need in standard edition, is the no expand hint.

So, just tell SQL Server you are not under any circumstances allowed to expand the indexed view, and we will indeed get usage of the indexed view here. So, we, you know, for a query that maybe SQL Server will be like, no, no, no, no, no, this would be too expensive.

We can, I’m going to expand this view and do the other thing. You can override SQL Server’s, you can override SQL Server’s thought process on that and say, no, thank you, no expand.

Please no. So, the no expand hint will work there. Now, but one thing that is important, whether you are on standard edition or enterprise edition, is that unless you use the no expand hint, SQL Server will not create or use statistics on your indexed views.

If you might get bad estimates, and you might even see missing statistics warnings if you don’t use it.

So, you know, at least maybe like, you know, when you start querying your index views, you might want to throw something on there with a no expand hint. At least get statistics created on that.

And then, you know, don’t forget to update your statistics because that’s usually a good thing, right? Usually. Stuff we can get away from. Stuff we can get away with sometimes.

Sometimes. But, anyway. We got fabric. Cool. So, 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 have some equally interesting T-SQL stuff to talk about.

All right. Cool. Thank you and goodbye. Bye.

Going Further

Learn T-SQL With Erik: Views and TOP 100 PERCENT

Posted on October 8, 2025September 21, 2025 by Erik Darling

Learn T-SQL With Erik: Views and TOP 100 PERCENT

Going Further

SQL Server Performance Office Hours Episode 32

Posted on October 7, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 32

Questions:

I’ve just installed SQL Sentry. Any advice?
Order By NEWID() will impact on Query Execution on large data table?
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? The plans have the database name in them but not sure if that’s a problem.
When you modify a stored 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?
Will you do a jig or a goofy dance. Feel free to have fun with your green screen with this one

To ask your questions, head over here.

Video Summary

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!

Full Transcript

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.