November 27, 2008 at 11:29 am
Hi guys, I picked up a script on this site to reindex / reorganize the indexes if fragmentation is over 10% (reorganize) and 30% to rebuild.
The thing is that I run the script and it takes about 3 hours to run. Then if I rerun the script immediatly after that, it still takes 2-3 hours to run. It's like either the index didn't get defragmented, or there are some statistics that are not updated which makes the job reindex the same stuff all over again.
Can anyone shed some light on what needs to be done, or what might be happening over here?
BTW, autoshrink is off NOW (was on when I got here)... 95% + fragmentation on ALL indexes :sick:.
There's 3 GB of free space in the data files, with only 6 GB of data in the DB.
November 27, 2008 at 11:53 am
Does the script check the number of pages for the index? If not, how many of those tables are less than 100 pages?
Without seeing the script - not sure what could be happening, but I suspect that you are trying to reorganize/reindex tables that will always meet your criteria.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 28, 2008 at 4:13 am
My bad about the reorg / reindex thing, I was thinking about another script... this scripts runs in about 2H15 every night... the first time I used it, the log whent from 0 to 3.2 GB with a DB of 6 GB, here's what I use, any recommendations are welcomed :
USE [master]
GO
/****** Objet : StoredProcedure [dbo].[check_fragmented_indexes] Date de génération du script : 11/28/2008 06:09:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- This script, as is, generates of list of index defrag candidates,
-- current threshhold at 10%, on a target database with default schema
-- of dbo.
-- usage : EXEC check_fragmented_indexes 'PROD-FORDIA', 10, 1
ALTER PROCEDURE [dbo].[check_fragmented_indexes]
@dbname sysname
, @maxFragmentation INT = 10--repair if 10% fragmentation
, @Exec AS BIT = 0--0 = Print, 1 = Execute
AS
SET NOCOUNT ON
CREATE TABLE ##fragmented_indexes
(
schemaname VARCHAR(128)
, dbname VARCHAR(128)
, tablename VARCHAR(128)
, index_name VARCHAR(128)
, fragmentation FLOAT
, numpages INT
)
CREATE TABLE ##schema_table
(
schemaname VARCHAR(128)
, tablename VARCHAR(128)
)
DECLARE @query VARCHAR(5000)
SELECT @query = ' USE [' + @dbname
+ '];
INSERT INTO ##fragmented_indexes(dbname, tablename, index_name, fragmentation, numpages)
SELECT db_name(ps.database_id), object_name(ps.OBJECT_ID),
b.name, ps.avg_fragmentation_in_percent, ps.page_count
FROM sys.dm_db_index_physical_stats (db_id(''[' + @dbname
+ ']''), NULL, NULL, NULL, ''LIMITED'') AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() and avg_fragmentation_in_percent > '
+ CONVERT(VARCHAR(10), @maxFragmentation)
+ ' and b.name <> ''NULL''
ORDER BY object_name(ps.OBJECT_ID);
INSERT INTO ##schema_table
SELECT s.name, t.name from sys.tables t
LEFT JOIN sys.schemas s on (s.schema_id = t.schema_id)
ORDER BY s.name, t.name
'
EXEC (
@query
)
UPDATE ##fragmented_indexes
SET schemaname = (
SELECT schemaname
FROM ##schema_table
WHERE ##schema_table.tablename = ##fragmented_indexes.tablename
)
IF @Exec = 1
BEGIN
DECLARE @ReorganizeIndexes AS VARCHAR(MAX)
SET @ReorganizeIndexes = ''
SELECT @ReorganizeIndexes = @ReorganizeIndexes + 'ALTER INDEX ['
+ index_name + '] ON [' + @dbname + '].'
+ QUOTENAME(schemaname) + '.' + QUOTENAME(tablename)
+ ' REORGANIZE; WAITFOR DELAY ''00:00:15'';' + CHAR(13) + CHAR(10)
FROM ##fragmented_indexes
ORDER BY (SELECT NEWID()) --avoids to work on 10 indexes in a row of the same big 1GB table... hence locking it from use, in case we have to run this in higher production hours
PRINT @ReorganizeIndexes
EXEC (
@ReorganizeIndexes
)
END
ELSE
BEGIN
SELECT 'ALTER INDEX [' + index_name + '] ON [' + @dbname + '].'
+ QUOTENAME(schemaname) + '.' + QUOTENAME(tablename)
+ ' REORGANIZE; WAITFOR DELAY ''00:00:15'';' + CHAR(13) + CHAR(10)
FROM ##fragmented_indexes
ORDER BY (SELECT NEWID()) --Avoids to work on 10 indexes in a row of the same big 1GB table... hence locking it from use, in case we have to run this in higher production hours
END
DROP TABLE ##fragmented_indexes
DROP TABLE ##schema_table
SET NOCOUNT OFF
GO
November 28, 2008 at 4:56 am
Ninja's_RGR'us (11/27/2008)
Hi guys, I picked up a script on this site to reindex / reorganize the indexes if fragmentation is over 10% (reorganize) and 30% to rebuild.The thing is that I run the script and it takes about 3 hours to run. Then if I rerun the script immediatly after that, it still takes 2-3 hours to run. It's like either the index didn't get defragmented, or there are some statistics that are not updated which makes the job reindex the same stuff all over again.
Can anyone shed some light on what needs to be done, or what might be happening over here?
BTW, autoshrink is off NOW (was on when I got here)... 95% + fragmentation on ALL indexes :sick:.
There's 3 GB of free space in the data files, with only 6 GB of data in the DB.
First of all I don't see any rebuild for indexes with fragmentation above 30% in your script.
About the time to run it 2-3 hours sounds way too long for a database of 6Gb, but I noticed that the script does all databases so maybe that's why it's taking so long. One thing to remember with this kind of scripts is that the initial step about querying sys.dm_db_index_physical_stats will always take more or less the same time.
Add some check which measures the amount of time which is spent on running sys.dm_db_index_physical_stats versus the actual reindexing.
I've had databases where running sys.dm_db_index_physical_stats took about an hour and the actual index maintenance 10 minutes. But these databases are much larger than yours. Hope this helps.
[font="Verdana"]Markus Bohse[/font]
November 28, 2008 at 5:23 am
Again about the 10%/30%, I was thinking about another version of the script that I wanted to implement...
The query of the dm views runs in around 2 minutes (over 50 trial runs).
I'll try removing the waitfor and see how long it takes to run the reorganize then... maybe the process takes 15 minutes to run with 2H00 of wait.
I'll give a live run this week-end while most of the staff is off the DB, so that I can remove the waitfor.
If any of you guys have a working script that you can send over, that'd be more than welcome. The kind of script that includes the 10%/30% check, # of pages and update of stats where necessary. I'm not sure my understanding of the matter is sufficient to make a good script here!
TIA.
November 28, 2008 at 7:09 am
If you're looking in the script section of this site you will find at least 3 scripts. Give a try on a test server and see which will suite you best.
[font="Verdana"]Markus Bohse[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply