SQL Server – Understanding data density

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.

DataPage1

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

  1. Tables with fixed sized rows.
  2. Page splits caused by random updates or inserts.
  3. 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.

DataPage2

Let us examine the table after inserting records using sys.dm_db_index_physical_stats

DataPage3

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.

DataPage4

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

Possible Solutions

  • Proper table design
  • Proper fill factor to avoid Page splits
  • Proper index re-build or re-organize strategy
  • Evaluating usage of data compression

Thanks

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *