Rebuilding Indexes and Shrinking Databases

Is there a best practice for the time of day to rebuild indexes and shrink
databases? i.e. not in the middle of the day
What performance hit would it have performing them actions on a 20Gb
Database have on disk I/O, Processor, etc.?
Thanks
Tim
Yes it is a good practice to rebuild indexes but when it depends on your
data load. We do it a twice a week.
Don't shrink database because it causes a fragmentation to be created of
the data pages. How do you increase a size of the database?
"Tim Earnshaw" <TimEarnshaw@.discussions.microsoft.com> wrote in message
news:1EBF5F1D-C20C-4969-A5AF-2A52E784A0F5@.microsoft.com...
> Is there a best practice for the time of day to rebuild indexes and shrink
> databases? i.e. not in the middle of the day
> What performance hit would it have performing them actions on a 20Gb
> Database have on disk I/O, Processor, etc.?
> Thanks
|||Tim,
Depends on what maintenance windows you have - do it then. I usually
rebuild indexes weekly and never, ever, ever shrink databases unless
there's an emergency (run out of disk space).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Tim Earnshaw wrote:
> Is there a best practice for the time of day to rebuild indexes and shrink
> databases? i.e. not in the middle of the day
> What performance hit would it have performing them actions on a 20Gb
> Database have on disk I/O, Processor, etc.?
> Thanks
|||Thanks for the replies
The reason I ask is someone where I work was shrinking and rebuilding
indexes at 2pm and they said that its industry / microsoft standard to
rebuild indexes whenever needed. I didn't believe them and thought that I
would ask on here.
"Mark Allison" wrote:

> Tim,
> Depends on what maintenance windows you have - do it then. I usually
> rebuild indexes weekly and never, ever, ever shrink databases unless
> there's an emergency (run out of disk space).
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Tim Earnshaw wrote:
>
|||Hello Tim,
It is wrong to rebuild Indexes just because there is a command "DBCC Rebuild
... " - Atleast not everyday !!.
We used to load the contracts data into the Reinsurance Accounting DB on a
daily basis. Before the load, we used
to drop the Indexes and recreate them after loading the data. This process
used to take care of Rebuilding the Indexes
However, for the transaction accounting tables (contract tables in the
Reinsurance Accounting becomes Read Only ),
I used to manually drop them recreate them on a weekly basis. Manually
because the "Rebuild Index" process fills
the transaction log pretty fast if the table is huge. The application/DB was
a 24/7 application and one hour (6:30 PM EST)
was allocated for all sort of maintenance.
Hope this helps.
Gopi
"Tim Earnshaw" <TimEarnshaw@.discussions.microsoft.com> wrote in message
news:F97F5310-D81B-4995-A7B6-C7DEA329DBFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the replies
> The reason I ask is someone where I work was shrinking and rebuilding
> indexes at 2pm and they said that its industry / microsoft standard to
> rebuild indexes whenever needed. I didn't believe them and thought that I
> would ask on here.
>
> "Mark Allison" wrote:
|||You may also want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best
Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
-Sue
On Tue, 5 Jul 2005 01:45:02 -0700, Tim Earnshaw
<TimEarnshaw@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the replies
>The reason I ask is someone where I work was shrinking and rebuilding
>indexes at 2pm and they said that its industry / microsoft standard to
>rebuild indexes whenever needed. I didn't believe them and thought that I
>would ask on here.
>
>"Mark Allison" wrote:
|||Tim,
You might also want to check out the sample script under DBCC SHOWCONTIG in
BOL. It allows you to only reindex ones that really need it.
Andrew J. Kelly SQL MVP
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:s7tkc1t81de95nhc20tbl3ftdf8vam2ei2@.4ax.com...
> You may also want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best
> Practices
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> -Sue
> On Tue, 5 Jul 2005 01:45:02 -0700, Tim Earnshaw
> <TimEarnshaw@.discussions.microsoft.com> wrote:
>
|||Although even that example I provided doesn't take into account whether the
index is actually used for range scans and so will benefit from being
rebuilt/defragged.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OAMduScgFHA.516@.TK2MSFTNGP09.phx.gbl...
> Tim,
> You might also want to check out the sample script under DBCC SHOWCONTIG
> in BOL. It allows you to only reindex ones that really need it.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:s7tkc1t81de95nhc20tbl3ftdf8vam2ei2@.4ax.com...
>
sql