October 6, 2008 at 3:39 pm
Can any one give me ideas on how to automate of indefrag and rebuild index job. Right now we have a maintenance plan that does it but it really sucks the performance out of the database when it runs. I am thinking that may be I can stagger the rebuild out over the week instaed slamming them all on one day. I am also thinking that there are a few tables that hold static data and don't even need to be defragged. Any advise would be great.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 6, 2008 at 3:47 pm
One thing I would recommend is to automate regular reports to check which tables are fragmented beyond what you would call a reasonable limit. Store these table names in a table.
The next step would be to loop thru the table names you have stored and defrag/rebuild each table. You can decide how often, how many tables per run, etc, you want to do.
Does this sound like a reasonable starting point?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 6, 2008 at 3:51 pm
That sounds right where I want to go with this. Do have any examples of how this could be done. I really don't want to re-invent the wheel.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 7, 2008 at 8:24 am
Another question that I have that is related to indexing is we have a number of tables with Text datatype (blob) which seems to be be picked up in the ShowContig with the indexid of 255. It seems to slow down the whole process. From what I have seem can I exclude these indexes from the SHOWCOntig rountine? or is there a faster way to do them?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 7, 2008 at 8:52 am
Arthur.Lorenzini (10/6/2008)
That sounds right where I want to go with this. Do have any examples of how this could be done. I really don't want to re-invent the wheel.
I do not have any examples I can post but I would recommend that you first search this site looking for scripts related to fragmentation. If my memory serves be right there have been several posted. I would look for one that stores the results in a table, or one that can easily be modified to do so.
Once you've set up a way to regularly see what you've got you can take a look at how to make it better, i.e. how to defrag/reindex.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 7, 2008 at 9:20 am
I found the following script that I have been testing and it seems to work except for an issue with BLOBs.
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 table/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
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
FROM #FragmentationResult
WHERE ScanDensity < 80 --Scan Density is low
AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
AND PAGES > 8 --Not a very small table
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 '** De-fragmentation #' + CAST(@id AS VARCHAR(15))+ ' **'
PRINT 'DBCC DBREINDEX on [' + @oname + '].[' + @iname
+ '] with ScanDensity = ' + CAST(@sdensity AS VARCHAR(15)) + '%'
DBCC DBREINDEX(@oname, @iname)
SET @TableCnt = @TableCnt - 1
END
--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 8, 2008 at 2:22 pm
I have like 5 similar scripts now to do index rebuild/reorganize, and I get confused all the time
Take a look at Pinal's article
http://blog.sqlauthority.com/?s=index+fragment
October 8, 2008 at 3:17 pm
Does it do any good to defrag BLOB indexes? I also have several cases I have heap table (no clustered indexes )? These table do not have clustered indexes because the clustered indexes cause contention because they are so large. ANy ideas what I should do?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply