SQL Server Performance

Measure Query Execution Time

Discussion in 'SQL Server 2005 General DBA Questions' started by petewulf, Sep 13, 2005.

  1. petewulf New Member

    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
  2. benwilson New Member

    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
  3. FrankKalis Moderator

    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()-@dtstart



    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  4. petewulf New Member

    Thank you Guys,<br /><br />@benwilson<br />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 -&gt; script table as -&gt; select, delete, update....to -&gt; new query editor window.<br />In Sql Server 2000 this was easier - but now it works fine <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />@FrankKalis<br />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.<br />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.
  5. benwilson New Member

    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

Share This Page