question on reindex

[paul]

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

[SQL Menace]

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