If you are trying to determine how long the various statements in a stored procedure are taking, then you really need to see output in real time (i.e. not wait till the end). The example below is the method that we currently use. The information is printed to the messages tab in real time and you can switch it off when you’ve finished debugging / optimizing by setting @Debug=’N’.

CREATE PROCEDURE [dbo].[p_RaiseErrorExample]
/*
------------------------------------------------------------------------------------------------------
Author: Datasmith, Richard Grieveson
Date: 17/10/2015
Description: Demonstrates the use of raise error to debug / optimise
Version: 1.0
To Run: EXEC p_RaiseErrorExample
Change History: 17/10/2015 - Procedure Creation
------------------------------------------------------------------------------------------------------
*/
AS
SET NOCOUNT ON

------------------------------------------------------------------------------------------------------
--Declarations
------------------------------------------------------------------------------------------------------
Declare @Debug nchar(1) = 'Y'
Declare @DebugMessage varchar(2000) = ''
Declare @DebugStartTime datetime = GetDate()

------------------------------------------------------------------------------------------------------
--Step 1
------------------------------------------------------------------------------------------------------
SELECT 'Step 1'
WAITFOR DELAY '00:00:02';

If @Debug = 'Y'
BEGIN
SELECT @DebugMessage = 'Step 1 completed after ' + CAST(DATEDIFF(SECOND,@DebugStartTime,GetDate()) AS VARCHAR) + ' seconds'
RAISERROR (@DebugMessage, 0, 1) WITH NOWAIT
END

------------------------------------------------------------------------------------------------------
--Step 2
------------------------------------------------------------------------------------------------------
SELECT 'Step 2'
WAITFOR DELAY '00:00:02';

If @Debug = 'Y'
BEGIN
SELECT @DebugMessage = 'Step 2 completed after ' + CAST(DATEDIFF(SECOND,@DebugStartTime,GetDate()) AS VARCHAR) + ' seconds'
RAISERROR (@DebugMessage, 0, 1) WITH NOWAIT
END

------------------------------------------------------------------------------------------------------
--Step 3
------------------------------------------------------------------------------------------------------
SELECT 'Step 3'
WAITFOR DELAY '00:00:02';

If @Debug = 'Y'
BEGIN
SELECT @DebugMessage = 'Step 3 completed after ' + CAST(DATEDIFF(SECOND,@DebugStartTime,GetDate()) AS VARCHAR) + ' seconds'
RAISERROR (@DebugMessage, 0, 1) WITH NOWAIT
END