SQL stored procedure runtime mystery

Question

I am trying to understand why the SQL Server stored procedure is slow, so I added some raw timers, for example:

Create Procedure DoStuff
As Begin

    Declare @Stopwatch datetime 
    Set @Stopwatch=GetDate()

    Print char(13) + 'Task A'
    /* Perform Task A */
    Print DateDiff(ms, @Stopwatch, GetDate()); Set @Stopwatch = GetDate()

    Print char(13) + 'Task B'
    /* Perform Task B */
    Print DateDiff(ms, @Stopwatch, GetDate()); Set @Stopwatch = GetDate()

    Print char(13) + 'Task C'
    -- Perform Task C
    Print DateDiff(ms, @Stopwatch, GetDate()); Set @Stopwatch = GetDate()

End

Exec DoStuff

I get something like this:

Task a
0

Task b
80

Task c
one hundred

Therefore, I would have thought that 180 ms would be required to complete this procedure. However, this procedure requires 3000+ ms; in client statistics i get

Client processing time: 12
Total execution time: 3105
Wait time on server replies: 3093

What explains the extra ~ 2800 ms?

Answer 1

. SQL Profiler SP: SP: StmtCompleted.

SP: , sproc , .
SP: StmtCompleted , sproc . , sproc.

, , .

Answer 2

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

@Stopwatch GETDATE(), OUTPUT. , EXECUTE RETURN , .

Answer 3

" ", . - .

Answer 4

- .

, , , .

, SP.

Answer 5

Transactional, if you execute it in SSMS, is an ignorant assumption. But you should look at the execution plan to find out more about the real reason. This may be a few things.

http://msdn.microsoft.com/en-us/library/ms178071.aspx