DBCC SHOWCONTIG drives DBREINDEX
-
SORRY ABOUT POSTING THIS TO OTHER FORUM AREAS...
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 highAND 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
Regards,Joe Burdette
hanesbrands.com -
It might make sence to run sp_updatestats and dbcc updateusage(0) with count_rows manualy. This may interfere with ongoing operations !
Then try your procedure again
- What's the result ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Quick question:
Do you have auto-shrink option enable for the database?
-
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
-
Balmukund -> "Do you have auto-shrink option enable for the database?"
NO!, NO WAY! 😉
I'm currently taking a look at a solution offered up by Nicholas Cain... I like the fact that it is DTS based... "Checking Your Database Fragmentation Level"... I also like how this solution provides a way to track index maintenance history...
Thanks for the link to "Microsoft SQL Server 2000 Index Defragmentation Best Practices", I'm taking a look at some of the DBCC SHOWCONTIG options, to better performance of that part of the process...
all of your input is appreciated!
Regards,Joe Burdette
hanesbrands.com -
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
-
There are some drawbacks of using DBCC INDEXDEFRAG
1. it doesn't update statistics at all
2. It is always fully logged
-
in my situation... INDEXDEFRAG can only be use to clear up minor fragmentation... DBREINDEX will be used for the bulk of the reorganization...
Regards,Joe Burdette
hanesbrands.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply