A Little About The OUTPUT Clause In SQL Server
Video Summary
In this video, I delve into the intricacies of SQL Server’s output clause and its impact on query performance. Specifically, I explore how choosing an inappropriate target for the output data can force a query to run in a single-threaded manner, which is something I strongly dislike—parallelism is usually preferable unless there’s a compelling reason not to use it. By using the Stack Overflow database as an example, I demonstrate this issue with a table called `high question scores`, showing how returning output directly to the client or using a table variable can lead to a single-threaded execution plan. To avoid such performance pitfalls, I recommend using a temporary table (prefixed with a # symbol) instead, which allows for parallel inserts and maintains better query performance.
Full Transcript
Erik Darling here with Darling Data. And today we’re going to talk about the output clause in SQL Server in one respect. And that is how if you choose incorrectly or unwisely what you are outputting data to when you use the output clause, you can end up with a query plan that is forced to run single-threaded. If you know anything about me, there is nothing that I hate more than a query that runs single-threaded when it doesn’t have to. It’s not a good thing. Like, I’m okay with parallel plans, I’m okay with serial plans, but I’m not okay with a plan that is artificially forced to run single-threaded for no good reason. So, what I have in my stack overflow database is a table called high question scores. And all it is is a worker table that I use for dumb demos that I can modify all over the place and nothing matters with it. So the whole point of this table, right, is to populate it with the display name and highest score.
For a user for a question that they have asked. So the select query that does that work looks like this. And I’m just going to find for one user. All I care about right now is finding for Jonathan Skeet. And if I run this query, now I have accelerated database recovery turned on. So all my rollbacks are instantaneous. None of the query foibles and follies for this query are going to be due to the rollback. If you’re, well, I’m sort of standing in front of it, but I promise you this thing only ran for one second. And I just want to actually take some time to talk about how amazing it is that I nailed that to the second. All right. One second flat. All right. Look at that. It was just like 999. Is it going to make it? Can I do it? I did it. All right. And that’s cool. Most of the work in this plan is done over here. We did a clustered index thing on this side. All right. Had some parallelism in the query plan. Things went fairly well. All right.
We also rolled all that back. We don’t have data in the table. All right. This table has been written into and rolled back from a million times. So it doesn’t really matter. Now, what I’m going to show you is what happens to that query if we return output results directly to the client. So let’s run this. You’ll get the sense that this is not going to run for exactly, precisely one second.
All right. This runs for five seconds. And now we have a query that runs for 5.453 seconds single threaded. Now SQL Server is changing its tune about an index for that thing. But if we look at the properties of the plan, now this is if you’re on SQL Server 2022 or you are using some fancy Azure-ish version of SQL that Microsoft pays you a premium for the privilege of using, you will get this non-parallel plan reason. DML query returns output to client. Very descriptive. Thank you.
Thanks for letting us know about that. I appreciate you. It’s a very good reason, I guess. I’m not sure. A little… Quite frankly, my brain’s a little tarnished on that one.
So that’s not great. The other thing that will cause that is if we output to a table variable. So right now I’m going to declare a table called high question scores, which is a table variable because it starts with an at sign. Everything that starts with an at sign is a variable. I don’t care who you are, where you come from, what you do with your life.
If we run this… Oh, this also runs for five and a half seconds. Zoom it will focus.
And similar to the last query that we looked at. If we go to the properties of the root operator, we will get quite a verbose non-parallel plan reason. There we go. Table variable transactions do not support parallel nested transaction.
All right. So if you’re using the output clause and you are moving around large chunks of data, or if the query you are using to generate the output data does a significant amount of work, you may want to consider outputting into a temp table. That is the pound sign table, right? Not the at sign table, the pound sign table.
Because temp tables do not have the same restrictions on parallel inserts that table variables do. Table variables screw you up in many ways. That’s one of them. Temp tables do not have that restriction or limitation on them.
So if you are going to run some big, you know, query that, you know, grabs a bunch of data and cross a bunch of tables and aggregates it and, I don’t know, row numbers and who knows what else, and you want to return that data to a client, even if it’s only like five or ten rows that come back, if the work you do to get to those five or ten rows is significant, then you should dump it into a temp table first and just select out of the temp table at the end, because you will be able to do a parallel insert into the temp table where possible.
But at the very least, you’ll be able to have a parallel plan to get that data into the temp table. So, please do mind your output clause targets in SQL Server, because there are, as usual, landmines in everything you do. All right. Cool.
Thank you for watching. I hope you learned something. I hope you enjoyed yourselves at least a little bit. Please be kind. Like and subscribe.
I’m not even asking for a comment, just a like and a subscribe. Just a little piece of your life. Like I’ve given you a little piece of my brain and heart, liver, lungs, kidney, large intestine, small intestine, colon, pancreas, appendix. I don’t know. Whatever. I’m through with this.
I only, my copy of Operation when I was a kid was broken. It was missing most of the pieces. So human anatomy is largely a mystery to me.
Anyway, 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.