Sometimes we need to troubleshoot memory pressure issues in SQL Server or in Azure SQL. One of the things that can help in these cases is to view the contents of the buffer pool.
I wrote a simple script that displays the contents of the buffer pool in terms of tables in the current database. For each table, it presents the total table space and the space consumed by the table in the buffer pool. The script is based mainly on the sys.dm_os_buffer_descriptors dynamic management view.
This script can answer questions like:
Which table in the current database consumes the most space in the buffer pool?
What percentage of that table is in the buffer pool?
If your PLE (Page Life Expectancy) is low, and you want to know why, then this script can help in your investigation.
For example, if you find out that one of the large tables in your database consumes a large portion of the buffer pool, then you might want to search for a query that scans the table and causes PLE to drop.
You can download the script from our Madeira Toolbox.