Important Notice: Our web hosting provider recently started charging us for additional visits, which was unexpected. In response, we're seeking donations. Depending on the situation, we may explore different monetization options for our Community and Expert Contributors. It's crucial to provide more returns for their expertise and offer more Expert Validated Answers or AI Validated Answers. Learn more about our hosting issue here.

How does the optimizer determine which buffer pool it will use to read pages into cache?

April 26, 2017buffer cache determine optimizer pages pool read

0

Posted

How does the optimizer determine which buffer pool it will use to read pages into cache?

1 Answer

0

Posted

It has to do with how many pages the optimizer expects to read and whether they will be in order. So a query that uses a non- clustered index to find only a few rows probably will use the 2K pool. A query like select *, which does a table scan, will probably try to use the largest pool available.