Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Defragment the indexes in a database - NOT WORKING Expand / Collapse
Author
Message
Posted Saturday, May 23, 2009 3:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
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
Posted Saturday, May 23, 2009 3:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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
Posted Saturday, May 23, 2009 4:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
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
Posted Saturday, May 23, 2009 7:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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
Posted Monday, May 25, 2009 4:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
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
Posted Monday, May 25, 2009 7:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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
Posted Wednesday, May 27, 2009 6:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
Thanks a lot.

Rgds
Mohan Kumar VS
Post #724003
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse