If you are a SQL Server programmer and use SSMS most of the time, you might be checking the time duration displayed at the status bar in SQL Server Management several times to check the execution time. However, the duration displayed in the status bar is only up to seconds. It is not an accurate execution time. The statement’s execution time in milliseconds is rounded to the nearest second. However, there are several ways to find the accurate execution time.
I have already discussed a couple of common methods (Using SET STATISTICS TIME, Using Client Statistics) to get the execution time to its milliseconds in my earlier article Getting Accurate Execution Time In SQL Server SSMS. So, here I am going to discuss another method.
Using Variables To Capture Time
In this method, you have to use two datetime variables to capture the starting and the ending time, then using the DATEDIFF function get the duration of execution in milliseconds. Here is an example:
USE WideWorldImporters;
GO
/* Declare variables */
DECLARE @startTime datetime,
@endTime datetime;
/* Set start time */
SET @startTime = GETDATE();
/* Your SQL Statement */
SELECT * FROM sales.Invoices;
/* Set end time */
SET @endTime = GETDATE();
/* Find duration in milliseconds */
SELECT DATEDIFF(ms, @startTime, @endTime)
AS Execution_Time_In_Millisecs
This method is one another way to find the execution time of one or a group of statements inside a stored procedure. However, the best way to find the accurate execution time of statements inside a stored procedure is by using the SQL Server Profiler or Extended Events. I have already written an article on how to debug statements Inside a stored procedure using profiler.
Reference
- More about DATEDIFF at Microsoft Docs.