Index Maint DBCC assistance.

I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- 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;
GODo you have a test environment where you can load a copy of prod? If so,
generate the defrag script there but execute it against prod. This way, you
won't be locking up the table in prod with a share lock by running DBCC
SHOWCONTIG.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- 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|||The way to really solve this is to upgrade to 2005 as the equivalent of
Showcontig does not block like in 2000. But the code you have now seems to
be the fastest and lightest way to do this in 2000. I never recommend on
larger dbs to reindex blindly so your approach is what I would recommend
anyway. You can keep a history of the fragmentation by index and use that to
determine which to defrag. Chances are if the usage is roughly the same week
to week the indexes will be roughly the same in terms of fragmentation. So
collect samples for a few weeks and base your rebuilding off of those
numbers for x many future weeks.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>I am working with multiple servers, and numerous databases. I have
> some large databases, some tables with 100's of millions of rows.
> Some of the DB's are 24/7 so recreating the indexes is not really an
> option. I am trying to come up with a "smart" index plan. I have
> seen many examples of this, and I have one that does seem to work,
> except that the DBCC showcontig seems to take forever! I am using a
> variation on the following. The DBCC Showcontig seems to lock the
> table, is this true?
> Is there a faster way to get the DBCC fragmentation information?
> Is it really worth the work identifying and defragmenting those
> indexes that fall below a threshold or should I just defrag every
> index?
> Any other help on a "smart" maint plan for index maint would be
> appreciated!
> OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
> I am on SQL 2000, some at SP3a / SP4.
> Sample of code below:
> http://msdn2.microsoft.com/en-us/library/ms175008.aspx
> -- 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|||And perhaps this "history of the index fragmentation" may show that some
indexes are fragmented too soon and maybe a new fill factor will be needed.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Andrew J. Kelly" wrote:

> The way to really solve this is to upgrade to 2005 as the equivalent of
> Showcontig does not block like in 2000. But the code you have now seems to
> be the fastest and lightest way to do this in 2000. I never recommend on
> larger dbs to reindex blindly so your approach is what I would recommend
> anyway. You can keep a history of the fragmentation by index and use that
to
> determine which to defrag. Chances are if the usage is roughly the same we
ek
> to week the indexes will be roughly the same in terms of fragmentation. So
> collect samples for a few weeks and base your rebuilding off of those
> numbers for x many future weeks.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David Hay" <david.hay@.gmail.com> wrote in message
> news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>|||Yes good point Ben, I should have noted that as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:93BADA3C-FD11-414E-9044-21E3D63B622B@.microsoft.com...[vbcol=seagreen]
> And perhaps this "history of the index fragmentation" may show that some
> indexes are fragmented too soon and maybe a new fill factor will be
> needed.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Andrew J. Kelly" wrote:
>|||Thanks to all for the information. I did run a test in Prod, and it
ran much faster. (Much larger box). I saw a script that loaded a
history table and I'll work on building that.
Thanks again to all responding.
David