February 12, 2020 at 10:34 am
what is buffer cache , plan cache in sql?
February 12, 2020 at 12:29 pm
SQL Server doesn't manage it's memory in one giant pool. It segments the memory management into different areas with different purposes for efficiency of the management.
The plan cache is the area of memory where execution plans and data about execution plans is stored. Every Data Manipulation Language (DML) query that your run in the system gets an execution plan. Most of them, but not all (tons of caveats and discussion around this), are stored in the plan cache. Then, the second time a query is called, instead of generating a plan, one from the plan cache is used (and again, tons of caveats, discussion, etc.).
The buffer cache is an area of memory for storing data. That is data from heaps, clustered indexes, non-clustered indexes, columnstore indexes, the lot. SQL Server reads pages from disk and puts them into memory in the buffer cache. Where and when possible, it keeps those pages there. Then, when another query that needs the same page arrives, it uses that page from memory rather than going back to the disk again, since the disk is slow.
That's both in a nutshell. There are a million variations and gotchas and details in and around these. I'm sure someone will come along shortly to lecture us on an exception to the general behavior.
"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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply