5

I have a stored procedure that have 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 in there running individually (as it is now) run fast. But when we remove the comments from the Transaction's piece of code the scripts run time increases from 1s to more than 2 minutes.

The system has been running for quite a while now, and this wasn't a problem before, I've been trying to search documentation about how SQL Server handle Transactions just in case there is anything that may affect SQL performance and the only thing that I have in mind is the Transaction Log... but ideally these individual statements run in a individual transaction as well, any idea?

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Noe
  • 125
  • 1
  • 9
  • 3
    This smells like a locking problem. Having all those actions running in a transaction will cause your session to hold locks on table a, b & c much longer than running it without a transaction. You should look into locks & waits on your server during the runs. – Jens Jun 13 '16 at 10:14
  • 2
    run your query and run this DMV in a seperate window and paste the output in question.. select ec.blocking_session_id,ec.wait_type,ec.last_Wait_type,txt.text,pln.query_plan from sys.dm_exec_requests ec cross apply sys.dm_exec_sql_text (ec.sql_handle) txt cross apply sys.dm_exec_query_plan(ec.plan_handle) pln – TheGameiswar Jun 13 '16 at 10:26
  • Thanks for this guys, I'll check it as soon as I can, as at the moment I don't have permissions enough to run those queries, I'll let you know the output once I can run them :) – Noe Jun 13 '16 at 11:33
  • That is a faked script **UPDATE** and **SELECT** lines should not work. Please provide code to fill tables A,B,C and code for your **[View]**. – Slava Murygin Jun 13 '16 at 14:41
  • The script is a copy of the original one, I just modify the columns and put some dummy data. Slava the original tables and view are too large to just copy and paste them here. It's not the data I select/update/insert what I'm worried about, I'm worried about the fact that when I surround them with a transaction the script's execution period increases a lot. I think there might me a lock somewhere as Jens suggested. – Noe Jun 13 '16 at 15:01
  • like @Jens suggested it seems to be something related with Locking resources, I reset SQL Server service and everything seems to be working again, The next time I'll try to monitor which other processes is locking the tables. Cheers Guys! – Noe Jun 16 '16 at 08:36

1 Answers1

3

As Jens suggested The problems was because of some Tables blocking, after resetting SQL Server Service this locks disappeared and the DB started working properly again.

Noe
  • 125
  • 1
  • 9