Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Finding Fragmentation Of An Index And Fixing It

From Wiki

Jump to: navigation, search

A lof of time your index will get framented over time if you do a lot of updates or insert and deletes. We will look at an example by creating a table, fragmenting the heck out of it and then doing a reorganize and rebuild on the index

First create this table

  1. CREATE TABLE TestIndex (name1 varchar(500)
  2. not null,id int
  3. not null,userstat  int not null,
  4. name2 varchar(500) not null,
  5. SomeVal uniqueidentifier not null)

Now insert 50000 rows

  1. INSERT TestIndex
  2. SELECT top 50000 s.name,s.id,s.userstat,s2.name,newid()
  3. FROM master..sysobjects s
  4. CROSS JOIN master..sysobjects s2


Now create this index

  1. CREATE CLUSTERED INDEX IX_TestIndex_Index ON TestIndex(SomeVal)

Now let us look at some data by using the sys.dm_db_index_physical_stats DMV. Keep this query handy, we will run it many times

  1. SELECT Object_name(object_id) as Tablename,s.name as Indexname
  2. ,index_type_desc
  3. ,avg_fragmentation_in_percent
  4. ,page_count
  5. FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') d
  6. join sysindexes s on d.object_id = s.id
  7. and d.index_id = s.indid
  8. and s.name ='IX_TestIndex_Index'

(Result Set)

Tablename Indexname index_type_desc avg_fragmentation_in_percent page_count
TestIndex IX_TestIndex_Index CLUSTERED INDEX 0.22172949002217296 451

That is good, almost no fragmentation. Let's change that, shall we?

  1. UPDATE TestIndex
  2. SET SomeVal = NEWID()


(Result Set)

Tablename Indexname index_type_desc avg_fragmentation_in_percent page_count
TestIndex IX_TestIndex_Index CLUSTERED INDEX 99.3717277486911 955

Okay, now you can see that the index is completely fragmented, we are also using 955 pages to store the data instead of 451.


There are two ways to fix fragmentation, one is to reorganize the index and the other is to rebuild the index. Reorganize is an online operation while rebuild is not unless you specify ONLINE = ON, ONLINE = ON will only work on Enterprise editions of SQL Server.
Here is how to do a reorganize:

  1. ALTER INDEX IX_TestIndex_Index ON TestIndex
  2. REORGANIZE;

(Result Set)

Tablename Indexname index_type_desc avg_fragmentation_in_percent page_count
TestIndex IX_TestIndex_Index CLUSTERED INDEX 2.8824833702882482 451

As you can see after the reorganize (DBCC INDEXDEFRAG for you SQL Server 2000 folks) fragmentation levels dropped to less than 3 percent.


Just for fun let's also rebuild (Drop and recreate the index for you SQL Server 2000 folks) the index

  1. ALTER INDEX IX_TestIndex_Index ON TestIndex
  2. REBUILD;

(Result Set)

Tablename Indexname index_type_desc avg_fragmentation_in_percent page_count
TestIndex IX_TestIndex_Index CLUSTERED INDEX 0.22222222222222221 450

As you can see the rebuild made fragmentation almost 0.

Here are two differences between REBUILD ONLINE = ON and REBUILD ONLINE = OFF
ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

Of course you will not run rebuild/reorganize manually for every index in your database, Michelle Ufford from the SQL Fool blog has a nice post with just a script which can do this automatically, you can find that here: Index Defrag Script

Permissions
In order to run the query that checks for fragmented indexes, you need to have VIEW DATABASE STATE permissions.

To determine if you have this permission:

  1. IF Exists(SELECT 1 FROM fn_my_permissions (NULL, 'DATABASE') WHERE permission_name = 'VIEW DATABASE STATE')
  2.   SELECT 'You have permission'
  3. ELSE
  4.   SELECT 'You do not have permission'

If you do not have permissions, a security admin on your server can grant you permissions with the following query:

  1. GRANT VIEW DATABASE STATE TO YourLoginName

You can also deny this permission to a user with the following query:

  1. DENY VIEW DATABASE STATE TO YourLoginName



Contributed by: --SQLDenis 16:38, 7 November 2008 (GMT)
Modified by: --George Mastros 13:43, 16 October 2010 (GMT)

Part of SQL Server Admin Hacks

603 Rating: 2.6/5 (45 votes cast)