question

Fatherjack avatar image
Fatherjack 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...
dmvcache
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks, that's my thinking but wanting some backup!
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks, good find!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.