November 20, 2006 at 8:20 pm
Hi all,
I want to know in a particular database, how to find out which are the tables are fragemented heavily and on which index, i want to list all the tables and indexes which are fragemented heavily.
Regards
Dinesh
Dinesh
November 21, 2006 at 3:13 am
Have a look at DBCC SHOWCONTIG and see if that may be of help to you.
/Kenneth
November 22, 2006 at 10:55 am
Here's a script I use to filter to the relevant data that dbcc showcontig returns.
You want AvgPageDensity to reflect a number as a percent close to your intended fillfactor, ScanDensity to be as high as possible, and logicalfrag to be as low as possible.
---------------------------------------------------
CREATE TABLE #SHOWCONTIG (
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(18,6),
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL(18,6),
ExtentFrag DECIMAL(18,6))
INSERT #ShowContig
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')
select objectname, indexname, indexid,
countrows, AvgPageDensity, scandensity, logicalfrag
from #ShowContig
where objectname not like 'sys%' and objectname not like 'dt_%'
and indexid <> 0 --don't show the table hash
and countrows > 10000 --indexes only really relevant with larger tables, or remove this to show all
order by objectname, indexid
drop table #SHOWCONTIG
----------------------------------------------------------------------------
November 22, 2006 at 11:01 am
Almost forgot... Here's a neat script for rebuilding every index in the database:
EXEC
sp_msforeachtable @command1="print '***' + '?'",
@command2
= "dbcc dbreindex ('?')"
November 22, 2006 at 11:37 am
This script works great for identifying index logical fragmentation:
/*
RUN AGAINST APPROPRIATE DATABASE
*/
SET NOCOUNT ON
-- Declare variables --
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @DatabaseName VARCHAR(255)
DECLARE @ServerName VARCHAR(255)
SELECT @DatabaseName = DB_NAME()
SELECT @ServerName = (Select @@ServerName)
-- Decide on the maximum fragmentation to check --
SELECT @maxfrag = 10.0
-- Declare TABLES cursor --
DECLARE tables CURSOR FOR
SELECT DISTINCT
obj.Name
FROM SysObjects obj
JOIN SysIndexes ind on obj.ID = ind.ID
WHERE obj.Type = 'U'
AND obj.Name NOT LIKE 'WorkingTables'
AND obj.Name NOT IN
(SELECT WT_Name FROM WorkingTables)
AND ind.IndID > 0
AND ind.IndID < 255
AND obj.Status > 0
AND ( ind.Status & 64 ) = 0
AND ( ind.Status & 8388608 ) = 0
AND ( ind.Status & 16777216 ) = 0
order by obj.Name
-- Create the fragmentation temp table --
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL NULL)
-- 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 TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor --
CLOSE tables
DEALLOCATE tables
PRINT + RTRIM (GetDate()) + ' SERVER:' + RTRIM(@ServerName) + ' DATABASE:' + RTRIM(@DatabaseName) + ''
PRINT '___________________________________________________________________'
PRINT ''
PRINT 'INDEX FRAGMENTATION MORE THAN ' + RTRIM(@maxfrag) + ' % '
PRINT ''
SELECT
'Index Name' = RTRIM(SUBSTRING(ObjectName, 1, 20)),
IndexId,
'% Fragmented' = LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Delete the temporary table
DROP TABLE #fraglist
GO
SET NOCOUNT OFF
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply