By Navratan Verma
Density – The degree of compactness of a substance or the quantity of people or things in a given area or space.
Let us try to understand in this data point of view. Download the script Here BufferUsage
Data in SQL Server world is stored in pages of 8KB each.
Now let us take a step back and understand how data is stored, read and displayed to the user.
As we know that data is stored in table which are in turn stored as pages of 8 KB each, assume that we have a
table Addressbook which has ID,FirstName,LastName,PresentAddress,PermanentAddress,DOB.
Data will be stored in in the table based on the data types chosen and loaded into buffer pool whenever a
select statement is issued against the table then display to the user. The data (pages) will remain loaded into
the buffer pool thus occupying certain space based on the no of records in that table or result.
Coming back to density if the space in the page is not utilized correctly in other words if more space is left free
in the page the condition is called lower density.
Causes of low density in pages are
- Tables with fixed sized rows.
- Page splits caused by random updates or inserts.
- Data deletions and pages free not re-used due to insert patterns.
Demonstration below (using fixed and variable sized rows) – I am creating a two versions of table Addressbook with different data types and inserting few thousand records.
Let us examine the table after inserting records using sys.dm_db_index_physical_stats
Although both tables have same no of rows you can observe the difference in data pages and avg_page_space_used_inpercent.
Let us select data from these two tables and see the Buffer count.
These are the issues caused by low data density
- High buffer pool memory needed to store additional pages.
- Additional I/O to read extra data pages in memory.
- Additional disk space used to store same amount of data
- Proper table design
- Proper fill factor to avoid Page splits
- Proper index re-build or re-organize strategy
- Evaluating usage of data compression