A simple method to measure execution time in SQL Server

Chris Maunder

Rate me:

5.00/5 (5 votes)

31 Oct 2016CPOL

82.2K

Timing SQL calls with millisecond accuracy

Introduction

Measuring the execution time of a statement or function can be handy when performance tuning. SQL has a ton of features but I often fall back to rough-and-ready methods to at least narrow down the bit that needs optimising. Remember that the first step in optimising is working out what needs to be optimised.

SQL Execution timing

With SQL Server 2008 Microsoft introduced the DateTime2 which provides higher accuracy than the DateTime type. While the accuracy is quoted as 100 nanoseconds, the theoretical accuracy and practical accuracy differ. 1ms is what you may end up with, which for us is fine.

To time SQL calls simply use

SQL

Declare @StartTime DateTime2 = SysUTCDateTime()

-- my SQL calls

Print 'Time taken was ' + cast(DateDiff(millisecond, @StartTime, SysUTCDateTime()) as varchar) + 'ms'

For SQL Server versions below SQL Server 2008 you'll need to fall back to the traditional DateTime:

SQL

Declare @StartTime DateTime = GetDate()

-- my SQL calls

Print 'Time taken was ' + cast(DateDiff(millisecond, @StartTime, GetDate()) as varchar) + 'ms'

Note the use of SysUTCDateTime instead of SysDateTime for the case when you measure time over a daylight Saving time change. OK, rare, but possible! (Thanks Richard). For those working on SQL Server 2005 and under, you're stuck with GetDate.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Chris Maunder

Founder CodeProject

Canada

Chris Maunder is the co-founder of CodeProject and ContentLab.com, and has been a prominent figure in the software development community for nearly 30 years. Hailing from Australia, Chris has a background in Mathematics, Astrophysics, Environmental Engineering and Defence Research. His programming endeavours span everything from FORTRAN on Super Computers, C++/MFC on Windows, through to to high-load .NET web applications and Python AI applications on everything from macOS to a Raspberry Pi. Chris is a full-stack developer who is as comfortable with SQL as he is with CSS.

In the late 1990s, he and his business partner David Cunningham recognized the need for a platform that would facilitate knowledge-sharing among developers, leading to the establishment of CodeProject.com in 1999. Chris's expertise in programming and his passion for fostering a collaborative environment have played a pivotal role in the success of CodeProject.com. Over the years, the website has grown into a vibrant community where programmers worldwide can connect, exchange ideas, and find solutions to coding challenges. Chris is a prolific contributor to the developer community through his articles and tutorials, and his latest passion project, CodeProject.AI.

In addition to his work with CodeProject.com, Chris co-founded ContentLab and DeveloperMedia, two projects focussed on helping companies make their Software Projects a success. Chris's roles included Product Development, Content Creation, Client Satisfaction and Systems Automation.