Fragmentation

Oopsie..I am posting on wrong order..

First it should have been the one on fillfactor..
Then this one..

And then the script to detect fragmentation.

There are two types of fragmentation ie.. internal and external fragmentation. The internal fragmentation which is caused by frequent deletes/inserts and also influenced by fill factors.External fragmentation can be further classified into extent and logical fragmentation which will be briefly explained here.

Before getting into external fragmentation just a quick background info.
As the tables grow new pages are added to the table. The newly added pages may/may not be the located as the immediate page(with the next physical address) to the previous page in the table.Ex: Let us say ,first 100 rows of a table belong to the page at address 500. The next 100 rows in table may be present in the page 515. So this would make the Query engine to jump from Page 500 to 515 while scanning thro the table.

Extents are a group/chain of 8 Pages. An Extent can contain pages from the same table/object or different table/object.When the query engine has to jump outside a current extent to obtain the next row in the table, it is termed as extent switch. The total number of extents in a table which are out of order is refered as extent fragmentation.

The Out of order pages ( only leaf level pages ) in indexes are termed as logical fragmentation.SQL Server provides logical fragmentation values for clustered indexed tables and Nonclustered Indexes. A table with no clustered index's fragmentation is expressed by SQL Server only in terms of extent fragmentation and not by logical fragmentation. When logical fragmentation is specified for a clustered index it implies for the entire table data as the leaf level pages of a clustered index make the actual table data. The script here provides the logical and extent fragmentation for the entire database.

So whats the problem if fragmentation is present? Problem would be a performance dip as SQL Server has to jump from one page to another which slows down reads.The fix is given below.

Fragmentation less than 30% - Reorg can help.Reorg command is ALTER INdex Indexname|ALL ON tablename Reorganize
Fragmentation greater than 30% - Rebuilding the index can help. ALTER INdex Indexname|ALL ON tablename Rebuild.
If its a heap(table with no clutsered index) that is heavily fragmented then creating a clustered index on the table can reduce the fragmentation.

All the options shuold be preferablly at our system's off peak time as they are quite resource intensive.Microsoft's maintenance planner has Rebuild and Reorg defined in it. Rebuild,Reorg,Database Shrinking have an impact on fragmentation and the comparison of these three options will be covered in upcoming posts.