How to debug a stored procedure using print statements and time analyses for each statement?

Print statements can be used to set the print in between the statements where we need to see the time taken by the query. Always use a print statement before and one after that so that we can track the time taken in between.

Note: these statements are used only for analyses never run the print statement for the production deployment this is just for checking the current performance as these kinds of statement reduce the performance in micro to milliseconds and takes few amount of memory for output and increase the logs

Below is the sample for the procedure:

alter Procedure usp_test_SaveTrans

as

Begin

print 'Procedure Started At: '+cast(cast( getdate() as datetime2) as varchar(30))

TRUNCATE TABLE Employ

DECLARE @v_Second_point NCHAR(50)='Trans_Second_point'

BEGIN TRANSACTION

   INSERT INTO Employ VALUES ( 1, N'EMP1',200000)

   SAVE TRANSACTION First_point

   WAITFOR DELAY '00:00:20';

 print 'Savepoint after the first INSERT after a delay of 00:00:20 created At: '+cast(cast( getdate() as datetime2) as varchar(30))

  INSERT INTO Employ VALUES ( 2, N'EMP2',234000)

 WAITFOR DELAY '00:01:07';

   SAVE TRANSACTION @v_Second_point

   print 'Savepoint after the Second INSERT after a delay of 00:01:07 created At: '+cast(cast( getdate() as datetime2) as varchar(30))

  INSERT INTO Employ VALUES ( 3, N'EMP3',500098)

  WAITFOR DELAY '00:00:40';

   ROLLBACK TRANSACTION @v_Second_point

 print 'ROLLBACK TRANSACTION SecondInsert after a delay of 00:00:40 done At: '+cast(cast( getdate() as datetime2) as varchar(30))

COMMIT

SELECT * FROM Employ

print 'End of Procedure done At: '+cast(cast( getdate() as datetime2) as varchar(30))

end