May 23, 2009 at 3:42 am
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
-- 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
May 23, 2009 at 3:56 am
How big are the indexes that aren't defragmenting? How many pages?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2009 at 4:22 am
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
May 23, 2009 at 7:15 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2009 at 4:32 am
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
May 25, 2009 at 7:40 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2009 at 6:20 am
Thanks a lot.
Rgds
Mohan Kumar VS
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply