SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuilding indexes


Rebuilding indexes

Author
Message
Ryan007
Ryan007
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4008 Visits: 1595
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//
BU69
BU69
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 283
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.
Satish Nagaraja
Satish Nagaraja
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 308
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 reorganize index to reduce fragmentation

ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentation
REORGANIZE;

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 :-)
Ryan007
Ryan007
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4008 Visits: 1595
Thank you.

Ryan
//All our dreams can come true, if we have the courage to pursue them//
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145987 Visits: 33199
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
John.Sansom
John.Sansom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2767 Visits: 1558
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. One of the best index optmisation scripts I have seen.

Cheers,


John Sansom (@sqlBrit) | www.johnsansom.com
Andrew Peterson
Andrew Peterson
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2242 Visits: 750
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.
maechismo_8514
maechismo_8514
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6722 Visits: 2228
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?
Andrew Peterson
Andrew Peterson
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2242 Visits: 750
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.
maechismo_8514
maechismo_8514
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6722 Visits: 2228
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search