Trans. within a SP. Is the SP code executed inside an implicit trans.?

[TheBugSlayer]

Hello fellows.
We have a SP that used to look like this:

CREATE PROC [dbo].[pSomeName]
AS
 UPDATE Table1...
 UPDATE Table2...
 ...
 UPDATE TableN

Now someone says the individual statements were taking too long, no matter what improvements they tried to implement, so they decided to enclose each statement within a transaction like this:

CREATE PROC [dbo].[pSomeName]
AS
BEGIN TRAN;
 UPDATE Table1...
COMMIT TRAN;

BEGIN TRAN;
 UPDATE Table2...
COMMIT TRAN;
 ...
BEGIN TRAN;
 UPDATE TableN
COMMIT TRAN;

Stemming from the above, my questions are:
1- In the original SP, in terms of transactions, are the individual UPDATE statements executed implicitly inside ONE transaction (the stored procedure)? In other words, I am trying to understand how a delay or failure in UPDATE 1 affects UPDATE 2.
2- Does it really represent any improvement to group the individual statements in a transaction? Wouldn't the code still execute in a sequential manner?

Thank you for your answers.


MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Do you have the data file (mdf) on the same physical drive as the log file (ldf)? If so, then I would encourage you to consider moving the log file to another physical drive.

You see, when you update data, there are a lot of things that SQL Server needs to do behind the scenes. This may not be exactly right, but it goes something like this:

1. put something in the transaction log indicating an update is about to occur.
2. update the data file
3. mark the log file for completion.

This is necessary because if the server shuts off in the middle of an update, when it comes back on, you want it to recover properly. The problem occurs when you have the data file and log file on the same disk. The disk must position itself properly to write to the log file, then re-position itself for the data file, and then back to the log file. By putting the data and log files on separate disks, you avoid this file I/O contention and will improve performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Thanks for your answer George. That I know. But to answer your question, yes, log and data files sit on the same HD...but that's just another of the wars that I am waging, lots of resistence! Since that is likely to remain the case for a while until there is a really big disaster, I am trying to make improvements on what we have. Still not sure about the transaction part...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)