SQL Server Performance Forum – Threads Archive

Weird Transaction Behavior

ok, I have some weird behaviour on our new 64 bit box. When I do this: WHILE @Counter < 5000
BEGIN
INSERT INTO Table VALUES(…)
SET @Counter = @Counter + 1
END It takes forever. On any of our other boxes it’s about 1 second. Now when I change the above statement to read: BEGIN TRANSACTION WHILE @Counter < 5000
BEGIN
INSERT INTO Table VALUES(…)
SET @Counter = @Counter + 1
END COMMIT TRANSACTION It’s as fast as any other machine. So I’m guessing that the transaction ensures that only one commit happens, whereas the first query causes each INSERT to make a commit, slowing things down badly. My only problem is…why doesn’t this happen on our other servers? Is there a 64 bit setting that might cause this? Or is it a database setting that happens to be different? Anyone have any insight into this? Any help would be greatly appreciated!

---

Have you seen any blocking when BEGIN ..END not used?
You might try with TRY….CATCH to see what is happening,http://msdn2.microsoft.com/en-us/library/ms175976.aspx fyi. BEGIN and END define a series of TSQL statements that execute together. If the BEGIN…END block were not included, both ROLLBACK TRANSACTION statements would execute Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

Any reason why you’re using a cursor instead of INSERT INTO target_table (…)
SELECT … FROM source_table ?

---

Well, it’s just a test script to see how quickly 5000 inserts can occur. My concern isn’t the logic of the statement, it’s the fact that it behaves differently between a 32 bit instance and a 64 bit instance. It’s not a production query. I haven’t seen any blocking because I’m the only person on the box. The other interesting thing is that when I run the query in tempdb it’s fast as well, but in any other database it’s slow without the begin transaction…commmit transaction. When I use perfmon to see what’s happening, the log writes max out the disk for about 20 seconds while the disk the data file is on has virtually no activity…so it’s definteily something to do with the way things are being logged.

---

]]>