For Day 25 of this series, we start out with Query #57, which is Buffer Usage. This query retrieves information from the sys.allocation_units object catalog view, the sys.dm_os_buffer_descriptors dynamic management view and the sys.partitions object catalog view about buffer pool usage in the current database. Query #57 is shown in Figure 1.
Figure 1: Query #57 Buffer Usage
This query shows you which tables and indexes are using the most buffer pool space in the current database. This is very important information to understand if you are under internal memory pressure, or you are seeing high read latency for your data file(s). The query also displays the data compression status for the index.
If you see an index that is using a lot of space in the SQL Server buffer pool (because it shows up near the top of this query), then I would investigate whether SQL Server data compression might make sense for that index. What you want to look for are indexes that are highly compressible, on relatively static data, at least as far as UPDATES are concerned.
Query #58 is Table Sizes. This query retrieves information from the the sys.partitions object catalog view about the table sizes and clustered index (or heap) data compression status in the current database. Query #58 is shown in Figure 2.
Figure 2: Query #58 Table Sizes
This query simply shows you the row counts and data compression status for the clustered index or heap for each table in the current database. I use this query to look for tables that might be good candidates of SQL Server data compression. Again, what you are looking for are large tables, that are relatively static, that compress well.