Capture And Display Execution Time Of SQL Query In SQL Server

Posted onAuthorHeelpBook2 Comments

Send Us a Sign! (Contact Us!)
Word PDF Epub Text
XML OpenOffice XPS MHT

This tutorial proposes 3 ways in order for you to get the Execution time of SQL Query or Stored Procedures are called or submitted to your SQL Server.

[tweet]

They will give you durations in microsecondsand base on the execution time, you may have a deeper understand and will do some optimization for your database structure/indexing to make it runs better.

1. Using SQL Server Profiler

I think it’s a easiest way for you to trace/track which Stored Procedures or SQL commands are running on SQL Server and how long it takes for each of  SQL Query/ Stored Procedure execution.

As you see, all commands are in TextData column and all Execution time for each are in Durationcolumn respectively.

2. Using SQL Script with @StartTime and @EndTime parameters

The [gs script] should be run on SQL Server Management Studio Query.

USE AdventureWorksDW;
GO

DECLARE @StartTime datetime,@EndTime datetime

SELECT @StartTime=GETDATE()

SELECT * FROM DimCustomer where Gender = 'M'


SELECT @EndTime=GETDATE()

SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in microseconds]

Just replace your own SQL statements with line 2, after execute the statement, it will show the Duration in microseconds in another result panel.

3. Using SQL Script with SET STATISTICS TIME (Transact-SQL)

It displays the number of milliseconds required to parse, compile, and execute each statement.

Run this SQL script on your SQL Query:

USE AdventureWorksDW;

GO

SET STATISTICS TIME ON

GO

SELECT * FROM DimCustomer where Gender = 'M'
Go

SET STATISTICS TIME OFF;

GO

And below is the result set:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.


(9351 row(s) affected)


SQL Server Execution Times:

CPU time = 63 ms, elapsed time = 479 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

That’s all! Feel free to contribute your own solution by submitting your comments as you are always be welcome.

SOURCE

LINK

LANGUAGE
ENGLISH

2 thoughts on “Capture And Display Execution Time Of SQL Query In SQL Server”

Comments are closed.