Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home SQL Server Capture And Display Execution Time Of SQL Query In SQL Server

Capture And Display Execution Time Of SQL Query In SQL Server

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.

They will give you durations in microseconds and 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.

SQL Server Profiler Execution Time

SQL Server Profiler Execution Time

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
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]

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.

Get Execution Time With Start Time And End Time

Get Execution Time With Start Time And End Time

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
USE AdventureWorksDW;
GO 
 
SET STATISTICS TIME ON
GO
 
SELECT * FROM DimCustomer WHERE Gender = 'M'GO
 
SET STATISTICS TIME OFF;
GO
[/SQL]
 
AND below IS the RESULT SET:
 
[text]
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.

USE AdventureWorksDW; GO SET STATISTICS TIME ON GO SELECT * FROM DimCustomer where Gender = 'M' Go SET STATISTICS TIME OFF; GO [/sql] And below is the result set: [text] 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.

Get Execution Time With SET STATISTICS TIME

Get Execution Time With SET STATISTICS TIME

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

May 31, 2011Hoan Huynh
Create Custom Update Profile Page For WordPress UsersHow To Change Default Home Page Of WordPress
You Might Also Like:
  • ASP.NET Store Session In MS SQL Server Database
  • PowerShell Piwik – File cannot be loaded because the execution of scripts is disabled on this system
  • PHP measure/ calculate execution time of loading page
  • Count Total Rows For All Tables In MS SQL Server
  • How To Check Which Stored Procedures Or SQL Commands Are Running On SQL Server
  • Encrypt With MD5, SHA Or SHA1 In MS SQL Server
  • SQL Server: Cannot resolve the collation conflict between in the equal to operation
  • How To Filter DataBase Name And Login Username In SQL Server Profiler
  • Facebook Load User Profile Via Graph API And FQL Query
  • Export/Import Data From Local Database To Remote Database With MS SQL Server 2008
Hoan Huynh

Hoan Huynh is the founder and head of 4rapiddev.com. Reach him at [email protected]

11 years ago SQL ServerDATEDIFF, GETDATE, How To, SET STATISTICS TIME OFF, SET STATISTICS TIME ON, SQL Server Profiler7,633
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
24,444 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
21,824 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
17,638 views
JQuery Allow only numeric characters or only alphabet characters in textbox
14,981 views
C# Read Json From URL And Parse/Deserialize Json
11,692 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Things to Learn about Installingderm Loan Type S
  • Online Photo Editor – Free Photoediting Software
  • A Guide to Finding the Best Paper Sellers
  • Photoediting in Home Isn’t Hard to Do!

  • Free Photo Editor Online
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (27)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (647)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development