January 24, 2012 at 8:25 am
So I've read some of the recent articles on Buffer Cache Hit Ratio, memory pressure, etc for SQL, and have a question regarding the "Maximum memory utilized" for each edition. I'm not talking about the setting in SQL Server "Maximum server memory," as my understanding is that limits how much RAM a particular install can use.
So, let me state what I think "Maximum memory utilized" means, and you can tell me if I'm right / wrong / WTH am I thinking wrong. 😉
The max memory utilized indicates the maximum memory SQL server will use to cache information. So if I have SQL Express (1GB max) and a 512MB database, SQL will (potentially) cache the entire DB in memory. If I have SQL Express and a 5GB DB, SQL will cache up to 1GB of that DB in memory, swapping pages in and out based on usage (a page with "popular" data will tend to stay in the cache, a "less popular" page may get swapped out to disk) SQL may even have nothing in the cache, if there is currently no activity.
Does this sound about right? I realize from reading various articles that trying to mess around with what gets cached or not is more likely to lead to worse performance than letting SQL handle it. I also understand that I can use the "Maximum server memory" setting if I have a server with very little RAM, to keep SQL from having to fight the OS for RAM.
Thanks,
Jason A.
January 24, 2012 at 8:33 am
It's not just data cache.
SQL's memory used is the data cache, the plan cache, query workspace and a whole lot of other smaller caches that exist. The data and plan cache tend to be the largest components.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2012 at 8:46 am
Thank you!
I had forgotten about caching the various query plans, etc.
Is there any way to see what's currently in the caches, to see how much cache space query plans, etc are taking?
January 24, 2012 at 8:51 am
Sure. Take a look through all of the sys.dm_os_memory_* DMVs (there are a lot of them)
Edit: Also DBCC MemoryStatus, but that might be harder to understand
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2012 at 10:11 am
Once more, thank you!
I'm poking around on Technet / MSDN now, trying to get a feel for those counters, and how to understand them.
Jason
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply