Generally writing or storing data non-contiguously on disk is known as fragmentation. Before explaining about fragmentation, I will give little information about MSSQL storage unit so it will be easy to understand the fragmentation.
The fundamental unit of data storage in MSSQL Server is 8kb chunk.That is called page.
The data which we are inserting into database table or other objects will be get stored in data page.That will contain actual data.
Extents are the basic unit in which space is allocated to tables and indexes. A extent is nothing but 8 continuous Data Pages. There is two extents types.
Uniform Extents
All the 8 pages in the extent is used by a single object. For example imagine that we are having table called Vembu. The 8 pages of a extent is filled by records in the Vembu table alone.
Mixed Extents
All the 8 pages in the extent is not used by a single object.A mixed extent can have maximum
Of 8 object in it.
Whenever a new object is created, SQL Server will allocate pages from Mixed Extent. When that object exceed 8 pages it will move that object to Uniform Extent.
Now we can move to fragmentation concept. We can classify fragmentation into two types:
Internal Fragmentation, External Fragmentation.
Internal Fragmentation
When records are stored non-contiguously inside the data page, then it is called internal fragmentation. Existence of unused space in between records.
Reason :
Deleting Records :
When we delete records on a table the records are deleted from data pages. So there will be will be unused spaces between records on the page or inbetween pages. Random deletes resulting in empty space on data pages
Update Records:
Updates to an existing records value with a larger value, which doesn’t fit on the same data page then that entire record will move to new data page. So there will be free space in old data page that is unused space and also when updating a value with smaller value than the existing value that leads to availability free space in data page.
Inserting Records:
We all know that a page is an 8KB size. if rows are 3200 bytes each, then only two records can be inserted on a 8KB page this results in leaving approximately 1600 bytes unused on every page.
This unused space causes poor cache utilization and more I/O, which ultimately leads to poor query performance.
External Fragmentation:
When the pages or extents of an object (table,index) is stored in non-continuous manner on disk, it is known as external fragmentation.
This will lead to higher disk rotations during querying operation.
Pictorial Representation of Non-Fragmented and Fragmented Table.
Non- Fragmented :
Fragmented :
This table representation having four records.
When we try to query a table which has no fragmentation. We will get data from one extent and also 0 % fragmentation total disk space used in 8KB and page used percent is 100(no free space left in page 1 page is filled fully).
When we try to query a table which has fragmentation from our picture. We will get data from two extent.So we need switch from one extent to another extent so disk rotation will increase compare to non-fragmented table. Disk space used is 16KB. Page used percentage not 100.There is free space in both the pages.
So if Fragmentation is exit in a database that result in poor query performance and more space utilization in disk.
Got questions? Email us at: vembu-support@vembu.com for answers.
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
