June 6, 2005 at 1:26 pm
I am trying to put together something that I can schedule and either produce a script to run or run an DBCC Reindex on ONLY the tables that need reindexing. I have a large database with a ton of tables and I just don't want to rebuild all the indexes on all of the tables just the ones in dire need of it. The question is how. Does anyone have anything setup in their environment to do this ?
June 6, 2005 at 1:32 pm
Maybe this will help (from today's articles) :
June 6, 2005 at 1:54 pm
Check out the scripts section too. Someone published a sctipt that would do a DBCC Showcontig on each table and then loop through them performing maintenance based on a fragmentation threshold. Sorry I couldn't remember the name of the script.
June 7, 2005 at 2:50 am
Or just open up BOL, goto the DBCC SHOWCONTIG help and Copy and Paste the code under E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
Happy DB-surfing
June 7, 2005 at 2:51 am
I have a script to do this. If you don't have any luck finding elsewhere, I can e-mail it to you. Let me know
Andy
June 7, 2005 at 3:03 am
See if this helps: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 7, 2005 at 3:13 am
I have copied (probably somewhere from this site ) and used the following probably with some modifications from the original.
CREATE TABLE AdminReindexDuration (
DBName varchar (255) NULL ,
DateStart datetime NULL ,
DateEnd datetime NULL
) ON PRIMARY
GO
CREATE TABLE AdminReindexHist (
DBName varchar (255) NULL ,
ObjectName varchar (255) NULL ,
ObjectOwner varchar (50) NULL ,
ObjectId int NULL ,
IndexName varchar (255) NULL ,
ScanDensity decimal(18, 0) NULL ,
dtDateTime datetime NULL CONSTRAINT DF_AdminReindexHist_dtDateTime DEFAULT (getdate())
) ON PRIMARY
GO
go
--sp_defragment_indexes 95.00
CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL
AS
/*
This stored procedure checks index fragmentation in a database and defragments
indexes whose scan densities fall below a specified threshold, @magfrag, which
is passed to the SP. This SP was initially based on a code sample in SQL Server 2000
Books Online.
Must be run in the database to be defragmented.
select * from AdminReindexDuration order by dbname,datestart
select * from AdminReindexHist
*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @tableid INT
DECLARE @tableidchar VARCHAR(255)
--check this is being run in a user database
SELECT @dbname = db_name()
-- IF @dbname IN ('master', 'msdb', 'model', 'tempdb')
-- BEGIN
-- PRINT 'This procedure should not be run in system databases.'
-- RETURN
-- END
--begin Stage 1: checking fragmentation
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id)
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0
-- Create the temporary table to hold fragmentation information
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)
-- Write to output start time for information purposes
insert MASTER.DBO.AdminReindexDuration
(DBName,
DateStart)
values (@dbname,GETDATE())
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database running dbcc showcontig on each one
FETCH NEXT
FROM tables
INTO @tableidchar
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tableidchar
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Report the ouput of showcontig for results checking
-- SELECT * FROM #fraglist
/*
DELETE
FROM AdminReindexHist
WHERE DBName = @dbname
*/
-- report of all tables to be reindexed
INSERT Master.dbo.AdminReindexHist
(DBName,
ObjectName,
ObjectOwner,
ObjectId,
IndexName,
ScanDensity)
SELECT @dbname,
ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity
FROM #fraglist f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Begin Stage 2: (defrag) declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity
FROM #fraglist f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Write to output start time for information purposes
--SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
-- Open the cursor
OPEN indexes
-- Loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON
SELECT @execstr = 'DBCC DBREINDEX (''' + RTRIM(@objectowner) + '.' + RTRIM(@tablename) + '''' +
', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
--SELECT 'Now executing: '
--SELECT(@execstr)
EXEC (@execstr)
SET QUOTED_IDENTIFIER OFF
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Report on finish time for information purposes
--SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
UPDATE MASTER.DBO.AdminReindexDuration
SET DateEnd = GETDATE()
WHERE DBName = @dbname
AND DATEDIFF(DD,DateStart,GETDATE()) = 0
-- Delete the temporary table
DROP TABLE #fraglist
June 7, 2005 at 9:30 am
I created some tables to store the dbcc showcontig information. Then i made a job that runs each night, and stores all this information in that table.
Then a while later, another job fires off that does a compare between today and yesterday. When certain indexes reach a predetermined threshhold, they are defraged. So each night, only the most offending indexes are defragged. This has dramatically sped up the database server.
June 7, 2005 at 10:08 am
the showcontig simply gets the results and i store them each day.
I dont always defrag, based on the > 10% < 10% rule. But with a deviation also. So, an index can get out of whack, but if it didnt deviate too much, i dont redo it. Initially it was just taking too much time to do too many indexes. So a comparison was instituted to only to those that were the most offensive.
Now, i realize that some will get out of whack and stay that way for a while. But when it was taking hours to defrag all these indexes, it was inhibiting production processes occuring at night. Its a 24/7 website, and often will get hit at night, when this process is going on.
So, it was just a decision to only defrag the most changed indexes that fell out of the range.
It just so happens that some of the largest tables, have bad development occuring, where massive amounts of data are deleted and reinserted. Causing those indexes to get way out of whack.
Does this justify my wierdness in comparing yesterdays to todays fragmentation levels? Any ideas how i can do it better, i'd gladly hear.
June 7, 2005 at 10:13 am
Sounds like a good idea to me. The question really is wheater the server is allowing the production to keep on going without have too many indexes that could be causing performance degradation. But only you know the system well enough to answer that question.
June 7, 2005 at 10:14 am
Okay, then I see. But you say you have a production server 24/7 with bad development happening? What are the developers doing in the production DB in such an enviroment? Wouldn't be best to move them off to some development server?
And maybe review the indexes... Maybe you could be better of having another index strategy?
June 7, 2005 at 2:37 pm
bad development has occurred in the past. They do not have access to production. But i have inherited this application and database. As is. wooohooo.
So, some of the ways that they deal with data is bad, which causes some indexes to get out of whack quite regularly.
And to answer the other question, yes, production can continue. But it is affected if that particular index on a table is being defragged. But since it is late at night, its less of a hassle than during the day. Or at least, i am not awake to take the support calls on its slowness at that hour... haha
December 21, 2005 at 1:28 pm
Greetings Andy,
Can you please email me the script at dcelleri@kronos.com
Thanks,
Dimas Celleri
Orlando FL, USA
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply