Transaction transaction takes too long?

Question

I have a stored procedure that has the following code:

BEGIN TRY
--BEGIN TRANSACTION @TranName

    DECLARE @ID int

    INSERT INTO [dbo].[a] ([Comment],[Type_Id],[CreatedBy])
    VALUES ('test',1,2)

    SET @ID = SCOPE_IDENTITY()

    INSERT INTO [dbo].[b] ([Can_ID],[Com_ID],[Cal_ID],[CreatedBy])
    VALUES (1,@ID,null,2)

    UPDATE c SET LastUpdated = GETDATE(), LastUpdatedBy = 2 WHERE b.id = @ID

    --COMMIT TRANSACTION @TranName

    SELECT * from [View] where a.id=@ID
    END TRY
    BEGIN CATCH
--ROLLBACK TRANSACTION @TranName
END CATCH

Each of the statements executed separately (as it is now) is fast. But when we remove comments from the transaction fragment, the script execution time increases from 1 s to more than 2 minutes .

The system has been working for quite some time, and before that it wasn’t a problem, I tried to find documentation on how SQL Server processes transactions, if SQL performance could affect anything and the only thing I have in mind is the transaction log ... but ideally, these separate statements are executed in a separate transaction, any idea?

Answer 1

. - , SQL Server , .