Reindex

How do you keep a system running optimum if you can't run
dbcc reindex because it locks the tables? The indexdefrag
has no perceptible performance increase, or very little
compared to rebuilding the indexes.
The db is about 60G. If we cant run the reindex because
of other jobs running all night that would get killed by
it, the speed the next day is affected, searches take
longer, concurrency gets affected, etc.
MichelIt's hard to believe that indexdefrag has little effect. If one nights
worth of work fragments the tables that badly then you might want to change
the fill factor or possibly rethink the clustered index. How fragmented do
the tables get after just one night of work?
--
Andrew J. Kelly
SQL Server MVP
"Michel" <michel@.micro-world.ca> wrote in message
news:081501c37735$a9c52ef0$a101280a@.phx.gbl...
> How do you keep a system running optimum if you can't run
> dbcc reindex because it locks the tables? The indexdefrag
> has no perceptible performance increase, or very little
> compared to rebuilding the indexes.
> The db is about 60G. If we cant run the reindex because
> of other jobs running all night that would get killed by
> it, the speed the next day is affected, searches take
> longer, concurrency gets affected, etc.
> Michel|||dbcc showcontig shows between 10 - 50% fragmentation on
the tables.
Some has high as 100% (log tables with lots of inserts).
The fill factor is 80% on tables. Its really not that much
new data.
All tables have a decent clustered index. It's just a
matter of one or
2 tables. This DB is fully relational with about 300
related tables.
It just seems that when data is inserted, the index just
dosent work
well anymore until I reindex. I may be missing something?
80% fill
factor on a table with 10M rows, mostly queried all day,
maybe 20-50 new
records added not much more. All the related tables get a
few records
here and there inserted. I can give more details but not
sure wich
details to give to help.
Michel
>--Original Message--
>It's hard to believe that indexdefrag has little
effect. If one nights
>worth of work fragments the tables that badly then you
might want to change
>the fill factor or possibly rethink the clustered
index. How fragmented do
>the tables get after just one night of work?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Michel" <michel@.micro-world.ca> wrote in message
>news:081501c37735$a9c52ef0$a101280a@.phx.gbl...
>> How do you keep a system running optimum if you can't
run
>> dbcc reindex because it locks the tables? The
indexdefrag
>> has no perceptible performance increase, or very little
>> compared to rebuilding the indexes.
>> The db is about 60G. If we cant run the reindex because
>> of other jobs running all night that would get killed by
>> it, the speed the next day is affected, searches take
>> longer, concurrency gets affected, etc.
>> Michel
>
>.
>