469,085 Members | 1,046 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,085 developers. It's quick & easy.

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 2644
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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.