asked
DMV - Page Count vs Data size
I am querying **
sys.dm\_os\_buffer\_descriptors** and am wondering whether a simple conversion from pages to MB is accurate. SELECT COUNT(*) / 128 AS MB FROM sys.dm_os_buffer_descriptors AS dobd The dmv also has a column called \[free\_space\_in\_bytes] which clearly details the amount of data on the page concerned but I am wondering whether there is ever a scenario where a page (for example) gets taken with 4000 free bytes and then later on gets updated to only have 20 free bytes or whether the page is reserved and the free space is also fixed. If the former then the calculation of `( COUNT(*) / 128.0 ) - SUM([dobd].[free_space_in_bytes])` needs to be done at the time of reference to the dmv but if not then the assumption can be that the cache commitment is simply the `COUNT(*) / 128.0 )` value. Looking forward to your thoughts and suggestions on how to prove it one way or the other...
As the page is the smallest unit of record for storage I would assume that your initial calculation is correct. The space is allocated from disk but can be populated later as inserts and updates are performed. A full page is allocated for each entry and is not available in RAM for other uses. TL;DR COUNT(*) / 128 is the amount of RAM in MB being used in the buffer pool.
To backup your thinking a bit more, here are the links to Paul Randal's survey and article. The survey clearly states this free space as "memory being wasted". As always, the article have very important and valuable information, especially how this free space could be so devastating. [Survey: how much server memory is being wasted? (code to run)][1] [Performance issues from wasted buffer pool memory][2] [1]:
http://www.sqlskills.com/BLOGS/PAUL/post/Survey-how-much-server-memory-is-being-wasted-%28code-to-run%29.aspx [2]:
http://www.sqlskills.com/BLOGS/PAUL/post/Performance-issues-from-wasted-buffer-pool-memory.aspx
9 People are following this question.
