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.
- 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.
So what can you do about it? There are a number of solutions to low page density including:
For systems with a 100s of GB of memory in use, this query may take a while to run:
SELECT
(CASE
WHEN ([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';