Pros and cons of reindexing

Hi we have a debate here,
Should we reindex periodically our database? If yes at what frequency? This
will help for performance?
Second question after the reindexing the transactions log have 5 Gig. After
a backup should we shrink the log? This will help performance?
Thank for your help
Dany"Dany Marois" <dany.marois@.videotron.ca> wrote in message
news:uBGRsL9BEHA.3404@.TK2MSFTNGP10.phx.gbl...
> Hi we have a debate here,
> Should we reindex periodically our database?
yes it will because reindexing will defragment the database. As for
frequency, depends on the database. my PeopleSoft db takes 19 hours to
reindex so I reserve weekends for this chore but I do it every week
If yes at what frequency? This
> will help for performance?
>

> Second question after the reindexing the transactions log have 5 Gig.
After
> a backup should we shrink the log? This will help performance?
>
not much but it will save space ...:-)
> Thank for your help
> Dany
>
Neil MacMurchy
"you'd do what, to who for how many biscuits?"|||> Should we reindex periodically our database?
If you need it, i.e., it helps performance, then yes.

> If yes at what frequency?
It depends on the level of fragmentation occurs. I.e., volume of data, the
pattern of your modifications and index layouts and stuff like that.

> This
> will help for performance?
Possibly, but again it depends. You can use DBCC SHOWCONTIG to check the
fragmentation level. And you can time your queries between going against
fragmented data and non-fragmented data to determine how much defrag will
improve performance. Mileage does vary.

> Second question after the reindexing the transactions log have 5 Gig.
After
> a backup should we shrink the log? This will help performance?
Why would you want to shrink it? It will only grow to that size as the users
do modifications and the file need to grow. I.e., shrinking the file will
hurt performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Dany Marois" <dany.marois@.videotron.ca> wrote in message
news:uBGRsL9BEHA.3404@.TK2MSFTNGP10.phx.gbl...
> Hi we have a debate here,
> Should we reindex periodically our database? If yes at what frequency?
This
> will help for performance?
> Second question after the reindexing the transactions log have 5 Gig.
After
> a backup should we shrink the log? This will help performance?
> Thank for your help
> Dany
>|||Read this whitepaper which will answer all of your questions:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dany Marois" <dany.marois@.videotron.ca> wrote in message
news:uBGRsL9BEHA.3404@.TK2MSFTNGP10.phx.gbl...
> Hi we have a debate here,
> Should we reindex periodically our database? If yes at what frequency?
This
> will help for performance?
> Second question after the reindexing the transactions log have 5 Gig.
After
> a backup should we shrink the log? This will help performance?
> Thank for your help
> Dany
>