Performance setting for a job

Hello,
I have a script that create some tables with (Select ... Into) for caching
and optimizing. This cache is created twice a w automatically. Problem:
The caching process needs 20 mins and while this 20 mins my website is not
working because SQL Server is busy with 100%.
Is there any chance to tell the SQL-Server that he should not use more than
20% of it's CPU for this job? No problem if it needs longer...
Thanks in advance.
CFIf you have indexes on the tables, drop these before you delete and
re-insert the data, then add the indexes after
It'll be quicker than inserting a huge amount of data into an indexed table.
You might want to consider using triggers or difference/update scripts to
maintain the tables over the course of the day if possible,
but without knowing what the cache tables consist of or how they are
populated it's difficult to say.
"Christian Funke" <ChristianFunke@.discussions.microsoft.com> wrote in
message news:1575B198-0132-46AA-8C32-1590110AA17A@.microsoft.com...
> Hello,
> I have a script that create some tables with (Select ... Into) for caching
> and optimizing. This cache is created twice a w automatically. Problem:
> The caching process needs 20 mins and while this 20 mins my website is not
> working because SQL Server is busy with 100%.
> Is there any chance to tell the SQL-Server that he should not use more
than
> 20% of it's CPU for this job? No problem if it needs longer...
> Thanks in advance.
> CF

Performance problem

HI, I have a performance problem with one of my set of packages. They run fine on dev machine but much slower on test server which has twice (8gb) the memory of my dev server (4gb). The first time I run the set of package they execute in less than a minute which is normal. But subsequent executions in same conditions (tables are empty) takes 2-3 minutes. When I restart Sql server 2005, the loading time is back to less than a minute.

I enables the "use awe" for memory beyond 4 gb on test server and while executing, total memory is far beyond the 8gb of test server and on the four available processors, only one seems to be used. Also, deployment on other machines have no problems. We rebuilt the test server (re-installed windows, Sql server 2005, SP1 and patches) with no luck.

I would like to monitor the problem using performance monitor and Sql server profiler, is there some specific counters or trace events that I should focus on? My set of packages (one main package calling several execute package tasks) load approximately 100K lines and they are deployes on file system.

Thank you,
Ccote

Not sure about the cause of your problem. I would sugest to enable package logging and compare execution logs between server and try to identify possible botle necks.

Also there is an interesting document about performance tunning:

http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/SSISPerfTuning.doc