Tempdb is also read to cache

Question

Suppose I have a session that create a temp table #table1 and insert some data and then read again, and again.
May I know I am reading from cache (buffer pool) or from disk directly ?

Answer 1

SQL Server always reads from cache during query processing, never directly from disk. Data not already in memory when needed by a query are read from storage as needed.

It is likely temp table data will remain in memory when read again, avoiding IO. Keep in mind temp tables are typically short lived and buffer cache entries are reused based on a least recently used (oldest) algorithm so actively used data will remain in cache. However, if the system is very busy and/or has little memory, the temp table buffer cache entries may have been reused and require IO for the temp table data.

Answer 2

Hi @sakuraime ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @sakuraime ,

May I know I am reading from cache (buffer pool) or from disk directly ?

Cache.

so tempdb data should flush away first when system don't have enough memory ?

If you are using tempdb, it will be the first victim,and there will be one error message. If you are using other db, there is also one error message here that will show timeout to do your command because of the memory, of cource, if there are not enough memory, you can not create a new session. For example, there are errors about not enough memory, like error 8645 and error 17189.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.