May 11, 2009 at 2:05 am
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.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 11, 2009 at 3:38 am
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.
May 11, 2009 at 3:48 am
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 2005
SELECT 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 b
WHERE AVG_FRAGMENTATION_IN_PERCENT > 30
AND a.database_id = b.dbid
AND page_count >10000
-- If AVG_FRAGMENTATION_IN_PERCENT 30% use rebuild with online option
index to reduce fragmentation
ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentation
REBUILD WITH (FILLFACTOR = 90, ONLINE=ON)
🙂
Cheer Satish 🙂
May 11, 2009 at 6:09 am
Thank you.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 11, 2009 at 6:31 am
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.
"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
May 11, 2009 at 7:19 am
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) stored procedure for index optimisation[/url]. One of the best index optmisation scripts I have seen.
Cheers,
May 11, 2009 at 10:00 am
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.
The more you are prepared, the less you need it.
May 11, 2009 at 1:53 pm
Andrew Peterson (5/11/2009)
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..
Hmm.. not really. It depends on the level of fragmentation, isn't it?
May 12, 2009 at 7:27 pm
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.
The more you are prepared, the less you need it.
May 13, 2009 at 12:03 am
Andrew Peterson (5/12/2009)
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.
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.
May 13, 2009 at 4:41 pm
Please correct me if this is incorrect but, there are two types of index Maintenenace
1. rebuilding an Index and;
2. Re-organizing an Index
And 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.
IMHO
I think I got that right.
CodeOn
😛
May 14, 2009 at 1:44 am
Malcolm Daughtree (5/13/2009)
Please correct me if this is incorrect but, there are two types of index Maintenenace1. rebuilding an Index and;
2. Re-organizing an Index
And 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.
IMHO
I think I got that right.
CodeOn
😛
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 😉
http://msdn.microsoft.com/en-us/library/ms177443.aspx
Hope this helps.
May 14, 2009 at 6:06 pm
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
😛
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply