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:

Autorotation

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. – Gladiator

Answer 1

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...

Aronson

The part regarding the Properties window is really the answer to this thread. "Elapsed Time" is sitting there staring you in the face. – Hamitosemitic

And I am now setting this answer as the accepted answer, since it is most correct. – Autorotation

Answer 2

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.

Pyrrhotite

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)? – Autorotation

@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. – Pyrrhotite

@MichaelGoldshteyn as for why your CPU time is greater, it is because you have a multi-core or hyperthreaded CPU. – Pyrrhotite

But the CPU time is way to large, given the elapsed time. Take another look at my example. – Autorotation

@MichaelGoldshteyn 1357 / 8 = 169.625. Coincidence? – Ruminant

@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.) – Aubreir

@DanJ, the DB doesn't do everything from memory. Often I/O is involved and I/O means more elapsed time. – Autorotation

@Aubreir 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. – Catina

@Catina You're correct that my 1,555-day-old comment was inaccurate. – Aubreir

Why I got nothing? SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. – Egg

Answer 3

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!):

Bussy

It is the same as the answer of @Ymagine First from Nov'2012. See the answer above... – Catholicism

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. – Bussy

Actually, it seems I don't have enough rep points to edit questions, so that's probably why I didn't do that. – Bussy

FYI the units for the Time Statistics are in milliseconds: brentozar.com/archive/2012/12/… – Stigmatism

Answer 4

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"

Evaporimeter

Datepart NS represents nanosecond, for milliseconds use MS – Fervency

Answer 5

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.

Yttriferous

I found that inconstant, sometimes shows time and sometimes just shows 0. – Egg

Answer 6

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

Marianomaribel

Answer 7

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

Richerson

For me, it's Shift + Alt + S – Preston

Answer 8

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

Shed