SQLTeam.com | Weblogs | Forums

What is sql page density


What is sql page density


This type of question should be googled before posting in a forum:

If your question still isn't answered after reading a few articles, let us know where you are stuck.


I already Googled it but couldn't find a simple answer, If you don't mind explain it?


It was a valid question, and Googling it doesn't really supply a clean answer. This is from Googling it:
"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."


Page density (or more precisely, AVERAGE PAGE DENSITY) is an indication of how full on average your data/index pages for the object(s) you are looking at is. A page is the most fundamental unit of storage in SQL Server, and can hold 8 KB. For a variety of reasons, the pages may not be full.

When a page splits, it can leave the two resulting pages to be less than full. A page split happens when a row needs to be inserted in a specific location, but there is not enough space on the page to insert that row.

A large row size can cause a page to be less than full. Only full rows can be stored in a page. Sql server does not store a row across data pages. So if your row is 5K, each page can store only a single row, and the remaining 3K will be left unused.

You can use sys.dm_db_index_physical_stats or DBCC SHOWCONTIG to see your average page density. Look for avg_page_space_used_in_percent column (or Avg. Page density column).