Hi Bob,

Thanks for your post... Just to avoid confusion ... The process is running within DTS (also on the same box) All DTS is doing is extracting the data from...

I don't understand why you think the indexes will be fragmented... The updates do not perform any updates to columns that are indexes.

The process carefully creates the indexes in a...

The ETL performs (generally) a full refresh of data, so NO indexes are updated

The indexes are dropped prior the the insert and then after the insert the indexes are...

Hi Gail,

The indexes are dropped because a different set of indexes are created. The reason for this was so that the process didn't fail when the indexes were created if...

Hi,

In simple terms the process does the following:-

1. Extract data from source system into RAW schema.

2. Load data into a transform table(s)

3. Load anomalies into an anomaly table (moving...

Sorry, but that is tosh...... If a CREATE INDEX takes 10 minutes previously and then takes over an hour now, that is NOT bad design that is an issue with...

There definitily isn't any locking, because during the night this is the only process that is running and each stored procedure is run sequentially.

With regards to the performance monitoring I...

Hi,

The amount of data increased a very small amount, the inital part of the ETL does transfer data over the network from Oracle to SQL Server, but this part of...

hi thanks for that GilaMonster..

I have run SQL Profiler and identified the areas that have been running slowly. It seems that indexing dropping/creation is causing an issue. But overall it...

I have already applied SP4 and the patch to development but this server doesn't have the same configuration, for example only 4Gb of memory and not on the SAN but...

What about SP4 itself, any servers with issues? I have a jittery client that are worried about regression should we have problems with SP4....Especially as Microsoft say you can't rollback.

HI Roger,

Have you tried running SQL Profiler when you run the query both times. both when you run the SP in Query Analyzer and in ASP (as Jo suggested).

You...

Cool thanks for that Colin, I had the same thoughts about 15Gb of memory... I have also read that upgrading to SP4 then applying a hotfix ( KB899761&nbsp

Hi,

Slightly off topic, but Microsoft don't recommend defragmenting a SAN disk...

Quote 'Physical Disk Fragmentation

Disk fragmentation can contribute to poor performance on some systems.

To determine whether disk fragmentation exists,...

Hi,

I have implmented XP_SMTP_MAIL ( http://www.sqldev.net/xp/xpsmtp.htm&nbsp and created some wrapper stored procedures to call the module.

To implement this, you need access to...