A Little About Views, Parameters, and Local Variables in SQL Server
Video Summary
In this video, I delve into the complexities of parameters and local variables in SQL Server views that contain windowing functions. Exploring how these elements interact with query plans under different compatibility levels and settings, I provide a comprehensive overview of what to expect and how to optimize performance. I cover the nuances of simple parameterization, the challenges it poses with windowing functions, and the impact of various trace flags and database configurations. By walking through practical examples and discussing the limitations of local variables, I aim to equip viewers with the knowledge needed to navigate these tricky scenarios effectively.
Full Transcript
There are some levels of exhaustion that are just unfathomable. I’m Erik Darling with Darling Data, and in this video I’m going to talk about how parameters and actually to some extent a little, just a tiny little bit about local variables and how they’re behave with views that contain windowing functions. This is sort of a wrap-up video because I’ve talked about some of these things separately, but with the advent of SQL Server 2000-2022 and a brand new compatibility level of 160 available, there is a little bit more to talk about. And boy is my heart tired. So let’s dig right in and have some fun. Now words of wisdom about windowing functions, they are generally aided and assisted from a performance perspective. And all those P’s are really going to test if my audio is synced up with any degree of accuracy. If we create them, well let’s look at our windowing function and let’s look at our windowing function.
We are query generally. We are generating a dense rank. We are partitioning by owner user ID and ordering by score descending. Aside from those two columns that are also in the select list, we also have creation date and last activity date. And so our index for this windowing function and the query more generally is going to be keyed on owner user ID and score descending and include creation date and last activity date. I like to include when reasonably possible all of the columns in the select list to cover the query so that we don’t have to deal with SQL Server thinking about key lookups because SQL Server often seems to make counterproductive choices when it comes to doing key lookups.
Either not doing them when it comes to key lookups. Either not doing them when it would be a great idea or doing them when it is a pretty terrible idea. So we are just going to make sure that we don’t have too many choices to worry about here.
I am going to worry our pretty little heads about this one. So I am going to create this view called Pushy Paul Error Free Professional Presenter over here. And we have got query plans turned on because believe it or not I have had this script file open for a while making sure that I have a reasonable number of bases covered when I give you all of this information that you are going to forget about mere moments from now.
But let’s run this query. Let’s select from our view and we are going to use a literal value here. Now various things could trip this up.
Simple parameterization would be one of them. And the database level setting. And the database level setting.
Well, let’s backtrack a tiny little bit. The query optimizer. Well, part of the query optimizer choices. One is to add simple parameterization to a query.
That could potentially trip this up. Another is the database level setting. Force parameterization that could also trip this up. But when we use a literal value and we run this query, everything returns nice and fast.
Right now we are in compat level 160. And even though this looks like we got a simple parameterized query, we didn’t. It was attempted, but it was not actually used.
If we look at the index seek over here, we have a literal value. I don’t know what a value is. It’s not a town in Colorado.
But we have a literal value down here. We do not see a parameter down here. So simple parameterization was not successfully applied to this query, though it was attempted. Surprise, surprise.
There are weird things in query plans that no one should ever have to explain to anyone. But we do get a quick index seek. We do get an accurate cardinality estimate.
And why wouldn’t we? It’s true, right? And obviously it’s true to me. Let’s try to say, sorry,win I’m going to kill. We’re trying to see the first demo protocol right here. SoHello. If I were to stuck in and try to estimate them. I would do, umm.
I would $100, $200, $ président, $! differences in the query plan under compatibility level 150. So let’s run these and well these run we’ll say that if you were to use the global trace flag 4199 if you were to use trace flag 4199 as a query in here or you were to use the database scoped configuration query optimizer hot fixes you would not run into this in compat level 150. Now you would also not run into this in compat level 140 I’ll tell you exactly which cumulative updates that applies to towards the end of the video but if we look what happens here the top query no longer features an index seek the top query now features an index scan then a window aggregate and then a filter whereas the bottom query just features the index seek and then the window aggregate the filter is of course going to be the parameter for user ID because that is the only filtering element in the query the main message here is that when you have a windowing function in your view and you use parameters under certain conditions that I will tediously explain over and over again I’m going to feed that dead horse a full meal the local variables and parameters can’t be pushed past either the window aggregate which is just a batch mode sequence project or the row mode sequence project plan operator fun right it’s exciting it’s great stuff under compatibility level 160 that story changes a bit because there was a more recent optimizer hot fix that allows for that to happen so if we switch this to compat level 160 we will see both queries um use well slightly different execution plan but here is our sequence project and in parentheses compute scale R thank goodness it’s not just a compute scale R so that we could tell that we are projecting a sequence here because everyone who looks at a sequence project operator immediately understands that they are projecting a sequence of some sort not just computing a scale R but both of these queries now uh get index seeks even though one of them this top one has a parameter and this one here is still lying to us about uh getting a simple parameterized plan well there’s not much you can do there is there could throw a one equal select one on there to clear things up but then y’all would accuse me of cheating or something so let’s switch back to compat level 160 and uh talk a little bit about why this happens um views of course can accept a parameter when you create a view there’s no parameter list for passing into the view uh you just create the view and then you call that view from somewhere where you decide what you want to filter things on uh the optimizer uh except under conditions that we will discuss uh quite a bit in this video uh cannot push parameters past sequence project uh compute scale R or window aggregate operators which in my earpiece I’m being told that uh window aggregate operators are really just batch mode uh sequence well I forgot a word in there P-R-O-G project there we go uh it only works with uh constant values like literals right um unless you are in compat level 160 or compat level 140 or 150 with certain uh patch levels and uh database level setting settings uh query optimizer hotfixes trace flag 4199 enabled globally or trace flag 4199 enabled at the query level uh recompiling would also allow for this because it would reduce your parameter or local variable to a literal value uh another way of getting around this limitation is to create a uh inline table valued function in place of a view because inline table valued functions uh can accept parameters inline table valued functions also prevent simple parameterization and uh even under compat level 150 this works out pretty well all right there’s our inline table valued function and even if we stick that inline table valued function inside of a store procedure even in compat level 150 we will get the execution plan that we’re after where the parameter value is pushed past the sequence project compute scale r and we get an index seek rather than a full index scan that takes six seven seconds like we saw in other uh in other demos run during the video our seek predicate here is not on a literal value it is just it is on the parameter value passed into the inline table valued function so sql server 2017 cumulative update 30 uh sql server 2017 of course maxes out with compatibility level 140 uh sql server 2019 cu17 which of course maxes out at compat level 150 uh both have this query optimizer hotfix available which again trace flag 4199 uh the database scope configuration query optimizer hotfixes and of course uh sql server 2022 rtm and beyond uh without making either of any of those changes either the trace flag and either uh uh either method or the database scope configuration will fix uh the issue with parameters local variables and views without a recompile hint fantastic you don’t need to go rewrite all your views as inline table valued functions though um i don’t know maybe i don’t think that’s such a terrible idea maybe maybe i think that’s actually an okay idea uh one it fixes a lot of weird problems like this and two it gets you in the habit of writing inline table valued functions instead of crappy functions like scalar udf’s or multi-statement udf’s but uh you could fix this with uh trace flag 4199 as well do this and trace flag 4199 would address the issue good for us uh so one thing that i do want to uh uh talk about a little bit is local variables because um even though uh the uh even though the query optimizer hotfixes will address uh the the issue with not being able to push the predicate past the sequence project compute scalar or the batch mode window aggregate which is just the batch mode version of the sequence project uh they do not help improve uh none of that and it helps improve cardinality estimates at all so even we’re in compat level 160 and we run this even though no did i switch that to 160 oh no i’m i’m i see now i’m forcing compat level 150 in here that’s silly me like i said it’s all so tiresome so if we look at this query plan uh uh we are forced to again scan the whole index and apply the filter later for that for that local variable now uh not now if we uh still use compat level 150 but we enable uh trace flag 4199 we’ll get the seek plan that we’re after but even doing that we get a the really crappy local variable density vector guess there of 12 rows out of two thousand seven twenty seven thousand nine hundred and one uh i apologize for causing you pain but i just want to make sure that you all caught that on this one as well it’s only another six seconds of your life what were you doing anyway can’t even say i love you in six seconds but um we continue to get the crappy estimate here right the estimates are over here because we have to grab every row the estimates only bat at the filter where there’s actual cardinality estimation to be applied to a filtering element in the query uh uh and that also goes for uh if we hint to use compatibility level 160 we run this and even though we get the seek plan that we’re after we still get the terrible guess so even though um some some effort was made by the uh by the the nice folks who brought you dot feedback uh to fix this issue uh we still do not get uh any sort anything resembling accurate cardinality estimates from the local variable so uh i would advise you except under uh closely monitored circumstances to avoid local variables where possible um pain swelling itching redness nausea vomiting diarrhea and possibly even death have occurred when using local variables so anyway uh the larger point here is that uh microsoft does do some valuable things across uh versions of sql server of course uh and even in some cumulative and sometimes in cumulative updates of course we don’t really have much choice anymore since we we will never see another service pack again we will only ever see cumulative updates from now until forever for sql server matter maybe there’ll be a different word for it at some point later but um anyway uh the the main point here is that uh there there are some things that can be fixed by upgrading there are some things that can be fixed with trace flags and this is one of them this is a pretty good example of it uh i’ve seen performance issues related to this crop up oh i don’t know probably a dozen or so times in my time consulting so this is a good thing to keep an eye out for in query plans of course i do generally recommend keeping an eye out for filter operators in query plans because they often indicate that you have done something monstrous not always of course but they are a good thing to keep track of because uh they usually mean that uh there was some predicate that possibly could have been pushed down closer to when you touched uh and table or index uh and it wasn’t and you could have been dealing with a lot fewer rows traveling throughout uh the course of your query plan if you would apply if you were able to apply those predicates earlier on in the query plan i think uh probably the the best example of that is when someone does a left join to a table to find rows that don’t exist and they filter on where the left join to table is null those tables have to be fully joined before that filter can be applied to rule out null values even when you’re using a not nullable primary key you have to deal with that of course that situation is usually better dealt with by using not exists which can eliminate those rows uh at the anti-semi join rather than fully joining the tables and looking for nulls after the fact uh some things like windowing functions like you know if you want to run a query and look for where row number equals one i mean that’s an expression that you just have to gin up at runtime that’s not something that you can uh not something that you can really help but that’ll that would something like that would also result in a later filter operation because that value wasn’t stored anywhere where you could you know persist it index it and filter on it uh earlier in a query plan than after you have generated the row number so anyway uh i think that’s about it for this one i think that’s also about it for me today i’m gonna i don’t know i guess eat a dinner and uh try to forget the computers exist for a little while so thank you for watching uh i hope you enjoyed yourselves i hope you learned something i hope that this wasn’t too tedious for you and uh remember to uh do the old like and subscribe one two shuffle for me anyway uh thank you again for watching
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
A repeat of https://erikdarling.com/a-little-about-views-parameters-and-local-variables-in-sql-server/?
Oops. That’s what I get for group scheduling a bunch of posts after my evening primrose oil.