SQL Server Management Studio, how to get execution time down to milliseconds

Question

When I submit a batch (e.g., perform a query) in SSMS, I see the time it took to execute in the status bar. Is it possible to configure SSMS to show the query time with millisecond resolution?

Here is the bar I am talking about with the section of interest circled in red:

enter image description here

Not really answering your question, but you can use SQL Server Profiler (a logging tool) to check the duration of your query. Duration is measured in milliseconds. — AGuyCalledGerald, Feb 29 '12 at 13:38

score 428 · Answer 1 · answered Nov 23 '11 at 18:58

428

What you want to do is this:

set statistics time on

-- your query

set statistics time off

That will have the output looking something like this in your Messages window:

SQL Server Execution Times: CPU time = 6 ms, elapsed time = 6 ms.

answered Nov 23 '11 at 18:58

2

But this puts the timing in the Messages window, which means I have to manually flip to it after performing the query. Also, the results seem to not make sense, for example: CPU time = 1357 ms, elapsed time = 169 ms. How does that add up, even if I do have 8 cores with hyperthreading (i.e., 16 virtual)? – Michael Goldshteyn Nov 23 '11 at 19:00
@MichaelGoldshteyn Unfortunately, I don't think there is a way to modify the elapsed time in the status bar for SSMS. I think your only option to capture it to `ms` would be my post. – Nov 23 '11 at 19:03
2

@MichaelGoldshteyn as for why your CPU time is greater, it is because you have a multi-core or hyperthreaded CPU. – Nov 23 '11 at 19:04
But the CPU time is way to large, given the elapsed time. Take another look at my example. – Michael Goldshteyn Nov 23 '11 at 19:12
28

@MichaelGoldshteyn 1357 / 8 = 169.625. Coincidence? – Dan J Nov 23 '11 at 19:20
1

@DanJ Yes, coincidence. (I just got: "CPU time = 16 ms, elapsed time = 882 ms.", and I don't have >= 55 virtual cores). CPU Time = time spent by the CPU. Things like sending data over the network often don't occupy CPU time. (CPU puts it in a buffer in memory that the Ethernet card can access directly.) – benizi Jul 01 '12 at 21:05
3

@DanJ, the DB doesn't do everything from memory. Often I/O is involved and I/O means more elapsed time. – Michael Goldshteyn Jul 31 '12 at 13:41
1

@benizi It wouldn’t make sense to divide elapsed time by CPU time like you did. If CPU time is greater than elapsed time, then it makes sense to divide CPU time by elapsed time. This will roughly show speedup/level of concurrency from multiple cores (assuming that network/comms overhead is negligible—like if you’re connecting locally over named pipes/shmem). 1357/169=8.03 which makes sense with 8 cores being fully utilized. Being greater than 8 could be explained by rounding error in either 1357 or 169 because 1357/170=7.98. – binki Oct 03 '16 at 16:19
11

@binki You're correct that my 1,555-day-old comment was inaccurate. – benizi Oct 04 '16 at 03:26
Why I got nothing? SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. – RMati Mar 22 '19 at 03:44

score 158 · Answer 2 · answered Apr 17 '15 at 14:41

158

Turn on Client Statistics by doing one of the following:

Menu: Query > Include client Statistics
Toolbar: Click the button (next to Include Actual Execution Time)
Keyboard: Shift-Alt-S

Then you get a new tab which records the timings, IO data and rowcounts etc for (up to) the last 10 exections (plus averages!):

enter image description here

answered Apr 17 '15 at 14:41

NickG

9,315
16
75
115

It is the same as the answer of @Ymagine First from Nov'2012. See the answer above... – Bogdan Bogdanov Aug 27 '15 at 07:57
That site (ironically) had an SQL error at the time, so I extracted the key info from a Google cache and posted as a new answer. I wasn't meaning to steal credit and perhaps I should have edited the original answer instead. – NickG Sep 08 '15 at 12:53
Actually, it seems I don't have enough rep points to edit questions, so that's probably why I didn't do that. – NickG Sep 10 '15 at 12:15
8

FYI the units for the Time Statistics are in milliseconds: https://www.brentozar.com/archive/2012/12/sql-server-management-studio-include-client-statistics-button/ – congusbongus Nov 07 '16 at 23:40

score 92 · Accepted Answer · edited Jan 16 '13 at 20:30

92

I was struggling with that until i found this...

http://blog.sqlauthority.com/2009/10/01/sql-server-sql-server-management-studio-and-client-statistics/

Also, if you open the Properties window you may find some magical "Connection elapsed time" that may give you some execution time... Hope it helps...

edited Jan 16 '13 at 20:30

weir

4,521
2
29
42

answered Nov 20 '12 at 12:58

Ymagine First

1,154
9
6

22

The part regarding the Properties window is really the answer to this thread. "Elapsed Time" is sitting there staring you in the face. – Eric Jan 20 '15 at 23:32
And I am now setting this answer as the accepted answer, since it is most correct. – Michael Goldshteyn May 09 '19 at 14:21

score 23 · Answer 4 · edited May 23 '17 at 11:47

23

To get the execution time as a variable in your proc:

DECLARE @EndTime datetime
DECLARE @StartTime datetime 
SELECT @StartTime=GETDATE() 

-- Write Your Query


SELECT @EndTime=GETDATE()

--This will return execution time of your query
SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs]

AND see this

Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"

edited May 23 '17 at 11:47

Community

1
1

answered Sep 26 '13 at 04:32

Mohammad Atiour Islam

5,380
3
43
48

1

Datepart NS represents nanosecond, for milliseconds use MS – Milan Matějka Feb 07 '14 at 10:24

score 13 · Answer 5 · edited May 22 '22 at 16:07

I was after the same thing and stumbled across the following link which was brilliant:

http://www.sqlserver.info/management-studio/show-query-execution-time/

It shows three different ways of measuring the performance. All good for their own strengths. The one I opted for was as follows:

DECLARE @Time1 DATETIME

DECLARE @Time2 DATETIME
 
SET     @Time1 = GETDATE()
 
-- Insert query here

SET     @Time2 = GETDATE()

SELECT  DATEDIFF(MILLISECOND,@Time1,@Time2) AS Elapsed_MS

This will show the results from your query followed by the amount of time it took to complete.

Hope this helps.

I found that inconstant, sometimes shows time and sometimes just shows 0. — RMati, Mar 22 '19 at 03:58

score 6 · Answer 6 · answered Jun 04 '14 at 07:52

I don't know about expanding the information bar.

But you can get the timings set as a default for all queries showing in the "Messages" tab.

When in a Query window, go to the Query Menu item, select "query options" then select "advanced" in the "Execution" group and check the "set statistics time" / "set statistics IO" check boxes. These values will then show up in the messages area for each query without having to remember to put in the set stats on and off.

You could also use Shift + Alt + S to enable client statistics at any time

score 4 · Answer 7 · edited Sep 17 '20 at 19:28

4

Include Client Statistics by pressing Ctrl+Alt+S. Then you will have all execution information in the statistics tab below.

edited Sep 17 '20 at 19:28

fcdt

2,371
5
14
26

answered Sep 17 '20 at 15:07

ashiqs

91
4

2

For me, it's `Shift` + `Alt` + `S` – Nate Radebaugh Nov 17 '22 at 13:38

score 2 · Answer 8 · edited Aug 27 '15 at 08:25

2

You can try this code:

USE AdventureWorks2012;
GO
SET STATISTICS TIME ON;
GO
SELECT ProductID, StartDate, EndDate, StandardCost 
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET STATISTICS TIME OFF;
GO

edited Aug 27 '15 at 08:25

veljasije

6,722
12
48
79

answered Apr 15 '14 at 07:36

Rohitkumar

151
1
6

SQL Server Management Studio, how to get execution time down to milliseconds

8 Answers8

Linked

Related