How to benchmark a SQL Server query using SQL Server

Question

I would like to benchmark a SQL query within SQL Server. What is the best approach to accomplish this in the most accurate way?

My idea was as follows -->

• record start-time
• execute query
• record end-time
• perform a date diff between start-time and the end-time and output to milliseconds or microseconds.

Answer 1

You are looking for:-

SET STATISTICS TIME ON

Check this out.

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. Microsoft SQL Server is unable to provide accurate statistics in fiber mode, which is activated when you enable the lightweight pooling configuration option. The cpu column in the sysprocesses table is only updated when a query executes with SET STATISTICS TIME ON. When SET STATISTICS TIME is OFF, 0 is returned. ON and OFF settings also affect the CPU column in the Process Info View for Current Activity in SQL Server Management Studio.