In SQL Server, you can use the SET STATISTICS TIME statement to display the time it takes to execute a T-SQL statement.

More specifically, it returns the number of milliseconds required to parse, compile, and execute each statement.

When SET STATISTICS TIME is ON, the time statistics for a statement are displayed. When OFF, the time statistics are not displayed.

The setting of SET STATISTICS TIME is set at execute or run time and not at parse time.

Example

Heres’s a simple example to demonstrate.

SET STATISTICS TIME ON;
GO

SELECT 
    c.CityName, 
    s.StateProvinceName AS State, 
    c.LatestRecordedPopulation AS Population
FROM Application.Cities c
INNER JOIN Application.StateProvinces s
ON c.StateProvinceID = s.StateProvinceID
WHERE c.LatestRecordedPopulation > 2000000
ORDER BY c.LatestRecordedPopulation DESC;
GO

Result:

Commands completed successfully.
+-------------+------------+--------------+
| CityName    | State      | Population   |
|-------------+------------+--------------|
| New York    | New York   | 8175133      |
| Los Angeles | California | 3792621      |
| Chicago     | Illinois   | 2695598      |
| Brooklyn    | New York   | 2565635      |
| Queens      | New York   | 2272771      |
| Houston     | Texas      | 2099451      |
+-------------+------------+--------------+
SQL Server parse and compile time: 
   CPU time = 4 ms, elapsed time = 6 ms.
Commands completed successfully.

Using a GUI?

If you’re running your query in a GUI such as SSMS or Azure Data Studio, you might need to click the Messages tab or similar in order to view the time statistics.

Here’s what happens when I run the previous query in Azure Data Studio.

I get the results, but no STATISTICS TIME information. To view that information, I need to click on Messages. Once I’ve done that, I can see the relevant info.

Subsequent Queries

Once set to ON, any subsequent queries will return the STATISTICS TIME information, until it’s set to OFF.

Here’s another ad hoc query that I ran in my command line interface just after the first one.

SELECT COUNT(*) FROM Application.People;

Result:

+--------------------+
| (No column name)   |
|--------------------|
| 1111               |
+--------------------+
SQL Server parse and compile time: 
   CPU time = 6 ms, elapsed time = 6 ms.

I had already set STATISTICS TIME to ON previously, therefore no need to do it again.

Turn it Off

To turn it off, simply set it to OFF.

SET STATISTICS TIME OFF;

Now, when I run T-SQL statements, I no longer get the time statistics under the query.

SELECT COUNT(*) FROM Application.People;

Result:

+--------------------+
| (No column name)   |
|--------------------|
| 1111               |
+--------------------+
(1 row affected)