Full Frontal
Video Summary
In this video, I delve into an intriguing aspect of SQL Server query optimization by demonstrating how different database compatibility levels can influence execution plans and performance. I illustrate this concept using two databases: the master database set to compatibility level 150 (SQL Server 2019) and the stack overflow database at compatibility level 140. By running a specific query in each context, I show that the same operation yields different execution plans due to batch mode on rowstore being enabled only in the higher compatibility level. This example highlights how changing the database context can lead to more efficient query execution without altering the actual code or adding hints. I hope this video provides you with valuable insights into leveraging database compatibility levels for optimizing your queries, especially when dealing with mixed workloads across different databases.
Full Transcript
That champagne still smells wonderful. Legally wonderful. Anyway, I want to show you something kind of interesting. And you can interpret this in your own way, and you can implement this in your own way when it might suit you. And it may suit you someday. If you have certain groups of queries that work really well, when they do one thing but not really the same. really well when they do another thing. I’m going to show you exactly what I mean by that. Now, let’s start off by making very, very sure that we are all clear about what context this database, what context, what database context, that champagne may have smelt a little too good, what database context this query is taking place in. Let’s also be quite sure that we understand which compatibility levels of data. these two queries are taking place in across these two databases. The master database is in compatibility level 150 and the stack overflow database is in compatibility level 140. So master is in 2019 where we get all sorts of fancy things if we are on the most enterprising edition possible like batch mode on rowstore and well no, because scalar UDF inlining is both standard and enterprise.
So, I don’t know, there’s some other stuff in 2019 that I suppose is okay too. I’m not sure what though. Every time I install it, my computer just blue screens. I’m kidding. I’m kidding. It’s fine. It’s production ready. Go use it. Go crazy. Go crazy. And just to make extra sure, right? Like I’m not kidding with you. We are in the master database.
And yet, when I run this query to select a count of records from the post table in the stack overflow 2013 database, we get a very particular query plan. Now, if you’ve watched other videos of mine, you would know that this hash match aggregate to implement a global aggregate could only be done via stream aggregate in prior versions of SQL Server. So, with the proliferation of batch mode, this can now be a hash aggregate. We no longer have to use a stream aggregate.
So, this hash match aggregate is taking place in batch mode. That should be a vocal warm-up exercise. I’m going to tell my vocal coach about that. Hash match aggregate. So, this is a batch aggregate. As well as this clustered index scan are taking place in batch mode. So, the hash match aggregate is batched. I said that fast, didn’t I? And that’s interesting because the stack overflow 2013 database is in 140 compat level where batch mode on rowstore should not be possible.
This is, if you look at the storage. Shut up. Emergency. If you look at the storage, this is rowstore. And we’re not doing any tricky stuff like joining to another table with a clustered columnstore index it on it or something to get batch mode happening. This is a natural occurrence within the query. Right? It just happens. Right? It’s nice. It’s cool. So, great. We have this thing happening.
Now, if you come over here and we very, very clearly use the stack overflow database and we reiterate the fact that the stack overflow database is in 140 compat level and we look at masters and we only run the query in the context of the stack overflow database. And we actually get the query plan. Good job, me. We have a different execution plan. Don’t we?
We see that stream aggregate that was only, that had to be used in prior versions or, not in prior versions of SQL Server, just in the context of a rowstore only query. So, that’s that. And you may find this to be an attractive option if you have a group of, let’s say, reporting queries that you can execute from another database context that’s in compatibility level 150 against another database. Maybe that’s turned more transactional in nature in compatibility level 140 so that we don’t have to worry about hinting and changing all sorts of stuff.
We can just change the, we can just execute from a slightly different context and still get all the benefits of the optimization, the compatibility level and the optimizer abilities of the database where the query originates. So, take that as you will, implement it as you must. I hope you learned something. I hope you enjoyed this thankfully much shorter video so my champagne doesn’t get warm. Well, I don’t like the smell of warm champagne, especially on camera.
Thanks 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.
Related Posts
- Getting The Top Value Per Group With Multiple Conditions In SQL Server: Row Number vs. Cross Apply With MAX
- Getting The Top Value Per Group In SQL Server: Row Number vs. Cross Apply Performance
- UDF Inlining And String Aggregations In SQL Server 2019
- Common Table Expressions Are Useful For Rewriting Scalar Functions In SQL Server