Overall data volumes are growing at a faster rate than our growth in processing capabilities.

Wrong.

Volumes are growing at the same pace as processing capabilities.

When processing capabilities are not sufficient they...

Still there is a shorter version:

select * 
from leftTable  T1
join rightTable T2 
   on (
      T1.C1 = T2.C1 AND...

Yes, when I was experimenting with my first log views I was prepared to trade to trade some insert performance for saving space and increasing reporting speed.

suprisingly, inserts have become...

You have range selection conditions against 7 different columns in the WHERE clause.

which of this columns is used as a 1st column for the clustered index on stkdetl?

is it the...

Actually, only 3 out of 7 have TOP 1.

looks like lousy attempt to eliminate "subquery returned more than 1 row" error when occurred.

well, the whole thing looks lousy.

what is “new data”?

I don't get it.

you say that if there are 1000 rows for a particular store. Than you need to create 1000 files, 1 for each transaction.

and then in the very...

If you leave just the core table :

SELECT * FROM s_stkdetl d
WHERE d.date >= @mDateFrom and d.date <= @mDateTo 
and d.Part_code>=@PartFrom AND d.Part_code<=@PartTo
and d.wh_code>=@FilterWhFrom and d.wh_code<=@FilterWhTo
and d.part_loc>=@LocFrom and...

Of course, right now, you have nothing that will benefit any queries except your purge query.

And the purge query can be easily changed to be based on date criteria.

which would...

This is called by a job, which runs every minute

"every minute" jobs create quite a trouble due to filling up the job history in msdb.

I found it more effective to...

A bit shorter version:

T1 join T2 
   on (
      T2.C1 = T1.C1 OR T1.C1 is null
    ...

It's a terrible idea to bcp from inside of a trigger.

Never import/export data, send emails, or anything like this from a code of a trigger.

You may create a staging table, populate...

Actually, a single scan would be just fine:

SELECT ID
FROM Response r
GROUP BY ID
HAVING MAX(result) <> MIN(result);

You don't need to scan the table 3 times.

2 would be perfectly enough:

SELECT ID
FROM Response r
WHERE r.result = 'Y'
AND EXISTS (SELECT * FROM Response r2 WHERE r2.ID...

Subject of Orig Post:

The Data are logs from Many different machines (1000s). The table was actually installed as a heap. The Table is 98% of the DB size....