Defragment the indexes in a database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Defragment the indexes in a database - NOT...

Defragment the indexes in a database - NOT WORKING

Rate Topic

Display Mode

Topic Options

Author

Message

Mohan Kumar VS

Posted Saturday, May 23, 2009 3:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 10:13 PM
Points: 283, Visits: 311

Hi

I'm trying to defragment the indexes in a database Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG as mentioned in BOL. But it fragmentation level is same even after executing the script.

Kindly suggest.

Rgds
Mohan Kumar VS

Here is the script as in BOL:

/*
E. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
The following example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold.

Perform a 'USE ' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename varchar(128)
DECLARE @execstr varchar(255)
DECLARE @objectid int
DECLARE @indexid int
DECLARE @frag decimal
DECLARE @maxfrag decimal

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0

-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal)

-- Open the cursor.
OPEN tables

-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor.
OPEN indexes

-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table.
DROP TABLE #fraglist
GO

Post #722541

GilaMonster

Posted Saturday, May 23, 2009 3:56 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

How big are the indexes that aren't defragmenting? How many pages?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #722542

Mohan Kumar VS

Posted Saturday, May 23, 2009 4:22 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 10:13 PM
Points: 283, Visits: 311

Hi

The details are as below for a table :

ReservedSpace: 10776 KB
DataSpace : 8640 KB
CombinedIndex Space: 1488 KB
UnusedSpace : 648 KB

When I executed DBCC SHOWCONTIG ('TableName'), I got the following result:

Table: 'TableName'(576109193); index ID: 0, database ID: 27
TABLE level scan performed.
- Pages Scanned................................: 1080
- Extents Scann.........................: 149
- Avg. Pages per Eed..............................: 150
- Extent Switches.....xtent........................: 7.2
- Scan Density [Best Count:Actual Count].......: 90.00% [135:150]
- Extent Scan Fragmentation ...................: 95.33%
- Avg. Bytes Free per Page.....................: 394.8
- Avg. Page Density (full).....................: 95.12%

Rgds
Mohan Kumar VS

Post #722549

GilaMonster

Posted Saturday, May 23, 2009 7:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

That's a heap (table that doesn't have a clustered index). It can't be defragmented because there's no index there to defragment.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #722565

Mohan Kumar VS

Posted Monday, May 25, 2009 4:32 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 10:13 PM
Points: 283, Visits: 311

Thanks for your response. I've idenfied the same.

Can you please let me know if there is any script to change Non-clustered index to Clustered Index?

Right now I'm doing it manually for all the table w/o CI, by Right-Clicking on the Table & so on....

Rgds
Mohan Kumar VS

Post #722909

GilaMonster

Posted Monday, May 25, 2009 7:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

There's no script to change an index from nonclustered to clustered. Drop the index and recreate it as clustered. Be careful which one you change. A poorly chosen clustered index will cause lots of problems.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #722968

Mohan Kumar VS

Posted Wednesday, May 27, 2009 6:20 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 10:13 PM
Points: 283, Visits: 311

Thanks a lot.

Rgds
Mohan Kumar VS

Post #724003

« Prev Topic | Next Topic »

Permissions