SQLServerCentral / Administering / SQL Server 2005 / Rebuilding indexes / Latest Posts

RE: Rebuilding indexes

Malcolm Daughtree — Thu, 14 May 2009 18:06:31 GMT

John,Thanks for the info, yes that helped. It is a balancing act between Pages used, Physical fargmnetation of the Datafile, Pad Index / fill factors and the like. Ahhh ! but that why we are DBA's.CodeOn:-P

RE: Rebuilding indexes

John.Sansom — Thu, 14 May 2009 01:44:37 GMT

[quote][b]Malcolm Daughtree (5/13/2009)[/b][hr]Please correct me if this is incorrect but, there are two types of index Maintenenace1. rebuilding an Index and;2. Re-organizing an IndexAnd the difference is signifigant. When you Re-organise an Index it only reorganises the index - within the existing index pages it will not change the Physical fragmentation of the Index. The index will still be scattered all over the data file - cause thats where it stores it, and you will probably not see any performance gains and the index will still be fragmented-physically, logically it will not be. Remember though that the slowest part of any RDBS is the swinging arm on the disk drive. This also is the reason you can re-organise an index 'On-line'. When you REBUILD an index, you physically remove the index from the data and rebuild the index within the Data File, usually this is contiguous. But here is the kicker if your Data file is heavily fragmented - Usually caused by the Autogrowth set to on - The index may still be and you will need to use other techniques to reduce fragmentation. The one I suggest is to do some Capacity planning on the Database and estimate the growth for a reasonable period- - say 9-12 Months and grow the Database in one go and turn Auto-growth off. I would rather automate the checking and alerting of database conditions than set and forget activities that will ultimately be forgotten and you come to work one day to a full disk drive, a suspect database and a boss none too pleased.IMHOI think I got that right.CodeOn:-P [/quote]Hi Malcolm,You are almost there.Reorganising an index can indeed reduce fragmentation however only within the intermediate levels (index pages/rather than data pages in the case of a clustered index) of the index tree structure. This is why the effectiveness is considered to be limited and why the recommended practice is to consider reorganisation of an index only when the fragmentation level is below a certain threshold.Page splits etc. can still occur within the intermediate levels of an index and so there is the possibility to reduce/remove these through reorganisation of an index.Take a look at the following MSDN reference for an explanation of the clustered index physical structure, there's a couple of nice pictures too ;-)[url=http://msdn.microsoft.com/en-us/library/ms177443.aspx]http://msdn.microsoft.com/en-us/library/ms177443.aspx[/url]Hope this helps.

RE: Rebuilding indexes

Malcolm Daughtree — Wed, 13 May 2009 16:41:57 GMT

Please correct me if this is incorrect but, there are two types of index Maintenenace1. rebuilding an Index and;2. Re-organizing an IndexAnd the difference is signifigant. When you Re-organise an Index it only reorganises the index - within the existing index pages it will not change the Physical fragmentation of the Index. The index will still be scattered all over the data file - cause thats where it stores it, and you will probably not see any performance gains and the index will still be fragmented-physically, logically it will not be. Remember though that the slowest part of any RDBS is the swinging arm on the disk drive. This also is the reason you can re-organise an index 'On-line'. When you REBUILD an index, you physically remove the index from the data and rebuild the index within the Data File, usually this is contiguous. But here is the kicker if your Data file is heavily fragmented - Usually caused by the Autogrowth set to on - The index may still be and you will need to use other techniques to reduce fragmentation. The one I suggest is to do some Capacity planning on the Database and estimate the growth for a reasonable period- - say 9-12 Months and grow the Database in one go and turn Auto-growth off. I would rather automate the checking and alerting of database conditions than set and forget activities that will ultimately be forgotten and you come to work one day to a full disk drive, a suspect database and a boss none too pleased.IMHOI think I got that right.CodeOn:-P

RE: Rebuilding indexes

maechismo_8514 — Wed, 13 May 2009 00:03:05 GMT

[quote][b]Andrew Peterson (5/12/2009)[/b][hr]That's why you need an automated script. in a 24/7 shop, it does not make sense to do manual checks. Automate the process, that includes checking the level of defrag. It's automated, and runs daily. It may not need to defrag daily, but it runs daily. [/quote]Even the automated process doesn't have to run every day it depends on the types of processes and the number of DML statements running against the server. i.e., how frequent the data in the server gets fragmented. Scheduling to run the script solely depends on the individual business.But yes, as Andrew suggested it's always a better idea to run the script daily to check for fragmentation levels.

RE: Rebuilding indexes

Andrew Peterson-472853 — Tue, 12 May 2009 19:27:51 GMT

That's why you need an automated script. in a 24/7 shop, it does not make sense to do manual checks. Automate the process, that includes checking the level of defrag. It's automated, and runs daily. It may not need to defrag daily, but it runs daily.

RE: Rebuilding indexes

maechismo_8514 — Mon, 11 May 2009 13:53:40 GMT

[quote][b]Andrew Peterson (5/11/2009)[/b][hr]For 24/7 systems, I highly recommend running a defrag at least daily. And I've had some databases that really needed it done more frequently. .[/quote]Hmm.. not really. It depends on the level of fragmentation, isn't it?

RE: Rebuilding indexes

Andrew Peterson-472853 — Mon, 11 May 2009 10:00:12 GMT

For 24/7 systems, I highly recommend running a defrag at least daily. And I've had some databases that really needed it done more frequently. If you have a solid procedure, running an automated defrag process is nothing to be afraid of. But you have to approach it more like an application, and less like a script. - Looking forward to reviewing Michelle Ufford's defrag script. At first glance, it looks more complete than the one I've written.

RE: Rebuilding indexes

John.Sansom — Mon, 11 May 2009 07:19:10 GMT

Index maintenance can be a little trickier on a reasonable sized OLTP database if you are working with the Standard Edition of SQL Server, as the online option is of course not avaialable.This is why it becomes advantageous to use a customised index maintenance stored procedure say, as you can tweak the optimisation process accordingly. i.e. determine whether to rebuild/reorganize dependant on fragmentation levels and to limit optmisations to only those specific indexes that require it etc.Take a look at Michelle Ufford's (SQLFool) [url=http://www.johnsansom.com/index.php/2009/03/automate-sql-server-index-defragmentationoptimisation/][u]stored procedure for index optimisation[/u][/url]. One of the best index optmisation scripts I have seen.Cheers,

RE: Rebuilding indexes

Grant Fritchey — Mon, 11 May 2009 06:31:41 GMT

Using the ONLINE rebuild will reduce locking & contention but will increase tempdb use, so be read for that.I wouldn't get too locked into particular time frames. It reall depends on the system. Some systems may only need indexes updated once a month. Some may need adjustments made twice a day. It really depends on data and the code running on your system. For example we have one application with a couple of tables that are, quite frankly, poorly put together. Due to internal issues we can't make changes to these tables at this time (don't ask). But, the statistics on the tables go out of data within a few hours. So, for just two tables, we're updating statistics once every two hours. It's a band-aid, not a solution, but sometimes, you'll be pulling doing the equivalent of slapping on duct-tape to fix problems in your system.

RE: Rebuilding indexes

Ryan007 — Mon, 11 May 2009 06:09:14 GMT

Thank you.

RE: Rebuilding indexes

Satish Nagaraja — Mon, 11 May 2009 03:48:06 GMT

Though SQL server provides user friendly tool to create rebuilding tasks , these tasks need to be run only once a week during weekends if posssible when the load on server is ,low.Also Iam not great fan of buliding indexes in autmated way, rather do it manually based on scripts below which has been written by SQL experts , if you are bent upon automating them you need to customize them further.- In my checklist of weekly activity run fragmentation check for tables have database pages greater than 10,000 as described below and fragmentation greater than 30% in SQL 2005SELECT name,page_count,object_id,index_type_desc,AVG_FRAGMENTATION_IN_PERCENT FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') a,sysdatabases bWHERE AVG_FRAGMENTATION_IN_PERCENT > 30 AND a.database_id = b.dbid AND page_count >10000 -- If AVG_FRAGMENTATION_IN_PERCENT < 30% use reorganize index to reduce fragmentation ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentationREORGANIZE;If AVG_FRAGMENTATION_IN_PERCENT > 30% use rebuild with online optionindex to reduce fragmentation ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentationREBUILD WITH (FILLFACTOR = 90, ONLINE=ON):-)

RE: Rebuilding indexes

BU69 — Mon, 11 May 2009 03:38:24 GMT

For an online index rebuild it means that all the tables will not be locked, for offline the tables would be locked so if you want your database to be available for the duration of your index rebuilds then the best option is to use online.

Rebuilding indexes

Ryan007 — Mon, 11 May 2009 02:05:35 GMT

Hi all,Good morning...I have some query regarding rebuilding indexes.I have created a maintenance job to rebuild index every day at 23:55. I have checked the option online indexing. Is it mean that tables/views will not be locked while rebuilding indexes.