February 13, 2006 at 12:43 pm
All,
Background: SQL Server 2000; Standard Edition; SP4; PeopleSoft environment. Resulting DBCC commands are run using the following syntax: DBCC DBREINDEX (TableName, '', 0)
I run the following stored procedure during my maintenance windows, it generates output that I then use to rebuild various indexes. During execution of the script, there are still open connections to the database, but schedule jobs have already completed execution, and there are no users logged into PeopleSoft. However, the PeopleSoft application components are still up and running.
Typically I have to run the script multiple times before the results being reported make sense. For example, lets say your database has 20 indexes that are identified by the script (out of 30 total), you rebuild those 20 indexes, and re-run the script. At this point you would expect to get nothing returned. After all, you've rebuilt the indexes with DBCC DBREINDEX, and DBCC DBREINDEX updates the statistics on the table. What I don't understand is, why would DIFFERENT indexes be identified to be rebuilt, after running the script a second time. After I run the script, rebuild indexes, re-run the script, rebuild indexes, repeat; then I can can get consistent results. I understand that DBCC DBREINDEX will only rebuild an index if it can get an exclusive lock on the table.
My question is this: Is there anything I can do to make the script run more consistently, possible preparation steps?
My goal is to get this to run as a fully automatic process, I'm just trying to understand the inconsistencies that I see between executions.
CREATE PROC sp_ReportReIndex
AS
--
SET NOCOUNT ON
--
-- Create temporary table to hold DBCC SHOWCONTIG output
--
CREATE TABLE #FragmentationResult(
ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
IndexId INT, [Level] INT, Pages INT, [Rows] INT,
MinimumRecordSize INT, MaximumRecordSize INT,
AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
ExtentSwitches INT, AverageFreeBytes FLOAT,
AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
ActualCount INT, LogicalFragmentation FLOAT,
ExtentFragmentation FLOAT
)
--
-- Create temporary table to hold tables/indexes that require
-- defragmentation
--
CREATE TABLE #Defragmentation(
[id] INT IDENTITY,
ObjectName VARCHAR(255),
IndexName VARCHAR(255),
ScanDensity FLOAT
)
--
-- Identify all user tables in the current database to analyze
-- fragmentation
--
SELECT [id], [name] INTO #UserTables
FROM sysobjects
WHERE type = 'U'
ORDER BY [id]
--
-- Determine fragmentation of every user table/index
--
DECLARE @id INT, @name VARCHAR(255), @TableCnt INT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #UserTables
WHILE @TableCnt > 0
BEGIN
SELECT TOP 1 @id=[id], @name=[name]
FROM #UserTables
WHERE [id] > @id
INSERT INTO #FragmentationResult
EXEC('DBCC SHOWCONTIG([' + @name + '])
WITH ALL_INDEXES, TABLERESULTS')
SET @TableCnt = @TableCnt - 1
END
--
-- Determine user tables/indexes that require defragmentation
--
-- Note: current ScanDensity threshold = 75
-- LogicalFragmentatin > 3
-- Pages > 2 (size of table)
--
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
FROM #FragmentationResult
WHERE ScanDensity < 75 --Scan Density is low
AND LogicalFragmentation > 3 --Logical Scan Fragmentation is high
AND Pages > 2 --pages
DROP TABLE #FragmentationResult
--
-- Defragment tables/indexes with high fragmentation
--
DECLARE @oname VARCHAR(255), @iname VARCHAR(255), @sdensity FLOAT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #Defragmentation
WHILE @TableCnt > 0
BEGIN
SELECT TOP 1 @id=[id]
, @oname = ObjectName
, @iname = IndexName
, @sdensity = ScanDensity
FROM #Defragmentation
WHERE [id] > @id
PRINT 'DBCC DBREINDEX (' + @oname + ', ZZ, 0)'
SET @TableCnt = @TableCnt - 1
END
--
-- Release resources
--
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF
GO
Joe Burdette
hanesbrands.com
February 14, 2006 at 2:53 pm
You can look at DBCC INDEXDEFRAG.It is an online operation and it does not hold locks long term and thus will not block running queries.
And then u can rebuild the indexes using DBReIndex during ur maintenance windows..
Thx,
Krishnan Kaniappan
-Krishnan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply