Here's my question: should wrapping this code in a transaction have
ANY effect on its performance? Because I'm seeing some major effects,
and did not expect it.
OK, here we go:
Have what looks like a fairly innocent SP that inserts a bunch of
records into a table.
In this case, the table is starting out empty, though in production
that will generally not be the case.
The format is generally:
insert into mytable
select from yourtable
and yourpk not in (select mypk from mytable)
insert into mytable
select from hertable
and herpk not in (select mypk from mytable)
Run from a DTS package after four hours it had not completed adding
about 1m rows on our memory-starved dev machine, and I killed it. The
same code has run in reasonable time, maybe five minutes, on our
slightly larger staging machine, also from inside a DTS package.
Anyway, I hacked up a version of the SP, removed the transaction
begin/commit, and ran it from QA. Completed in under four minutes!
I reset the table and then tried running the original code (with
transaction). After six minutes I killed it, it had not completed.
Ran notrans code again. Completed in three minutes!
No contention on the machine that I can see.
So, it rather LOOKS like something in the management of the
transaction is a problem here. Here, but not there.
So, anybody, wassup with this?
BTW, when it runs slowly and forever, it spawns off six threads for
the SPID, and consumes lots of CPU, doing very little (but not zero)
IO. Certainly no blocking is seen.
Yes, there is physical IO, usually a few thousand, and a few thousand
I guess the overall performance problem is an artifact of the tiny
memory (now 768mb) on the dev server, causing the optimizer to choose
different plans than on the staging server, and then running them more
I doubt it would take 60 seconds to run this on a 3ghz, 4gb server.
Just looking for a reality check and any suggestions.
The obvious one is already in action - there will probably be some new
dev and staging servers soon, where soon is the typical month or so.
Meanwhile, we get to study SQLServer behavior under this kind of
After freeproccache, the original code ran in 1:57!
Better than a reboot ...