How do I get an accurate timing of a SQL Server stored procedure?

Question

I am timing the difference between using a raw database and a snapshot. I can time UDFs and views using this routine:

DECLARE @Loops INT SET @Loops = 1 
DECLARE @CPU INT SET @CPU = @@CPU_BUSY 
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
DECLARE @SelectedValue int

WHILE @Loops <= 100 
BEGIN

    EXEC @SelectedValue = CashFlow.usp_CSF_Report_ProjectedExpendituresByFundingParticipant '7/1/1999'
    IF @SelectedValue = 50
            PRINT 1 
    SET @Loops = @Loops + 1   
END

PRINT 'usp_CSF_Report_ProjectedExpendituresByFundingParticipant' 
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)  
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE())) 
PRINT ''   

But when I run it with a stored procedure, as shown, I get the result set back.

I'm concerned that the time taken to display the results invalidate the timing results.

Tried wrapping it in a view or UDF, but I don't see any solution in that direction that doesn't add huge random overhead.

How can I capture or ignore the results so they don't display?

Answer 1

You could use a server side trace or extended events.

Server side trace

Extended events