reindexing, rebuilding, etc

hi, new to sql server (2000)..in my previous database, we had to do
maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
database is relatively small (8G)but the reindexing in sql server is done in
minutes, whereas in our old system it used to take up to 4 hrs...Is sql
server that fast or am I doing something wrong?
also, what routines are recommended for regular maintenance and should I do
individually or create a plan?
Thanks,
GerryUse the code found in Books Online, DBCC SHOWCONTIG for reindexing, and read
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
first.
As for the other things:
Backup schedule and types is determined by the owner of the system.
I prefer to do DBCC CHECKDB as often as I can. I prefer to do it as often as
I do db backup. This
was, if I get a corruption, I can do a log backup, restore the most recent d
b backup and all
subsequent log backups and the corruption probably not re-introduced by rest
oring the log backup.
Your mileage will vary, so just put in some common sense into planning these
things.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done
in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I d
o
> individually or create a plan?
> Thanks,
> Gerry|||Thank you very much.
"Tibor Karaszi" wrote:

> Use the code found in Books Online, DBCC SHOWCONTIG for reindexing, and re
ad
> http://www.microsoft.com/technet/pr...ver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
>|||Gerry M wrote:
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done
in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I d
o
> individually or create a plan?
> Thanks,
> Gerry
Hi Gerry... I have a script that I run that checks the fragmentation of
all my indexes, and rebuilds those that are badly fragmented. My site
is currently "in limbo", but you can obtain the script from Google's
cache. Search for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded"
Tracy McKibben
MCDBA
http://www.realsqlguy.com

reindexing, rebuilding, etc

hi, new to sql server (2000)..in my previous database, we had to do
maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
database is relatively small (8G)but the reindexing in sql server is done in
minutes, whereas in our old system it used to take up to 4 hrs...Is sql
server that fast or am I doing something wrong?
also, what routines are recommended for regular maintenance and should I do
individually or create a plan?
Thanks,
GerryUse the code found in Books Online, DBCC SHOWCONTIG for reindexing, and read
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx first.
As for the other things:
Backup schedule and types is determined by the owner of the system.
I prefer to do DBCC CHECKDB as often as I can. I prefer to do it as often as I do db backup. This
was, if I get a corruption, I can do a log backup, restore the most recent db backup and all
subsequent log backups and the corruption probably not re-introduced by restoring the log backup.
Your mileage will vary, so just put in some common sense into planning these things.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I do
> individually or create a plan?
> Thanks,
> Gerry|||Thank you very much.
"Tibor Karaszi" wrote:
> Use the code found in Books Online, DBCC SHOWCONTIG for reindexing, and read
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx first.
> As for the other things:
> Backup schedule and types is determined by the owner of the system.
> I prefer to do DBCC CHECKDB as often as I can. I prefer to do it as often as I do db backup. This
> was, if I get a corruption, I can do a log backup, restore the most recent db backup and all
> subsequent log backups and the corruption probably not re-introduced by restoring the log backup.
> Your mileage will vary, so just put in some common sense into planning these things.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
> > hi, new to sql server (2000)..in my previous database, we had to do
> > maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> > database is relatively small (8G)but the reindexing in sql server is done in
> > minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> > server that fast or am I doing something wrong?
> >
> > also, what routines are recommended for regular maintenance and should I do
> > individually or create a plan?
> >
> > Thanks,
> > Gerry
>|||Gerry M wrote:
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I do
> individually or create a plan?
> Thanks,
> Gerry
Hi Gerry... I have a script that I run that checks the fragmentation of
all my indexes, and rebuilds those that are badly fragmented. My site
is currently "in limbo", but you can obtain the script from Google's
cache. Search for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded"
Tracy McKibben
MCDBA
http://www.realsqlguy.com

re-Index advice

We periodically rebuild our indexes when we notice defragmentation of
indexes however after rebuilding the indexes we do not recompile the
stored procedure. My experience has been that after rebuiling the
indexes we ususlly notice a performance gain right away however I was
wondering if it is also necessary to recompile all our stored
procedures after the indexes are rebuilt. What is the best practise?
ThanksWhen you reindex an index it will by default update the stats which
invalidate the plans for anything that references them. That in turn will
force the recompile of the plan the next time it is used. So in short you do
not need to recompile after an index rebuild as it is already done for you.
--
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154619914.120880.28460@.b28g2000cwb.googlegroups.com...
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>|||shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>
Defragging the indexes shouldn't require recompiling anything, unless
the indexes were so badly fragmented that the optimize was ignoring them
and using a sub-optimal execution plan. I personally have never had to
do this.
You don't say how you "notice" fragmentation, but you might consider an
automated approach that runs nightly or weekly. See my site for one
possible method:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Andrew that makes sense. What would be the best way to check to
make sure that the stored proc's are indeed getting recompiled?
Thanks
shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks|||Well it is not something one generally has to check on. If you know the
stats are getting updated (you can see with DBCC SHOWSTTISTICS) then you can
count on any plans that reference those stats to get recompiled the next
time they are run. This is true in all cases unless you have used the KEEP
PLAN or KEEP FIXED PLAN hints in the query.
--
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154694889.877119.181210@.p79g2000cwp.googlegroups.com...
> Thanks Andrew that makes sense. What would be the best way to check to
> make sure that the stored proc's are indeed getting recompiled?
> Thanks
>
> shub wrote:
>> We periodically rebuild our indexes when we notice defragmentation of
>> indexes however after rebuilding the indexes we do not recompile the
>> stored procedure. My experience has been that after rebuiling the
>> indexes we ususlly notice a performance gain right away however I was
>> wondering if it is also necessary to recompile all our stored
>> procedures after the indexes are rebuilt. What is the best practise?
>> Thanks
>

re-Index advice

We periodically rebuild our indexes when we notice defragmentation of
indexes however after rebuilding the indexes we do not recompile the
stored procedure. My experience has been that after rebuiling the
indexes we ususlly notice a performance gain right away however I was
wondering if it is also necessary to recompile all our stored
procedures after the indexes are rebuilt. What is the best practise?
ThanksWhen you reindex an index it will by default update the stats which
invalidate the plans for anything that references them. That in turn will
force the recompile of the plan the next time it is used. So in short you do
not need to recompile after an index rebuild as it is already done for you.
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154619914.120880.28460@.b28g2000cwb.googlegroups.com...
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>|||shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>
Defragging the indexes shouldn't require recompiling anything, unless
the indexes were so badly fragmented that the optimize was ignoring them
and using a sub-optimal execution plan. I personally have never had to
do this.
You don't say how you "notice" fragmentation, but you might consider an
automated approach that runs nightly or weekly. See my site for one
possible method:
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Andrew that makes sense. What would be the best way to check to
make sure that the stored proc's are indeed getting recompiled?
Thanks
shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks|||Well it is not something one generally has to check on. If you know the
stats are getting updated (you can see with DBCC SHOWSTTISTICS) then you can
count on any plans that reference those stats to get recompiled the next
time they are run. This is true in all cases unless you have used the KEEP
PLAN or KEEP FIXED PLAN hints in the query.
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154694889.877119.181210@.p79g2000cwp.googlegroups.com...
> Thanks Andrew that makes sense. What would be the best way to check to
> make sure that the stored proc's are indeed getting recompiled?
> Thanks
>
> shub wrote:
>