471,591 Members | 1,879 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,591 software developers and data experts.

question on reindex

Hi,
i have several tables in production whose contents are renewd totally in 1
week. So everyd day we delete ~15% records and then insert 15% new.
And after a few days, the performances drops :

TABLE level scan performed.
- Pages Scanned................................: 169617
- Extents Scanned..............................: 21630
- Extent Switches..............................: 153827
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 13.78% [21203:153828]
- Logical Scan Fragmentation ..................: 45.06%
- Extent Scan Fragmentation ...................: 52.66%
- Avg. Bytes Free per Page.....................: 5042.5
- Avg. Page Density (full).....................: 37.70%

I can't program a dbcc reindex every day because of concurrent access (it
locks the tables too long), actually i can only program it on sunday.

What else can i do ? I can adjust the fill factor but how to find the good
value if i don't want to waste space.

The total size of the database is ~150GB.

Thx
Sep 27 '06 #1
1 2716
Have you tried defragmenting by using DBCC INDEXDEFRAG?

Unlike DBCC DBREINDEX (or the index building operation in general),
DBCC INDEXDEFRAG is an online operation. It does not hold locks long
term and thus will not block running queries or updates

Denis the SQL Menace
http://sqlservercode.blogspot.com/

paul wrote:
Hi,
i have several tables in production whose contents are renewd totally in 1
week. So everyd day we delete ~15% records and then insert 15% new.
And after a few days, the performances drops :

TABLE level scan performed.
- Pages Scanned................................: 169617
- Extents Scanned..............................: 21630
- Extent Switches..............................: 153827
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 13.78% [21203:153828]
- Logical Scan Fragmentation ..................: 45.06%
- Extent Scan Fragmentation ...................: 52.66%
- Avg. Bytes Free per Page.....................: 5042.5
- Avg. Page Density (full).....................: 37.70%

I can't program a dbcc reindex every day because of concurrent access (it
locks the tables too long), actually i can only program it on sunday.

What else can i do ? I can adjust the fill factor but how to find the good
value if i don't want to waste space.

The total size of the database is ~150GB.

Thx
Sep 27 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Craig Stadler | last post: by
1 post views Thread by Sally Sally | last post: by
5 posts views Thread by Edmund Dengler | last post: by
1 post views Thread by Ulrich Wisser | last post: by
2 posts views Thread by esource | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.