In SQL Server, the SET STATISTICS TIME ON statement is used to display the amount of time that SQL Server requires to parse, compile, and execute a query. This statement helps you gauge the performance of your queries.
Here's how you can use it:
1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
2. Enable Statistics Time by executing the command:
SET STATISTICS TIME ON;
3. Run your query. For example:
SELECT *
FROM Employees
WHERE Department = 'Sales';
4. Disable Statistics Time (Optional) by executing the command:
SET STATISTICS TIME OFF;
5. Review the results. After you run the query, you will see messages in the Messages tab of the SSMS output area that report the elapsed time in milliseconds for each execution. It typically looks like this:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 25 ms.
Example:
Here's a complete example combining all the steps:
SET STATISTICS TIME ON;
SELECT *
FROM Employees
WHERE Department = 'Sales';
SET STATISTICS TIME OFF;
Notes:
- The output shows the timing related to compiling and executing your SQL command.
- Make sure to turn off SET STATISTICS TIME if you do not want to see time statistics for every query you run afterward.
- This will not show you execution plans or detailed performance metrics but is useful for quick checks on query execution time.