April 18, 2012 at 6:05 am
Hi ,
we are running a maintenance plan with the below three tasks :
1.Database integrity
2.rebuild index
3.update statistics .
The maintenance plan is running on weekends. At the time of rebuild index we are getting site alerts .
Ya i know that at the time rebuild index it will lock the tables and don't allow another user to execute the SQl stmts.
We are using SQL 2005 standard edition sp3, So online index also not possible .
Can you suggest any other alternatives to avoid this problem .
Thanks
Lavanyasri
April 18, 2012 at 7:15 am
try reorganise index, its much less intrusive. It can be less effective so monitor your fragmentation.
If your queries are mostly returning small amounts of data a little fragmentation is not such a problem, so if performance is ok dont sweat it.
Also only rebuild\reorganise those indexes that actually require it.
---------------------------------------------------------------------
April 18, 2012 at 7:46 am
I would be very careful about using reorganize as a mechanism for performance enhancements. I've seen tests run by Brad McGehee and it's only of marginal use within a very narrow range. Most of the time, rebuild works better.
I agree that you should just defrag the indexes that need it. If you're using maintenance plans and they're not doing what you need, you might look at scripting. I prefer Michelle Ufford's scripts[/url]. Also Ola Hallengren's scripts are very popular[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 18, 2012 at 8:12 am
1. In your maintaince plan step 3. Update statistics is unneccessary
since during the rebuild process the statistics are re-computer. See
2. In your maintainence plan do you determine the amoutn of index fragmentation? The following properly altered using your DB and table names will do it for you.
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO
Some experts in SQL Server recommend
Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%.
From:
April 18, 2012 at 8:21 am
+1 on Michelle's scripts. This script allows for you to evaluate the index fragmentation and then, through passed in parameters, choose which indexes you are going to be rebuild / reorg as well as what size tables you want to consider in the index maintenance run. Definitely highly recommend this script.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 18, 2012 at 8:23 am
Grant Fritchey (4/18/2012)
I would be very careful about using reorganize as a mechanism for performance enhancements. I've seen tests run by Brad McGehee and it's only of marginal use within a very narrow range. Most of the time, rebuild works better.
thats interesting, do you have a link? And do you mean within a narrow range of fragmentation? I would have to go back and check the database concerned but IIRC I have had reasonable results from reorganise.
Would agree rebuild where possible.
---------------------------------------------------------------------
April 18, 2012 at 8:25 am
george sibbald (4/18/2012)
Grant Fritchey (4/18/2012)
I would be very careful about using reorganize as a mechanism for performance enhancements. I've seen tests run by Brad McGehee and it's only of marginal use within a very narrow range. Most of the time, rebuild works better.thats interesting, do you have a link? And do you mean within a narrow range of fragmentation? I would have to go back and check the database concerned but IIRC I have had reasonable results from reorganise.
Would agree rebuild where possible.
He did a presentation on it at Connections and I had a long chat with him about it. No link. Sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 18, 2012 at 8:31 am
but it basically reinforced the 'only reorganise at low fragmentation' advice generally given?
---------------------------------------------------------------------
April 18, 2012 at 8:36 am
george sibbald (4/18/2012)
but it basically reinforced the 'only reorganise at low fragmentation' advice generally given?
Basically, but it was largely that the benefits were so small at various data ranges (he tried all different loads) and only benefiting within a very narrow range of fragmentation, that it actually was very seldom worth bothering with. Instead, when appropriate, just defrag. The load is not much more (and in some cases the same, and in rare cases, less) and the benefits are much higher.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 18, 2012 at 8:40 am
thanks Grant
---------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply