Fixing Index Fragmentation in SQL Server

Here is the fragmentation status on indexed tables:

And here is my PageDefinition table:

Any suggestion what possible changes I can made in this to low down the fragmentation. I am doing this first time so solution with reason will be very helpful.also please let me know if I need 开发者_运维问答to add some more detail here.

Thanx

---

Primary reason for fragmentation is pagesplits. Insert of new records or updating existing records might have resulted in changes in the way data allocated in a page

Good Presentation - http://devconnections.com/updates/LasVegas_Fall10/SQL/Randal-SQL-SDB306-Fragmentation.pdf

http://blog.sqlauthority.com/2008/03/27/sql-server-2005-find-index-fragmentation-details-slow-index-performance/

You can provide fillfactor depending on updates/inserts done on table. Example - If you table is read only and no update might take place you can provide 100% fill factor. This would provide allocation so that your inserts/updates does not cause framgmentation.

70% you can configure to experiment with. Rebuilding index is another solution for this problem.

---

Kimberly Tripp - the Queen of Indexing - has a ton of great blog post on how to select a good clustering key (in SQL Server, the primary key is - by default - your clustering key).

Check them out, read them, learn them - obey them! :-)

• GUIDs as Primary and Clustering key
• Ever-increasing clustering key - the Clustered Index Debate..........again!
• Disk space is cheap...... THAT'S NOT THE POINT!!!

Basically, just read her whole blog - everything on indexing, clustered indices and so on.

Your clustering key (and thus by default - your primary key) should be:

• narrow - 4 byte INT is great, anything beyond 16 byte (GUID) is a massive waste of space
• unique - read her blog posts on why
• static - never change (if possible)
• ever-increasing (typically: INT IDENTITY) to avoid page splits which cause most of the fragmentation