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.

It matters a lot if you have a warm or cold cache. To clear out your cache execute dbcc dropcleanbuffers before you run your query — gjvdkamp
– gjvdkamp, Commented Sep 20, 2013 at 14:53

Rahul Tripathi · Accepted Answer · 2013-09-20 14:46:52Z

25

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.

answered Sep 20, 2013 at 14:46

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to benchmark a SQL Server query using SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related