Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Rebuilding indexes Expand / Collapse
Author
Message
Posted Monday, May 11, 2009 2:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:21 AM
Points: 1,602, Visits: 1,572
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//
Post #713904
Posted Monday, May 11, 2009 3:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:22 AM
Points: 133, 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.
Post #713945
Posted Monday, May 11, 2009 3:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:21 AM
Points: 169, Visits: 286
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
Post #713950
Posted Monday, May 11, 2009 6:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:21 AM
Points: 1,602, Visits: 1,572
Thank you.

Ryan
//All our dreams can come true, if we have the courage to pursue them//
Post #714008
Posted Monday, May 11, 2009 6:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #714028
Posted Monday, May 11, 2009 7:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:03 AM
Points: 351, Visits: 1,556
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
Post #714085
Posted Monday, May 11, 2009 10:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 12, 2014 9:23 AM
Points: 206, Visits: 402
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.
Post #714283
Posted Monday, May 11, 2009 1:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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?

Post #714463
Posted Tuesday, May 12, 2009 7:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 12, 2014 9:23 AM
Points: 206, Visits: 402
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.
Post #715578
Posted Wednesday, May 13, 2009 12:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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.




Post #715680
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse