wasted buffer pool memory

Data density? Think of this as how packed full or data, index, or LOB rows a data file page is. The more free space on the page, the lower the data density.

Low data density pages are caused by:

• Very wide data rows (e.g. a table with a 5000-byte fixed-size row will only ever fit one row per page, wasting roughly 3000 bytes per page).
• Page splits, from random inserts into full pages or updates to rows on full pages. These kind of page splits result in logical fragmentation that affects range scan performance, low data density in data/index pages, and increased transaction log overhead (see How expensive are page splits in terms of transaction log?).
• Row deletions where the space freed up by the deleted row will not be reused because of the insert pattern into the table/index.

Low data density pages can be detrimental to SQL Server performance, because the lower the density of records on the pages in a table:

• The higher the amount of disk space necessary to store the data (and back it up).
• The more I/Os are needed to read the data into memory.
• The higher the amount of buffer pool memory needed to store the extra pages in the buffer pool.

 

So what can you do about it? There are a number of solutions to low page density including:

• Change the table schema (e.g. vertical partitioning, using smaller data types).
• Change the index key columns (usually only applicable to clustered indexes - e.g. changing the leading cluster key from a random value like a non-sequential GUID to a sequential GUID or identity column).
• Use index FILLFACTOR to reduce page splits, and...
• Periodically rebuild problem indexes.
• Consider enabling data compression on some tables and indexes.

 

For systems with a 100s of GB of memory in use, this query may take a while to run:

SELECT

   (CASEWHEN ([database_id]= 32767)

       THEN 'Resource Database'

       ELSE DB_NAME([database_id])END) AS [DatabaseName],

   COUNT(*)* 8 / 1024 AS [MBUsed],

   SUM(CAST([free_space_in_bytes]AS BIGINT))/(1024 * 1024) AS[MBEmpty]

FROM sys.dm_os_buffer_descriptors

GROUP BY[database_id];

GO

 

 

EXEC sp_MSforeachdb

    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]

    FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')

BEGIN

USE [?]

SELECT

    ''?'' AS [Database],

    OBJECT_NAME (p.[object_id]) AS [Object],

    p.[index_id],

    i.[name] AS [Index],

    i.[type_desc] AS [Type],

    --au.[type_desc] AS [AUType],

    --DPCount AS [DirtyPageCount],

    --CPCount AS [CleanPageCount],

    --DPCount * 8 / 1024 AS [DirtyPageMB],

    --CPCount * 8 / 1024 AS [CleanPageMB],

    (DPCount + CPCount) * 8 / 1024 AS [TotalMB],

    --DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],

    --CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],

    ([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],

    CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]

FROM

    (SELECT

        allocation_unit_id,

        SUM (CASE WHEN ([is_modified] = 1)

            THEN 1 ELSE 0 END) AS [DPCount],

        SUM (CASE WHEN ([is_modified] = 1)

            THEN 0 ELSE 1 END) AS [CPCount],

        SUM (CASE WHEN ([is_modified] = 1)

            THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace],

        SUM (CASE WHEN ([is_modified] = 1)

            THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]

    FROM sys.dm_os_buffer_descriptors

    WHERE [database_id] = DB_ID (''?'')

    GROUP BY [allocation_unit_id]) AS buffers

INNER JOIN sys.allocation_units AS au

    ON au.[allocation_unit_id] = buffers.[allocation_unit_id]

INNER JOIN sys.partitions AS p

    ON au.[container_id] = p.[partition_id]

INNER JOIN sys.indexes AS i

    ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]

WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 -- Taking up more than 100MB

ORDER BY [FreeSpacePC] DESC;

END';