By Navratan Verma
When we give a simple select from a table (eg: select * from Person.person) what happens behind the scenes is SQL Server tries to see if the required data is present in the Memory (Buffer Pool) if not the same data (page) is retrieved from the disk and copied into the SQL buffer pool and the same is presented to the user as result and cached for later use.
All the pages cached can be retrieved using the DMV sys.dm_os_buffer_descriptors applicable for SQL 2005 onwards. Download Script Here Bufferpool
Here are few examples. ( I had run few random select statements on the databases in my Test system)
This display Database wise cache size in Buffer pool.
Display Objects names and buffer size for a particular database.
These results helps us understand how much memory each database is using in the buffer pool. The second result shows the amount of memory is being used by individual indexes in the current database. This information can be helpful in case you want to implement data compression available in SQL 2008 onwards only in Enterprise Edition.