Insert performance --2nd post

Hi,
In our application we do bulk insert 20000 rows into staging database
(bulk/logged recovery model) and then insert into production database from
this staging database. It seems like SQL Server is taking 20 sec to do the
insert into prod database. While if we delete records from prod database
table and run insert again it takes 600ms. Cpu time is approx 250ms in both
cases, writes in 1st case are 3500 and 250 in second and reads are
negligible. What can be the possible reason for this behavior that insert
into a tables as select * from 2nd table always runs slow first time as
compare to if we run insert again it works fine(we ran dbcc dropcleanbuffers
and checkpoint to make sure memory is clean for 2nd run)?
Also we tried following steps:
We remove the clustered index and still the same issue with heap also...
also following is true:
Auto update stats is off ( we do it manually every night)
No trigger
No foreign key to/from this table
No blocking at time of insertion
Autogrow option is off for data and log files
Strange that the same rows insert slowly first time and then if delete from
table and insert again runs very fast...
Thanks
--Harvinder
Hi
Look at performance counters for Page Splits and Extent Allocations.
Also, if you have hardware disk cache, the data is in disk cache on the 2nd
run.
Regards
Mike
"harvinder" wrote:

> Hi,
> In our application we do bulk insert 20000 rows into staging database
> (bulk/logged recovery model) and then insert into production database from
> this staging database. It seems like SQL Server is taking 20 sec to do the
> insert into prod database. While if we delete records from prod database
> table and run insert again it takes 600ms. Cpu time is approx 250ms in both
> cases, writes in 1st case are 3500 and 250 in second and reads are
> negligible. What can be the possible reason for this behavior that insert
> into a tables as select * from 2nd table always runs slow first time as
> compare to if we run insert again it works fine(we ran dbcc dropcleanbuffers
> and checkpoint to make sure memory is clean for 2nd run)?
> Also we tried following steps:
> We remove the clustered index and still the same issue with heap also...
> also following is true:
> Auto update stats is off ( we do it manually every night)
> No trigger
> No foreign key to/from this table
> No blocking at time of insertion
> Autogrow option is off for data and log files
> Strange that the same rows insert slowly first time and then if delete from
> table and insert again runs very fast...
>
> Thanks
> --Harvinder
>