SQL server select all rows memory performance

Question

I wrote a database migration script that joins 3 tables from SQL server 2005 and uploads the result into AWS database. The query is something like this:

SELECT a.x, b.y, c.z 
FROM Books a join Editions b on a.id = b.book_id
join BookExtras c on c.edition_id = b.id

Books, Editions and BookExtras tables have millions of rows. SQL server will load all these records in the memory and join them. The concern of some of my team mates is it will increase the memory pressure and the script will crash. My argument is OS will allocate whatever physical memory it deems sufficient for this process and we don't have to worry as the virtual memory mechanism will take care of it.

Could someone highlight pros and cons of a mega-query like the above which uses OS pages against using paging in the query itself to load only a couple of records in memory?

Answer 1

You should research how SQL Server executes queries. In this post I find a few wrong assumptions:

SQL server will load all these records in the memory

There are many physical join algorithms in use, including ones that spill to disk. SQL Server never uses OS paging under normal operations. It controls memory usage and spilling on its own.

In any case, not all data being processed must reside in memory at all times.

You can join tables of 100TB in size and SQL Server will eventually finish processing them.

millions of rows

This does not sound like a lot. A few GB of data?

The concern of some of my team mates is it will increase the memory pressure and the script will crash.

It will increase memory pressure but most likely not in a way you will notice. Default query memory is capped at 10% of SQL Server memory. It will never crash due to memory pressure. SQL Server spills to disk (not the OS).

SQL Server is made for such queries. The product is engineered to execute exactly queries like this. This is a data-warehouse style query. Totally fine.

Given that you do not fully understand how queries execute and that it is unrealistic for you to learn all of it in the short term, you should probably test the script on a development server.