Response time measure

Question

Response time measure

Orlowski Marian

Newbie

Points: 2
More actions
April 26, 2005 at 9:03 pm

#164348

I am starting my affair with SQLserver so may be the answer is obvious but not ofr me yet..
In SQL Query Analyzer, the information about query response time is provided in sec. Is it possible to set this up to msec, or to get the information in msec other way? Tx

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

No, you can not change that specific timing. However, you can use SET STATISTICS TIME ON to get some detailed timing information. Look it up in Books Online. Another way is to use Show Server Trace in the Query menu (or Ctrl+Shift+T). That will give you a separate tab with some information regarding the query, similar to what you will get using SQL Profiler.

If you just want a rough estimate of how long a query took to execute you can use something like the following as well:

DECLARE @start datetime, @end datetime

SET @start = GETDATE()

// Execute query here

SET @end = GETDATE()

PRINT 'Execution time in milliseconds: ' + CONVERT(VARCHAR(10), DATEDIFF(ms, @start, @end))

Edit: Changed SSTATISTICS IO to STATISTICS TIME which is of course what gives timing information.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

DCPeterson SSCoach Points: 15266 More actions · Answer 2

you can also issue this command SET STATISTICS TIME ON and then do your queries...

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 3

You could also run the SQL Profiler with -

Events: RPC:Completed, SP:Completed, SQL:BatchCompleted

Data Columns: Duration, TextData

and this will give you elapsed execution time in milliseconds of each event as it completes.

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Orlowski Marian Newbie Points: 2 More actions · Answer 4

Thanks a lot guys for your helpful response. My 'life ' will be easier by checking straight away, on a couple of significant examples, the behaviour of of different indexes and/or equivalent queries. Regards, Marian