Friday, January 14, 2011

MS SQL SERVER 2008 Fix Fragmented Index



Who Should Read This Blog: MS SQL Server DBA facing following issues:

1. MS SQL Server DBA - Index getting fragmented often.
2. MS SQL Server DBA - Index not being used by the queries properly.

I can see many DBAs asking the same questions again and again regarding index fragmentation. Most of them take a wrong path to fix this problem. Let's understand how index fragmentation happens and how to fix this.

Index fragmentation is of two types: Internal and External. Internal is not very harmful. Let's talk External now.

Before you start fixing index fragmentation, you should be able to identify whether the system is OLTP or OLAP. Please never go for high fill factor if you have an OLTP system and low fill factor if you have an OLAP system.

Let's Understand: If you go for high fill factor then this means you are allowing more data
to absorbe into the index pages. And when new data will come in the order, page split will
occur. So wait before you look for high fill factor. The same rule applies on Low fill
factor also. If your system is OLAP then never ever go for low fill factor as this will
unnecessarily consume the space on each page. So the key is to understand your system
before you understand the problem.

Reason of Index Fragmentation:

1. Autoshrink. Verify that autoshrink is not on. The sql server will cost high fragmentation in case it needs to shrink the data. I believe to confirm index defragmentation as soon as you ever need to shrink the DB.

2. Having less Fill factor on OLTP env can cause the more page splits. Please understand your system before you go for the fill factor and index managment. You can fix the fill factor with following command.

ALTER INDEX PK_Powershelltute ON [Tuitionaffordable].dbo.Powershelltute REBUILD WITH
(ONLINE=ON, MAXDOP=1, SORT_IN_TEMPDB=ON, FILLFACTOR=95)

3. Following is the query where you can identify the fill factor of your tables:

SELECT  tbl.name AS table_name,
        indx.name AS index_name,
        indx.fill_factor
FROM    sys.tables tbl
        JOIN
        sys.indexes indx
        ON tbl.object_id = indx.object_id
WHERE
        indx.type in (1,2)
ORDER BY
        tbl.name,
        indx.name


No comments:

Post a Comment