What is it?
Well the first type is called logical scan fragmentation and it’s just that: pages that are out of order in the index (when looking at their physical order compared to their logical order). Now, if your query doesn’t rely on a linked list (if it’s a seek, lookup, unordered scan) then logical scan fragmentation is not a concern. Your queries won’t be hurt at all if you don’t rely on a logical order. You can be illogical in your arguments with your friends if your friends don’t rely on logic (or if your friends are imaginary). It’s kind of like my 18 mo. old son came along and stuck some new pages in the book…which just makes the flow of the book harder. The physical order of the pages and the local order (the index references the pages) are out of whack. The fix? rebuild or reorganize.
The other type is called average page density (or internal fragmentation) and it is just that: when the page density is less than it should be. If your database is a high-read database with few inserts then you want this to be low. If there are many inserts and updates then you want a little more spaces in your pages, so you would specify a fill factor on your index creation (this helps avoid the very evil and very expensive page splits). I keep a journal (pencil and paper) and if I get tired of the journal I’m on I’ll draw big pictures or write a few words on the page to get through the journal…making my average page density low. If I want to fix it I’ll go through and cut the words/pictures out and place them in consecutive order in a new journal (this would be a rebuild).
How do you know if you have fragmentation? Check the dmv dm_db_index_physical_stats. Look for avg_fragmentation_in_percent and avg_page_space_used_in_percent columns.
select * from sys.dm_db_index_physical_stats(db_id(‘northwind’),NULL,NULL,NULL,NULL)
Rebuilding is a single transaction, you can defrag as you go, and it rebuilds the index in the same physical order as the linked list and puts the pages in order. You can do it while the database is online (2005 and up), but it is suggested to do this a low-use times.
Reorganizing is longer and is not as optimal as rebuilding, but it can be interrupted and it only locks a couple of pages at a time so it has less interruption on your online database.
