How much memory is allocated when you UNION ALL?

Question

How much memory is allocated when you UNION ALL?

arza77

SSC Enthusiast

Points: 172
More actions
January 9, 2023 at 4:54 am

#4135269

If I have a query with multiple UNION ALLs, will the optimizer try and grab enough memory to run all the individual queues in one, or will it make individual allocations for the separate queries? Google has not been my friend here.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

I guess my question would be.... Why not just try it and see? Then post the answer here and Google will have a place to land the next time someone needs to know. 😀

p.s. My suspicion is that it will allocate enough memory to handle all of the parts whereas my suspicion is that it will only need to allocate enough memory to handle the largest part for a plain UNION.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

In SQL Server, the amount of memory used to store the results of a UNION ALL query is dependent on the data types and sizes of the columns being returned, as well as the number of rows being returned. The database engine will use memory to store the intermediate results of the UNION ALL operation as well as the final result set.

The amount of memory used will also depend on the available memory resources at the time the query is executed and the memory configuration of the database server. If the query requires more memory than is available, SQL Server may spill some of the intermediate results to tempdb.

It is generally a good practice to ensure that your database server has sufficient memory resources to handle the queries it needs to execute, and to monitor the server's memory usage to ensure that it is not running out of memory.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Since the memory allocation is based initially on the query, database objects, and the statistics on all those, put together through the query optimization process, you can see exactly the estimated amount of memory by simply capturing an estimated execution plan. The properties of the first operator will show the full memory allocation for the query. That gets you exactly where you want to be.

Now, that said, this may still be down to testing, as the other answers say. Because your statistics may be out of date, your query may not lend itself to statistics use, or index use, you could see estimates that are very much not in keeping with reality. Further, for some values in the filtering criteria, you may see wildly different memory needs. So, testing to be sure.

However, you can absolutely start with a good understanding of what's going to happen from that estimated plan.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Just a quick thought, UNION ALL without filtering (where clauses) will allocate enough memory for table and index scans, adding a filter / where clause will change some of those scans to seeks where, as Grant mentioned, is applicable according to the statistics.

😎

My question here is why are you asking this question, if you need to read that data, then this is the cost, no way around it?