January 25, 2008 at 8:01 am
If I select the option to Rebuild Indexes, does this include all the steps that Reorganise Index step would perform? So i f I select Rebuild Indexes I would not have to select Reorganise Index as this would laready be happening?
January 25, 2008 at 8:12 am
Rebuild does more than Reorganize. Microsoft recommends rebuilding if fragmentation is over 30%, and reorganizing if fragmentation is between 5% and 30%, and leaving the index alone if fragmentation is below 5%.
See: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm, article on Rebuilding and Reorganizaing indexes, in Books Online, for more details.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2008 at 8:23 am
Thanks - that's great. So a Rebuild does all a Reorganise does and more?
January 25, 2008 at 11:33 am
Yes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2008 at 5:56 am
Specifically, rebuild will update statistics with a full scan (so don't manually update stats too otherwise you could end up with worse stats if your default is to do a sampled scan), and it will do a better job (in some circumstances) of removing fragmentation than a reorganize.
The drawback is that it *always* rebuilds the whole index, no matter how much fragmentation there is whereas reorganize will only remove the fragmentation that exists.
Beware of rebuilding all indexes in a database every day as many people do - you should only be removing fragmentation when that fragmentation is contributing to workload performance degradation.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 26, 2008 at 9:42 am
Paul Randal (1/26/2008)
Beware of rebuilding all indexes in a database every day as many people do - you should only be removing fragmentation when that fragmentation is contributing to workload performance degradation.
Paul - just curious: is this just to avoid the extra workload (meaning - it's just unnecessary and wasteful), or is there another reason to avoid it?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 26, 2008 at 4:12 pm
Just to avoid the extra workload. Every index you rebuild unnecessarily means:
- potential for data file growth
- depending on how you rebuild, reduced concurrency on the table (for offline rebuild) or load on tempdb (for online rebuild)
- extra space used in log backups
- extra info shipped in log shipping
- extra info shipped in a database mirroring partnership (and the index rebuild will be fully logged)
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 29, 2008 at 5:25 pm
Got it. Kind of what I expected to hear, but the BEWARE in your warning made me doubt myself...:)
Thanks!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 30, 2008 at 12:21 am
Practically instead of 30% fragmetation limit, Index should be rebuild when index fragmentation is great than 35-40%. Index should be reorganized when index fragmentation is between 10% to 35-40%. Index rebuilding process uses more CPU and it locks the database resources. You can turn on rebuild indexes online option when Index is rebuilt. It is important that you experiment to determine the best threshold for your environment.
May 1, 2008 at 9:46 am
Paul Randal (1/26/2008)
Specifically, rebuild will update statistics with a full scan (so don't manually update stats too otherwise you could end up with worse stats if your default is to do a sampled scan), and it will do a better job (in some circumstances) of removing fragmentation than a reorganize.
With this in mind, would I still want to update column statistics? Since I would like like to run a weekly job to maintain my indexes, is there a way have it detect the percent fragmentation to determine if a reorganize or rebuild should be done?
May 1, 2008 at 12:21 pm
For statistics that aren't on indexed columns, yes, you may want to manually update these if they get out of date and the auto update hasn't kicked in (e.g. if you've changed the data distribution such that there's now a skewed distribution of values).
Yes - look in Example E of DBCC SHOWCONTIG or Example D (I think) of sys.dm_db_index_physical_stats - and only remove fragmentation in indexes where it's going to be worthwhile (see the discussion above).
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 1, 2008 at 1:02 pm
Many thanks, the example is perfect. One more thought - If I setup that script to run weekly, would it ever be beneficial to just do a rebuild of ALL indexes periodically, maybe once a month, to make sure that stats get updated properly for the indexes that never reach a level of fragmentation to cause a rebuild?
May 1, 2008 at 3:56 pm
> ... or Example D (I think) of sys.dm_db_index_physical_stats - and only remove fragmentation in indexes where it's going to be worthwhile
I have a stored procedure that you could also use for this. It supports about the same logic as the example in Books Online. It can also be used to update statistics on indexes that have been reorganized.
http://ola.hallengren.com/Documentation.html
Ola Hallengren
May 1, 2008 at 8:59 pm
Another thing to keep in mind is that on SQL Server 2005, sp_updatestats has been modified to only update statistics on tables that need it. If you use the maintenance plan plug-in to update statistics, this plug-in generates an update statement for every table in the database.
The difference in processing can be dramatic. I reduced that step in my process from 3 hours to less than 30 minutes by using the procedure instead.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply