If you're going to populate the tables , do you use table hints (tablock) ?

This may avoid all the rowlevel locking overhead you're suffering.

Offcourse, you need to be the only active...

Is this a dedicated SQLServer server ?

If not, check out the amount of ram used by other applications. (With your settings, they shouldn't consume more than 1Gb ! )

Because you've...

a timeout is a client-side setting.

it is something the client decides and handles !

run sql-profiler during your rebuild.

Maybe it shows conflicts or ongoing queries of applications (wich you didn't expect to take that long)

if your server has less then 4 Gb ram, AWE is overhead.

Use the /3gb switch and that should do just fine as long as you stay under 3gb for each...

we've all started as "just a lowly developer" ... it take a hunter to know a hunter

probably your tempdb needs to be bigger...

also start traces 1204 and 3605 for all sessions (and add -T1204 and -T3605 to the startup parameters of your sqlserver)

They provide deadlockinfo in your sqlserver-log-files.

- Setting deadlock_priority to low...

- check the datatypes of the parameters provided by your application.

You'll be able to avoid index-scans, if you use the correct datatype (as defined for the column of you table).

-...

still works the same way, as long as you don't use dmv's.

Even better, if this standard date conversion is only calculation or formatting, you may want to take a quick...

you could use windows authentication in the dts-connection-objects, and only provide the needed rights on your prod-servers to the service accounts that runs sql-agent (so it starts the dtsrun.exe)

if your processing does not need to be executed online-realtime, only execute it online-neartime.

have your update-trigger raise an error (nonblocking, but logged) and define an alert for it , so...

it's not because your queries slow down, that your network slows it down !

For sqlserver it depends wich trace-flags are activated for the trace.

e.g. do you only capture sqltext or...

Been there, done that

Thank you Jeff for this reference.

- I've contacted MS and they confermed in x64-sqlbrowser, it does not pulish, but only supplies connection-routing. That's why I couldn't see the instance but...

I 've forgotten to mention the setup of my script.

So it creates an #-temptb and it fills it up with all the data.

Then each while loop deletes 1 row from...