SQLTeam.com | Weblogs | Forums

What is sql page density


#1

What is sql page density


#2

This type of question should be googled before posting in a forum:
https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=sql%20server%20page%20density

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


#3

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


#4

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."


#5

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).