Overview on SQL Server Fragmentation

Overview on SQL Server Fragmentation

SQL Server stores data on 8KB pages. When we insert data into a table, SQL Server will allocate one page to store that data unless the data inserted is more than 8KB in which it would span multiple pages. Each page is assigned to one table. If we create 10 tables then we'll have 10 different pages.

As you insert data into a table, the data will go to the transaction log file first. The transaction log file is a sequential record meaning as you insert, update, and delete records the log will record these transactions from start to finish. The data file on the other hand is not sequential. The log file will flush the data to the data file creating pages all over the place.

Now that we have an idea of how data is stored, what does this have to do with fragmentation?

There are two types of fragmentation: Internal Fragmentation and External Fragmentation

SQL Server Internal Fragmentation

SQL Server Internal Fragmentation is caused by pages that have too much free space. Let's pretend at the beginning of the day we have a table with 40 pages that are 100% full, but by the end of the day we have a table with 50 pages that are only 80% full because of various delete and insert statements throughout the day. This causes an issue because now when we need to read from this table we have to scan 50 pages instead of 40 which should may result in a decrease in performance. Let's see a quick and dirty example.

Let's say I have the following table with a Primary Key and a non-clustered index on FirstName and LastName:

I'll talk about ways to analyze fragmentation later in this tip, but for now we can right click on the index, click Properties, and Fragmentation to see fragmentation and page fullness. This is a brand new index so it's at 0% fragmentation.

Let's INSERT 1000 rows into this table:

INSERT INTO Person VALUES
('Brady', 'Upton', '123 Main Street', 'TN', 55555)
GO 1000

Now, let's check our index again:

You can see our index becomes 75% fragmented and the average percent of full pages (page fullness) increases to 80%. This table is still so small that 75% fragmentation would probably not cause any performance issues, but as the table increases in size and page counts increase you may see performance degrade. You can also see from the screenshot above that this table went from 0 pages to 4.

SQL Server External Fragmentation

External Fragmentation is caused by pages that are out of order. Let's pretend at the beginning of the day we have a perfectly ordered table. During the day we issue hundreds of update statements possibly leaving some empty space on one page and trying to fit space into other pages. This means our storage has to jump around to obtain the data needed instead of reading in one direction.

sys.dm_db_index_physical_stats – Introduced in SQL Server 2005, this dynamic management view (DMV) returns size and fragmentation information for the data and indexes of the specified table or view.

SELECT OBJECT_NAME(ips.OBJECT_ID)
 ,i.NAME
 ,ips.index_id
 ,index_type_desc
 ,avg_fragmentation_in_percent
 ,avg_page_space_used_in_percent
 ,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
 AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC 

Index Rebuild : This process drops the existing Index and Recreates the index.

USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REBUILD
GO
Index Reorganize : This process physically reorganizes the leaf nodes of the index.

USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REORGANIZE
GO

Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.