April 3, 2006 at 1:59 pm
I have inherited a 250 gig sql2000 database that has only 3 hours a night downtime. I need to find out how I can possibly cycle thru all the tables and do a indexdefrag or dbreindex on a schedule? Doing it is not the problem I have the procs but I need some pointers. Can I just let it rip or will I see excessive locking, tran logs etc.
Input from other folks in a similar situation would be appreciated.
ALso whats your thoughts on dbreindex vs. indexdefrag?
April 4, 2006 at 2:36 am
Try this script from BOL:
/********************************************************/
/*Perform a 'USE ' to select the database in which to run the script.*/
-- 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
SELECT @maxfrag = 30.0
-- Declare 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 cursor for 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
/********************************************************/
It will defrag anything it finds with a fragmentation greater then 30%
In the cursor you could always insert a check that verifies what time it is and optionally could exit the loop if it would work for too long. This way you could defragment as many tables you have time for every night (and not Defragment the same one again, next night).
Happy hunting!
Hanslindgren
April 4, 2006 at 7:36 am
Thanks Alot. I was aware of that stored procedure but I like the thought for testing the time between cursor loops. I will do that?
Second question. The dbase is in full recovery. Should I be concerned with transaction log management in respect to disk space? Should I change the recovery mode to builk logged or simple during the job or run it first and see then decide?
April 4, 2006 at 8:12 am
In the long run it might be beneficial to become familiar with what hits that database. There might be tables that are relatively static and don't need to be hit. There a likely others that need regular attention. You can always modify the BOL script to produce just the fragmentation output, run that and see where you are at.
Another question is how firm is that time frame specified. Do the business processes dictate that or did someone arbitrarily set that amount of time?
April 4, 2006 at 9:14 am
Yes. That could rise a big concern. Since all datamovement (even during defragmentation) is logged and will be kept in the transaction log, something really needs to be done either during or after your defrag process.
Maybe the easiest way would just to do a full backup after you have defragmented?
Big DBs and defragmentation is a problem that needs to be thought carefully about...
I hope I could be of some help.
//Hanslindgren
April 4, 2006 at 9:14 am
You also might choose to split up the tables on different periods. Heavily hit tables might be in a script that runs every night. Less used might be scheduled every other night, or even 2-3 nights a week.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 6, 2006 at 9:23 am
I agree with splitting up the tables to be defragged on different days on a VLDB. I've tried the above defrag script on a large database and it runs a very long time and the transaction log was very large as well (Sorry, but I don't recall the exact numbers). What I did was find the 6 largest tables and defrag them separately on a different day, then on the 7th day I defrag the rest of the tables. Works for me 🙂
April 6, 2006 at 12:22 pm
I modified the above script quite a bit... I noticed that the above script could not deal with non-dbo table owners, nor could it deal with different collations from Information_schema, nor could it deal with databases that had weird named tables(if you have used SQL Best Practices Analyzer, you know the table names I am talking about).... Also, I wanted to do offline defrags, and I also wanted a way to record what indexes/tables/databases were being defragged each eve..
My script will offline tables to defrag the individual indexes, and then it writes an entry to another database detailing what databases/tables/indexes were defragged, what their fragmentation and scan densities were at, and finally how long in milliseconds the defrag took to run...
Currently I have to schedule a job and setup a step for each database w/the script below.. I tried to make one step that recursed through a list of the databases, but I seem to remember the SQL Job system didnt like all the swapping databases I was doing, so I ended up going to individual steps for each database. Wish List: is to make one job step that recurses all databases and reindexes per below... not sure if/when I will get around to looking at it again though.. hehe
Side Note: If you are working on a database with non server-default collation you will need to modify the column collation in red below to something like: ObjectName nCHAR (255) COLLATE Latin1_General_CI_AS_KS_WS NULL.
Side Note2: If you go with multiple steps per job for each database, yuo will need to code in the @dbname variable so when you record what transpired in your seperate database, it will be able to code in the correct database name...
Side Note3: you will prolly need to specify your own database/table for the section that records history...
Hope this helps, If anyone has any fixes/recommendations, I would love to see/hear about them... I aint the bestest dba, so always looking for ways to improve...
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
declare @maxscan DECIMAL
declare @scan DECIMAL
declare @dbname VARCHAR (128)
declare @strttime DATETIME
declare @runtime decimal
declare @indexname CHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @dbname = 'Accounting' --Set DBName up here
SELECT @maxfrag = 10.0 -- max fragmentation
SELECT @maxscan = 75.0 --maxscan density
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT '['+Table_schema+'].['+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
Update #fraglist
set objectname = (SELECT '['+table_schema+'].['+TABLE_NAME+']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND OBJECTNAME = TABLE_NAME)
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexName, LogicalFrag, scandensity
FROM #fraglist
WHERE (LogicalFrag >= @maxfrag or scandensity <= @maxscan)
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag, @scan
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @execstr = 'DBCC DBREINDEX (''' + RTRIM(@tablename) +''', '''+RTRIM(@indexname)+''', 0)'
select @strttime = getdate()
EXEC (@execstr)
select @runtime = convert(decimal, DATEDIFF(ms, @strttime, getdate()))
Print @execstr+'indexname: ' + '('+RTRIM(@indexname) + ') '
INSERT INTO [DB_HISTORY].dbo.NghtlyReindx values (@dbname, RTRIM(@tablename),RTRIM(@indexname), RTRIM(@frag),RTRIM(@scan),@strttime, @runtime)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag, @scan
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
May 10, 2006 at 11:57 am
If this is 2005 db, there's an online rebuild of index that you can use. The only down fall is that it uses a lot of tempdb space so be prepared. You can also partition your DB to multiple drives and possibly split the indexes also into separate drive. That should make your rebuild of indexes more faster.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply