Hi Guys,
Small query regarding SQL buffer pool-
I have assigned 12 GB max memory to SQL server. Here I am trying to fetch all the records from table size is 30 GB.
My question is how the SQL server will perform this operation. Explanation please.
It will start querying the data and reading into the buffer. It will also start streaming it down to the client. It will eventually start reusing the buffer for data that's already in the table.
It may also write temporary data to tempdb so that it can get a consistent copy of the data and then stream for there.
It's one of those things that works well so I rarely think about it.
SQL will read all the data you require it to read. After the buffer pages get full, SQL will reuse the least useful page (typically the oldest page) to read the next page.
Say you've read 12GB, loading the first page into buffer 1. When you go to read the next page after the 12GB of buffer space, SQL will reuse buffer 1 to hold the first page after 12GB.
1 Like
Thanks Graz for an explanation.
Thanks Scott.