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.

ThxHave 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:

Quote:

Originally Posted by

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

Question on 'load/replace" in SQL Server

I have to periodically overwrite selected tables in one environment
(Prod) with the contents of the same named tables from another
environment (Staging).
There are about 200 tables involved.
In SQL Server what would be the best way to do this and minimize
logging?
Thanks in advance.
Gerry"DataPro" <datapro01@.yahoo.com> wrote in message
news:1166795421.720680.18940@.42g2000cwt.googlegroups.com...
>I have to periodically overwrite selected tables in one environment
> (Prod) with the contents of the same named tables from another
> environment (Staging).
> There are about 200 tables involved.
> In SQL Server what would be the best way to do this and minimize
> logging?
"Best" is relative.
I'd suggest creating a DTS package and on the receiving server either put it
into simple mode (but break up your backup chain) or bulk-logged during the
load.
Having said that, I do a quarterly update with 3 very large tables and still
tweak it, but I use BCP on one and and bulk insert on the other and a batch
file. And also do log-backups between the 3 tables.
> Thanks in advance.
> Gerry
>|||Thanks much
Greg D. Moore (Strider) wrote:
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1166795421.720680.18940@.42g2000cwt.googlegroups.com...
> >I have to periodically overwrite selected tables in one environment
> > (Prod) with the contents of the same named tables from another
> > environment (Staging).
> >
> > There are about 200 tables involved.
> >
> > In SQL Server what would be the best way to do this and minimize
> > logging?
> "Best" is relative.
> I'd suggest creating a DTS package and on the receiving server either put it
> into simple mode (but break up your backup chain) or bulk-logged during the
> load.
> Having said that, I do a quarterly update with 3 very large tables and still
> tweak it, but I use BCP on one and and bulk insert on the other and a batch
> file. And also do log-backups between the 3 tables.
>
> >
> > Thanks in advance.
> > Gerry
> >

Question on load/replace" in SQL Server

I have to periodically overwrite selected tables in one environment (Prod) with the contents of the same named tables from another environment (Staging).

There are about 200 tables involved.

In SQL Server what would be the best way to do this and minimize logging?

Thanks in advance.
GerryI usually do this:

0. bcp out all data from source
1. disable FKs as appropriate in dest
2. truncate/delete from tables in dest
3. bcp in all data to dest
4. re-enable FKs in dest

You could probably also use DTS/SSIS for this, but I prefer bcp because I like the cmd line.

EDIT: one thing I forgot: step 2.1 should be to drop all indexes in dest, then at 4.1 recreate indexes. with indexes dropped the data will import much faster.