Page Density in SQL Server Database

Question

Is hi Page density is good or bad?

Today when i check my Azure SQL DB.

Here is the details:

More fragameted index (>50%) the avg_page_space_used_in_percent is from 50%-60%
But Less fragmentationed index (less than 30%) the avg_page_space_used_in_percent is from 95%-60%

Answer 1

Hi,

Today when i check my Azure SQL DB

You are using the tag sql-server-general, which mean SQL Server on premises usually, and speak about Azure SQL DB. You better use the tag azure-sql-database

Density

To be 100% sure we speak about the same term Density let's clarify that I mean the amount of data in the data page (each page size is 8kb so the amount of the space which is used)

The following query can help you to determine average page density for indexes (including the CLUSTERED index which is the table itself)

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,  
       OBJECT_NAME(ips.object_id) AS object_name,  
       i.name AS index_name,  
       i.type_desc AS index_type,  
       ips.avg_page_space_used_in_percent,  
       ips.avg_fragmentation_in_percent,  
       ips.page_count,  
       ips.alloc_unit_type_desc,  
       ips.ghost_record_count  
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips  
INNER JOIN sys.indexes AS i   
ON ips.object_id = i.object_id  
   AND  
   ips.index_id = i.index_id  
ORDER BY page_count DESC;  
GO  

Note: The FILLFACTOR of the index is the percentage value to fill data in the data page, so this directly related to the Page density.

Is hi Page density is good or bad?

Depend.

High page density means you read lass pages to get all the data, but at the same time it might result for example with page split when you INSERT/UPDATE data. So if your data in the table does not change, then why do you need free space but if you change the data a lot then you might prefer lower density.

shrink will complete faster if page density is high for example (lass pages to move), so many times before shrink we will prefer to increase page density before shrinking data files. SELECT will be faster for High page density but UPDATE/INSERT might be slower.

You need to balance according to your way of using the data.

More fragameted index (>50%) the avg_page_space_used_in_percent is from 50%-60% But Less fragmentationed index (less than 30%) the avg_page_space_used_in_percent is from 95%-60%

Microsoft documentation recommends to have 60-70% or more and consider rebuilding or reorganizing if it is less.

Obviously it is a golden rule and not something you must follow. You should be familiar in the way you use the data and do your own decision accordingly.

Highly recommend to go over the following two documents:

https://learn.microsoft.com/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699#index-maintenance-in--and-

https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage?WT.mc_id=DP-MVP-5001699#evaluate-index-page-density

Answer 2

Hi @Vijay Kumar ，

avg_page_space_used_in_percent: This is an average percentage use of pages that represents to internal fragmentation. The higher this value is, the better
https://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/

you also need to focus on avg_fragmentation_in_percent

Rebuilding an index with the FILLACTOR option specified allows the page fullness to be changeed to fit the query pattern on the index, of course, fill factor may not a perfet solution
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15#evaluating-disk-space-use

It is recommended that check the fragmentation level of each index before and defrag or rebuild the indexes that meet your thresholds use database maintenance scripts

https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

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

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.