SQL Server Performance Forum – Threads Archive

Measure Query Execution Time

Hello all! Is there any way to measure the execution time for a query in Sql-Server 2005?
I’ve found an article at this site which is written for Sql-Server 2000 and the commands to get the amount of time should be achieved with the following statements: SET STATISTICS IO ON
SET STATISTICS TIME ON But when i want to execute this in Sql-Server Management Studio, it tells me that the SET statement is not supported: The Set SQL construct or statement is not supported. Can anyone help please? Thanks in advance,
pete

I would have thought that would work from a query editor window in 2005…if you add it to the code for a view, it wont work… Click on the Query menu, Query Options and you can set these kind of options. Ben ‘I reject your reality and substitute my own’ – Adam Savage

Your "best" tool to measure the duration is the clock. Take the time before you start the query and right after it. Some something like this
USE Northwind
DECLARE @dtStart DATETIME
SET @dtStart = GETDATE()
SELECT * FROM Orders
SELECT GETDATE()[email protected]dtstart —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Thank you Guys, @benwilson The "normal query window" is very hard to find, i was alwys wondering why there is no output for the estimated time and why nearly all of the icons showed in the qery designer toolbar were greyed out. Now i found out what the main problem was: I’ve always worked with the "sql-pane" and there you are very restricted, there’s no message output neither the toolbar is activated. After trying around a while, i was able to find the "query window" on a right click at the database in the object explorer or if you right click at the table -> script table as -> select, delete, update….to -> new query editor window. In Sql Server 2000 this was easier – but now it works fine <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> @FrankKalis It’s a good idea to measure the time with the method you mentioned, but i’m always getting "1900-01-01 00:00:00.000" as a result. I’ve read that the smallest displayed time unit in datetime is about 3,33 ms (a 300 of 1000), with the build in measure option i will get a little bit more exact results.

In the version of 2005 i am using, in the top left under the File menu is a button ‘New Query’…just click on that! (if its not there already, you should be able to add it to the toolbar) ‘I reject your reality and substitute my own’ – Adam Savage

]]>