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
Declare @StartTime DateTime2 = SysUTCDateTime()
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
:
Declare @StartTime DateTime = GetDate()
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
.
Chris Maunder is the co-founder of
CodeProject, DeveloperMedia and ContentLab, 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. While at CodeProject, Chris' roles included Architecture and coding, Product Development, Content Creation, Community Growth, Client Satisfaction and Systems Automation, and many, many sales meetings. All while keeping his sense of humour.