Post reply

Alter Table and Statistics Time

  • May 18, 2010 at 1:41 pm

    #386957

    I am adding Foreign Keys after data transfer and wanted to see how long the process took. I started by surrounding the statement with

    SET STATISTICS TIME ON

    and

    SET STATISTICS TIME OFF

    In-between was just one line

    ALTER TABLE theTable ADD CONSTRAINT theTable_anotherTable FOREIGN KEY (anotherTableID) REFERENCES anotherTable(anotherTableID)

    When the process finished, I checked the time and saw that there were two results for SQL Server Execution Times and they weren't identical.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 2 ms.

    SQL Server Execution Times:

    CPU time = 75000 ms, elapsed time = 126418 ms.

    SQL Server Execution Times:

    CPU time = 75000 ms, elapsed time = 126429 ms.

    I looked on-line for an explanaiton of why this would show up twice and what the difference is between the two results, but I didn't find anything. Either of the elapsed times shown roughly equates to the actual elapsed time, they don't each represent 1/2 the time.

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • June 4, 2010 at 12:08 pm

    #1176677

    I found this article to be helpful in explaining how to interpret the output when IO and/or TIME statistics are set to ON

    http://www.sql-server-performance.com/articles/per/statistics_io_time_p1.aspx

    -

  • June 4, 2010 at 12:12 pm

    #1176681

    Thanks! I've bookmarked it so I can rad it this weekend when I have the time.

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

Viewing 3 posts - 1 through 2 (of 2 total)

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