SQL Server Diagnostic Information Queries Detailed, Day 25

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.

   1: -- Breaks down buffers used by current database by object (table, index) in the buffer cache  (Query 57) (Buffer Usage)

   2: -- Note: This query could take some time on a busy instance

   3: SELECT OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id, 

   4: CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],  

   5: COUNT(*) AS [BufferCount], p.Rows AS [Row Count],

   6: p.data_compression_desc AS [Compression Type]

   7: FROM sys.allocation_units AS a WITH (NOLOCK)

   8: INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)

   9: ON a.allocation_unit_id = b.allocation_unit_id

  10: INNER JOIN sys.partitions AS p WITH (NOLOCK)

  11: ON a.container_id = p.hobt_id

  12: WHERE b.database_id = CONVERT(int,DB_ID())

  13: AND p.[object_id] > 100

  14: GROUP BY p.[object_id], p.index_id, p.data_compression_desc, p.[Rows]



  17: -- Tells you what tables and indexes are using the most memory in the buffer cache

  18: -- It can help identify possible candidates for data compression

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.

   1: -- Get Table names, row counts, and compression status for clustered index or heap  (Query 58) (Table Sizes)

   2: SELECT OBJECT_NAME(object_id) AS [ObjectName], 

   3: SUM(Rows) AS [RowCount], data_compression_desc AS [CompressionType]

   4: FROM sys.partitions WITH (NOLOCK)

   5: WHERE index_id < 2 --ignore the partitions from the non-clustered index if any

   6: AND OBJECT_NAME(object_id) NOT LIKE N'sys%'

   7: AND OBJECT_NAME(object_id) NOT LIKE N'queue_%' 

   8: AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%' 

   9: AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%'

  10: AND OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%'

  11: AND OBJECT_NAME(object_id) NOT LIKE N'filetable_updates%'

  12: AND OBJECT_NAME(object_id) NOT LIKE N'xml_index_nodes%'

  13: AND OBJECT_NAME(object_id) NOT LIKE N'sqlagent_job%'  

  14: AND OBJECT_NAME(object_id) NOT LIKE N'plan_persist%'  

  15: GROUP BY object_id, data_compression_desc



  18: -- Gives you an idea of table sizes, and possible data compression opportunities

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.