July 4, 2021 at 4:32 am
From what I could find and read SQL Server uses memory to cache pages and to cache execution plans.
1. I know how I set the total memory used by the SQL Server.
Is it possible to set the split between the memory used to cache pages and the memory used to cache plans?
2. How does the "Cycling" / reuse of memory mechanism work?
When does the SQL server decides to drop old pages out in order to free memory for new pages? (does it simply works like Page that was not hit the longest time will be thrown first?)
If the mechanism does work, when is a correct time to use the "DBCC FREEPROCCACHE"?
Thanks in advance,
David.
July 4, 2021 at 11:51 am
As far as I know, you can not config the division of memory between plan cache and data cache. This is done by the server.
SQL server decided which memory page will be over written with different data (or cycled as you wrote) according to the cache that this page belongs to. If this is part of the data cache, then it will cycle the pages according to the LRU (Least Recently Used) algorithm. This means that pages that are used a lot, will be the last pages to be removed from cache. The plan cache has a slightly different algorithm. SQL Server takes into account how often the plan is used, but it also takes into account how hard it had to work in order to come up with this query plan. This means that we can have a situation that there is a trivial plan that is being used more then plan B, but SQL server had to go through all the stages and more CPU and memory in order to get the query plan for plan B, so it will decide the leave query plan B in the cache and overwrite the page that had the trivial plan.
Regarding DBCC FREEPROCCACHE - Have to admit that I used it only on test servers and demos:-). Don't remember that I've used it on production server.
Adi
July 4, 2021 at 7:23 pm
From what I could find and read SQL Server uses memory to cache pages and to cache execution plans.
1. I know how I set the total memory used by the SQL Server. Is it possible to set the split between the memory used to cache pages and the memory used to cache plans?
2. How does the "Cycling" / reuse of memory mechanism work? When does the SQL server decides to drop old pages out in order to free memory for new pages? (does it simply works like Page that was not hit the longest time will be thrown first?) If the mechanism does work, when is a correct time to use the "DBCC FREEPROCCACHE"?
Thanks in advance, David.
(1.) No. SQL decides how to use the memory.
(2.) Yes, SQL has an LRU algorithm that weights pages and this determines which pages to free/release.
As for DBCC FREEPROCCACHE, I'd say the correct time is never in prod. Instead, use DBCC FLUSHPROCINDB as needed.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 5, 2021 at 7:46 am
Hello all,
Thanks for all your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply