REINDEX and INDEXDEFRAG

I am currently working on researches on INDEXDEFRAG and REINDEX. I
would like to know what is the best thing to use REINDEX or
INDEXDEFRAG' Since we are currently maintaining a large amount of
data(about 20 Gig) and we are in a hospital that is why i'm finding a
way to do indexing that will not lock the system so the users can still
use the system
thank you..You would want to check the following article for the
differences, guidelines for implementing in your
environment:
Microsoft SQL Server 2000 Index Defragmentation Best
Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
-Sue
On 20 Mar 2006 19:39:04 -0800, "Ludy Doctor"
<mismld@.hsc.dlsu.edu.ph> wrote:
>I am currently working on researches on INDEXDEFRAG and REINDEX. I
>would like to know what is the best thing to use REINDEX or
>INDEXDEFRAG' Since we are currently maintaining a large amount of
>data(about 20 Gig) and we are in a hospital that is why i'm finding a
>way to do indexing that will not lock the system so the users can still
>use the system
>thank you..|||Hi Sue!!!! thanks for that...one more thing...can you help me set up a
failover cluster in SQL 2000?|||Check the following article:
SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
It's also explained in books online - check the help topic:
Failover Clustering
-Sue
On 21 Mar 2006 00:40:04 -0800, "Ludy Doctor"
<mismld@.hsc.dlsu.edu.ph> wrote:
>Hi Sue!!!! thanks for that...one more thing...can you help me set up a
>failover cluster in SQL 2000?

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
>
>.
>