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
>

Progress Indicator for Table Reindex

I am reindex a 100 Gig Table with 30 million rows. It is currently running 14
hours.
Is there any way for me to find out approx when this will end (ie percent
complete)
No. There is no progress reporting for index ops in SQL Server 2000 or 2005.
Do you know how long the index took to reindex last time? That would be the
basis for a rough first-order approximation of the run-time this time. Other
factors that make determining this very difficult are concurrent cpu and I/O
load and distribution of free space in the database (basically you need to
have another 120 Gb of free space to be able to rebuild a 100 Gb clustered
index - if you don't the files will grow during the operation).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Gozick" <MarkGozick@.discussions.microsoft.com> wrote in message
news:666E1A4F-D8AF-440C-8D56-84A5DF1DE0EC@.microsoft.com...
> I am reindex a 100 Gig Table with 30 million rows. It is currently running
14
> hours.
> Is there any way for me to find out approx when this will end (ie percent
> complete)
>
|||100GB "TABLE" ?
how big is the DB ?
Greg Jackson
PDX, Oregon
|||the DB is 400 Gig. The 100 Gig table is the General Ledger Detail table which
is quite large. We can only keep 2 years on-line.
"pdxJaxon" wrote:

> 100GB "TABLE" ?
> how big is the DB ?
>
> Greg Jackson
> PDX, Oregon
>
>
|||Mark Gozick wrote:
> the DB is 400 Gig. The 100 Gig table is the General Ledger Detail
> table which is quite large. We can only keep 2 years on-line.
Did you just say "only"? ...
robert
|||That's not that big any more...
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:u4COG9HYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Mark Gozick wrote:
> Did you just say "only"? ...
> robert
>

Progress Indicator for Table Reindex

I am reindex a 100 Gig Table with 30 million rows. It is currently running 14
hours.
Is there any way for me to find out approx when this will end (ie percent
complete)No. There is no progress reporting for index ops in SQL Server 2000 or 2005.
Do you know how long the index took to reindex last time? That would be the
basis for a rough first-order approximation of the run-time this time. Other
factors that make determining this very difficult are concurrent cpu and I/O
load and distribution of free space in the database (basically you need to
have another 120 Gb of free space to be able to rebuild a 100 Gb clustered
index - if you don't the files will grow during the operation).
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Gozick" <MarkGozick@.discussions.microsoft.com> wrote in message
news:666E1A4F-D8AF-440C-8D56-84A5DF1DE0EC@.microsoft.com...
> I am reindex a 100 Gig Table with 30 million rows. It is currently running
14
> hours.
> Is there any way for me to find out approx when this will end (ie percent
> complete)
>|||100GB "TABLE" ?
how big is the DB ?
Greg Jackson
PDX, Oregon|||the DB is 400 Gig. The 100 Gig table is the General Ledger Detail table which
is quite large. We can only keep 2 years on-line.
"pdxJaxon" wrote:
> 100GB "TABLE" ?
> how big is the DB ?
>
> Greg Jackson
> PDX, Oregon
>
>|||Mark Gozick wrote:
> the DB is 400 Gig. The 100 Gig table is the General Ledger Detail
> table which is quite large. We can only keep 2 years on-line.
Did you just say "only"? ...
robert|||That's not that big any more...
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:u4COG9HYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Mark Gozick wrote:
> > the DB is 400 Gig. The 100 Gig table is the General Ledger Detail
> > table which is quite large. We can only keep 2 years on-line.
> Did you just say "only"? ...
> robert
>

Progress Indicator for Table Reindex

I am reindex a 100 Gig Table with 30 million rows. It is currently running 1
4
hours.
Is there any way for me to find out approx when this will end (ie percent
complete)No. There is no progress reporting for index ops in SQL Server 2000 or 2005.
Do you know how long the index took to reindex last time? That would be the
basis for a rough first-order approximation of the run-time this time. Other
factors that make determining this very difficult are concurrent cpu and I/O
load and distribution of free space in the database (basically you need to
have another 120 Gb of free space to be able to rebuild a 100 Gb clustered
index - if you don't the files will grow during the operation).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Gozick" <MarkGozick@.discussions.microsoft.com> wrote in message
news:666E1A4F-D8AF-440C-8D56-84A5DF1DE0EC@.microsoft.com...
> I am reindex a 100 Gig Table with 30 million rows. It is currently running
14
> hours.
> Is there any way for me to find out approx when this will end (ie percent
> complete)
>|||100GB "TABLE" ?
how big is the DB ?
Greg Jackson
PDX, Oregon|||the DB is 400 Gig. The 100 Gig table is the General Ledger Detail table whic
h
is quite large. We can only keep 2 years on-line.
"pdxJaxon" wrote:

> 100GB "TABLE" ?
> how big is the DB ?
>
> Greg Jackson
> PDX, Oregon
>
>|||Mark Gozick wrote:
> the DB is 400 Gig. The 100 Gig table is the General Ledger Detail
> table which is quite large. We can only keep 2 years on-line.
Did you just say "only"? ...
robert|||That's not that big any more...
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:u4COG9HYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Mark Gozick wrote:
> Did you just say "only"? ...
> robert
>

Progress Indicator

How do I know the percentage done or left when command like DBCC REINDEX or
ALTER TABLE on a huge table?
Thanks in advance.In general, you don't.
However, in SQL Server 2005, you can query the sys.dm_exec_requests view. It
gives you progress
report for *some* operations (documented in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Chong" <jonathan@.smartie.com> wrote in message
news:%238q1PLZeGHA.5016@.TK2MSFTNGP04.phx.gbl...
> How do I know the percentage done or left when command like DBCC REINDEX o
r
> ALTER TABLE on a huge table?
> Thanks in advance.
>