View buffer pool Data Density

We all know that the data accessed by SQL Server is stored in the buffer pool, but do you know the data density used in the buffer pool? If the density of data to be queried in the buffer pool is small, it means that a lot of useless memory is occupied, resulting in a waste of memory. So how can we view the data density in the buffer pool?

 

In SQL server2005, SYS. dm_ OS _buffer_descriptors DMV is provided. You can use this view to query information of all data pages in the SQL Server Buffer Pool. The free_space_in_bytes field tracks the free space on each page, and summarizes all the free pages to obtain the data density.

 

The following script can view the buffer pool space occupied by each database and the memory space occupied by NULL data.

 

Select

(Casewhen ([database_id] = 32767)

Then 'Resource database'

Else db_name ([database_id]) End)
[Databasename],

Count (*) * 8/1024as [mbused],

Sum (cast ([free_space_in_bytes] asbigint)/(1024 *
(1024) as [mbempty]

From SYS. dm_ OS _buffer_descriptors

Group by [database_id];

Go

 

Resource Database occupies a buffer pool of 28 m in total, and its hollow part is 7 m.

 

Databasename mbused mbempty

-----------------------------------------------------------------------------------------------

Resource Database 28 7

 

If there is low-density data, you can associate it with sys. allocation_units to track the memory usage of each object.

 

Possible causes of low data density:

• Paging
• Big Data columns (for example, if the field in a table is 500 characters, 3000 bytes will be wasted)
• Space is not reused after data is deleted

Several solutions:

• Change table Schema
• Change the index column (for example, to the guid or identity column)
• Use fillfactor to reduce paging]
• Regular index reconstruction or organization Index
• Start Data Compression