| Author |
Topic |
|
izaltsman
A custom title
1139 Posts |
 Posted - 2003-06-03 : 18:21:36
|
| Hi, All! Does anyone here have a system that has to be available 24x7? How do you reindex your tables when you don't have any good maintenance windows? What do you do in order to minimize the impact on your system? DBCC INDEXDEFRAG is great, and I run it all the time, but I would like to rebuild the indexes from scratch periodically. And DBCC DBREINDEX is way too heavy to run on a live system -- my app becomes completely unusable while the thing runs... Any thoughts are greatly appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 18:44:04
|
| All of the systems that I support in production are 24x7. One requires 99.999% availability according to the SLA. We do the DBCC DBREINDEX about twice per week. We run it very early in the morning when usage is minimal. We have not heard any complaints about the system slowing down during the reindex. On one system, we broke up the DBCC DBREINDEX so that it only ran certain tables on one day then other tables on another day, then still other tables on yet another day. The way that we split it up was according to the table size so that no more 3 large tables were reindexed each night.Tara |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-06-04 : 02:50:18
|
| short of switching to Oracle, you are SOL.I think Yukon will have the online index rebuild feature. As suggested earlier, rebuilding during off peak hours is probably your best bet right now.-ec |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2003-06-04 : 09:25:05
|
| Tara, ec, thanks for your input!Tara, how long does the reindex take on your larger tables? Do you know what the average value of the PhysicalDisk:%DiskTime PerfMon counter is at the time when you reindex a large table? What's your RAID configuration? Is your table data and indexes reside on different filegroups? I just want to compare your setup to mine, although at this point I know that the disk subsystem is what's slowing my reindex down... I am almost sure that the best thing I can do to improve the situation is to upgrade it... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-04 : 12:02:47
|
quote:
Tara, how long does the reindex take on your larger tables? Do you know what the average value of the PhysicalDisk:%DiskTime PerfMon counter is at the time when you reindex a large table? What's your RAID configuration? Is your table data and indexes reside on different filegroups?
On one table that is 5GB (our biggest table so far), it takes only 5 minutes to rebuild it. But that's on very, very expensive hardware. On our less expensive setup, it takes about 20 minutes to do our next largest table, although I don't know what the size is right now. I do not know what the average value of the PhysicalDisk:%DiskTime is during the reindex. We only run PerfMon during the day (and during that time it is only it less than 5%). We are running RAID5 here. We do not split up our tables and indexes on different filegroups.Tara |
|
|
|
srf
Starting Member
42 Posts |
Posted - 2003-06-04 : 12:39:24
|
| It takes several hours to dbcc dbreindex some of my larger tables, in the 35 gig range, with 8 procs and 8 gigs of RAM, and very fast disks. The table has a few indexes, one being clustered.If you aren't allowed any maintenance windows you're kinda screwed. Maybe have everything read-only for a few hours while you fail over to a second server and do reindexing. Or have the app designed to point to a second table while you reindex the first. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2003-06-04 : 13:22:48
|
| Thanks for your feedback everyone!Sounds like I definitely need to upgrade my hardware -- mine isn't even getting 1/10th the speed... |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-11-26 : 08:42:41
|
quote:
Originally posted by srf
It takes several hours to dbcc dbreindex some of my larger tables, in the 35 gig range, with 8 procs and 8 gigs of RAM, and very fast disks. The table has a few indexes, one being clustered.If you aren't allowed any maintenance windows you're kinda screwed. Maybe have everything read-only for a few hours while you fail over to a second server and do reindexing. Or have the app designed to point to a second table while you reindex the first.
How sure are you that your disks are that fast, srf ?I ask because we did a DBCC DBREINDEX on our full database (160 gb) on EMC that we are having significant IO queue length issues , and it ran in 3h45m. That is doing 810 indexes... WARNING : if you do this on SQL 2K, watch your LOG files 1. We got caught out by the log file growth, since DBREINDEX is a logged operation, and were slightly red-faced with our 60GB log file... We considered that slow.We are also moving to a system of staggered reindexing of tables (similar, it sounds to Tara), based on a analysis job that determine a tables needs, based on scan densities and logical fragmentation. This will enable us to not reindex things that don't need it, and reindex things that do need it, more often than once weekly. Quite a bit of work doing the determination of important tables in our 740 table database, but we are looking forward to implementing in a few months.CiaO*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
Reindexing on a 24x7 system,