Alter Table and Statistics Time

Question

Alter Table and Statistics Time

Sioban Krzywicki

One Orange Chip

Points: 27770
More actions
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

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

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

Jason- SSCrazy Points: 2795 More actions · Answer 1

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

-

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 2

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