Reorganizing my indexes during working hours

Question

Reorganizing my indexes during working hours

noncentz

Mr or Mrs. 500

Points: 580
More actions
December 3, 2010 at 9:32 am

#142765

Good Morning everyone,
I have some slightly fragmented indexes that I would like to reorganize but the database is in use most of the day. I know index rebuilds happen 'online' But I don't want the process to say, lock any rows so that performance is affected badly.
Any thoughts on this?
Noncentz

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

I would not rebuild/reorganize the indexes during business hours unless the fragmentation itself it causing a high severity incident.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Robert Biddle SSC Eights! Points: 812 More actions · Answer 2

It can cause some trouble, but it can be done. My recommendation would be to use some sort of intelligent means of managing your indexes. Only rebuild indexes that need to be rebuilt, consider reorganizing others. It will use up system resources pretty signficantly. Will it bring your system to a complete halt? It really depends on how good your hardware is for your applications.

Please note, that despite their name, Online indexes can actually cause blocking. So that may be something you want to take into consideration.

You should also probably consider changing FillFactors on your indexes. This will slow how much they fragment but will consume more disk space. The best way of going about this, in my opinion, is to log your index fragmentation before you run your maintenance procedures. This way you can see how fragmented you were each time before your process runs. Those which are in the 80's and 90+, seriously consider giving them a different FillFactor.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Reorg the Indexes if the Fragmentation level is > 5% and <30%

Rebuild the Indexes if the Fragmentation level is > 30%

It's better to do reorg on Nightly basis and Rebuild during the weekends. Not recommended during the business hours.

Index Rebuilds are online, only for the Enterprise edition of the SQL Server.

You can use Maintenance Plans or Custom scripts to do that.

http://ola.hallengren.com/

Thank You,

Best Regards,

SQLBuddy.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

To deternine the extent of index fragmentation you may want to execute some code like: (this found in a SSC posting so long ago that I can not remember the authors name to give credit where credit is due.)

SELECT OBJECT_NAME(OBJECT_ID) AS Tablename,s.name AS Indexname

,index_type_desc

,avg_fragmentation_in_percent

,page_count

FROM sys.dm_db_index_physical_stats(DB_ID('TipsNTricks'),DEFAULT,DEFAULT,DEFAULT,DEFAULT) d

join sysindexes s ON d.OBJECT_ID = s.id

and d.index_id = s.indid

WHERE Index_Type_Desc <> 'HEAP'AND avg_fragmentation_in_percent >30

For a good discussion of how this might effect production operations, and how to ease the load on a production server/database read this blog post

http://www.sqlservercentral.com/blogs/sqldownsouth/archive/2009/9/25/trace-flag-1118.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

noncentz Mr or Mrs. 500 Points: 580 More actions · Answer 5

Thank you for all the thoughtful responses. I guess the answer is no, I should not reorganize or rebuild during working hours. I think the best way for me to tackle this is to use a script to run nightly and specify that between 5 and 30 organize, and 30 > will be rebuilt. I am certain I have seen this script around before... just never used it.

C Ya

Noncentz

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

anolan

In addition to prior forum replies, may I also refer you to the following Blog post:

http://adventuresinsql.com/2010/09/how-is-fill-factor-impacting-my-indexes/

It contains sample code that returns a large amount of index data, and discuses a little thought of Index factor (Fill Factor) and it's effect on performance. I believe you will find it informative and helpful.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

anolan (12/3/2010)
Thank you for all the thoughtful responses. I guess the answer is no, I should not reorganize or rebuild during working hours. I think the best way for me to tackle this is to use a script to run nightly and specify that between 5 and 30 organize, and 30 > will be rebuilt. I am certain I have seen this script around before... just never used it.
C Ya
Noncentz

Also monitor the log expansion and space management.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 8

noncentz (12/3/2010)
Good Morning everyone,
I have some slightly fragmented indexes that I would like to reorganize but the database is in use most of the day. I know index rebuilds happen 'online' But I don't want the process to say, lock any rows so that performance is affected badly.
Any thoughts on this?
Noncentz

Ummmm.... just to be sure... IIRC, "online" rebuilds only occur in the Enterprise Edition of SQL Server 2005.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nigel. SSChampion Points: 11636 More actions · Answer 9

Try this http://sqlfool.com/2010/04/index-defrag-script-v4-0/[/url]

Nigel
How to post questions

noncentz Mr or Mrs. 500 Points: 580 More actions · Answer 10

SSCrazy - Thanks for the script man I have been testing it for 2 days with no problems whatsoever as long as I manage the growth of my transaction log.

I didn't realize I couldn't run Rebuilds online with my version of SQL server but I am convinced this was a bad idea to begin with.

Thanks for the help....... cheers

Nadrek SSC-Insane Points: 20039 More actions · Answer 11

Rebuilding small indexes during business hours in systems that aren't highly time-critical isn't a problem; if the table's locked for 2 seconds, does anyone care? If they do... don't try it. If they don't... then perhaps it's all right.

Here's some 2000-compatible, relatively primitive code I use for determining table size in my automated index maintenance scripts - I rebuild small indexes during a short nightly maintenance window (the parameters are tuned so it takes, total, 3 minutes or less), larger indexes on weekends in a larger window, and if the largest indexes take longer, then I'll do them every 2 weeks.

Table size was important in my 2000 code because it related to how long a DBCC SHOWCONTIG would take.

SET @execstr = 'USE [' + @DBName + '];' -- start out by selecting the database to work in

SET @execstr = @execstr + ' SELECT t.TABLE_SCHEMA + ''.'' + t.TABLE_NAME'

SET @execstr = @execstr + ' FROM INFORMATION_SCHEMA.TABLES t' -- update this for SQL Server 2005

SET @execstr = @execstr + ' INNER JOIN sysobjects so'

SET @execstr = @execstr + ' ON so.name = t.table_name'

SET @execstr = @execstr + ' INNER JOIN sysindexes si'

SET @execstr = @execstr + ' ON si.id = so.id'

SET @execstr = @execstr + ' WHERE t.TABLE_TYPE = ''BASE TABLE'' '

SET @execstr = @execstr + ' AND so.xtype = ''U'' '

SET @execstr = @execstr + ' GROUP BY t.table_schema, t.table_name'

SET @execstr = @execstr + ' HAVING CASE '

SET @execstr = @execstr + ' WHEN SUM(CONVERT(BIGINT,Reserved)) > SUM(CONVERT(BIGINT,dpages)) THEN SUM(CONVERT(BIGINT,Reserved))'

SET @execstr = @execstr + ' ELSE SUM(CONVERT(BIGINT,Dpages))'

SET @execstr = @execstr + ' END BETWEEN ' + CONVERT(VARCHAR(19),@minSumReservedOrDPagesWhicheverLargerToLookAt) + ' AND ' + CONVERT(VARCHAR(19),@maxSumReservedOrDPagesWhicheverLargerToLookAt)

Ola Hallengren's work is one of the very few maintenance scripts I've seen that includes a "stop if you run over X time" provision.

If you have strict maintenance windows, then you need to code yourself up a much more tedious, though not difficult, predictive mechanism - the "don't do _this_ index if it's likely to end up running over X time" idea, where you don't start a 3 hour index rebuild 2 minutes before the end of your maintenance window. Another useful trick is to add a "don't do any rebuild that is expected to take over N seconds" check; so if you _really_ need to, you can simply run all <5 second rebuilds, and know that you're unlikely to lock any given index for more than 5-7 seconds. Pauses between rebuilds are a potentially useful option, though the total runtime can increase dramatically.

Should you wish to predict, note that clustered and nonclustered indexes rebuild at significantly different speeds; don't try one number to predict them both. I've found "pages per second" to be the best (still poor) predictor of index rebuild/reorg speeds on SQL 2000. A reasonable solution starts off with pre-measured, passed in (because it can vary, DB to DB) starting values, and after a certain amount of work, replace those with a calculation of the actual times the maint procedure has spent.

Note that the rebuild >30 frag and reorg < 30 frag guidelines are arbitrary; use your own judgement. Personally, when I have a maintenance window to work with, I always rebuild - the time would go to waste otherwise, and rebuilding also significantly helps scan density.