February 12, 2008 at 12:32 pm
Should I schedule a regular job to do an index defrag ?
February 12, 2008 at 1:00 pm
maybe 😉
We schedule index rebuilds on most of our server on a weekly basis.
(during low usage hours)
On some servers it is scheduled every day because we are not allowed
to change clustering indexes.
On your SQL2005EE we use online rebuilds wherever possible.
ALTER INDEX ALL ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD WITH ( ONLINE = ON );
If the load is to high, you may want to select based on fragmentation.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 12, 2008 at 1:52 pm
We do it all the time but computing fragmetation level is paramount if you have to do it intraday 😉
* Noel
February 12, 2008 at 3:07 pm
Yes.
But the details vary.
For example, if I ran an index defrag on my main production server all at once, it would run longer than the time-slot I have available for it. So I run some tables on the Sunday night, some Monday night, etc.
I do index rebuilds on a monthly basis, using a similar scheme.
I have another server where there are 25 indexes on the whole server (the prior DBA was more than a little incompetent). I can rebuild all of those in 8 seconds currently, so I do a blanket rebuild every night. (I'll change that as I get the server more under control.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 13, 2008 at 2:53 am
Depends how fast your indexes get fragmented.
I've got a job that does a contig check on each table each week and records the results. It then will go and rebuild indexes of any table with fragmentation over a threshold value (currently 25%)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply