<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Rebuilding indexes
13 posts, Page 1 of 2
1
2
»»
Rebuilding indexes
Rate Topic
Display Mode
Topic Options
Author
Message
Ryan007
Ryan007
Posted Monday, May 11, 2009 2:05 AM
SSCommitted
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:43 AM
Points: 1,564,
Visits: 1,488
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
BU69
BU69
Posted Monday, May 11, 2009 3:38 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, September 20, 2012 1:35 AM
Points: 133,
Visits: 280
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
Satish Nagaraja
Satish Nagaraja
Posted Monday, May 11, 2009 3:48 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, March 29, 2013 2:25 AM
Points: 164,
Visits: 251
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
Ryan007
Ryan007
Posted Monday, May 11, 2009 6:09 AM
SSCommitted
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:43 AM
Points: 1,564,
Visits: 1,488
Thank you.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
Post #714008
Grant Fritchey
Grant Fritchey
Posted Monday, May 11, 2009 6:31 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 13,380,
Visits: 25,164
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #714028
John.Sansom
John.Sansom
Posted Monday, May 11, 2009 7:19 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 2:16 AM
Points: 343,
Visits: 1,454
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.sqlbrit.com
The SQLBrit Community Forum
- "There's so more to being a Data Professional than just technology."
Post #714085
Andrew Peterson-472853
Andrew Peterson-472853
Posted Monday, May 11, 2009 10:00 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, January 16, 2013 7:40 AM
Points: 136,
Visits: 259
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
maechismo_8514
maechismo_8514
Posted Monday, May 11, 2009 1:53 PM
Ten 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
Andrew Peterson-472853
Andrew Peterson-472853
Posted Tuesday, May 12, 2009 7:27 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, January 16, 2013 7:40 AM
Points: 136,
Visits: 259
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
maechismo_8514
maechismo_8514
Posted Wednesday, May 13, 2009 12:03 AM
Ten 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 »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
