January 13, 2010 at 1:56 pm
Suppose you run a query with SET STATISTICS TIME ON and you get the following output
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(5819 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 7125 ms, elapsed time = 12772 ms.
this seems to indicate 1 ms to parse and compile the plan (which is a select statement the recompile query hint to force a recompile).
However, the XML query plan shows:
<QueryPlan DegreeOfParallelism="4" MemoryGrant="544" CachedPlanSize="72" CompileTime="36" CompileCPU="36" CompileMemory="872">
...
So, which was it? did it take 1 ms or 36 ms to compile the query? Has anyone ever examined these for consistency who could help clear up that confusion?
Thanks,
Jason
January 13, 2010 at 2:09 pm
Is that the only 'parse and compile' line? I just did my own test (with a stored proc and a clear plan cache), and got the following.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 44 ms, elapsed time = 44 ms.
(20 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 269 ms.
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 314 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
The exec plan showed a compile time of 43ms and the stats time showed 3 parse and compile lines, the last having a compile time of 44 ms (and that difference I can put to rounding)
It's not uncommon to have multiple parse and compile lines, especially when you're also displaying the exec plan, as one of those will be for the SET STATISTICS XML ON command that management studio sends to SQL to get the exec plan back
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2010 at 2:22 pm
Hello.
This is interesting. I freed the proccache and then ran the query without the recompile hint. The full output is
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 32 ms, elapsed time = 37 ms.
(5819 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 8390 ms, elapsed time = 11999 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
and the query plan showed 36 ms. So that seems consistent. Then, without clearing the cache, I ran the statement with the recompile hint, which, I thought, would generate a new plan. This time, I get:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(5819 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 9735 ms, elapsed time = 5094 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
the XML plan showed the same 36ms compile time.
It seems that the use of the recompile hint changes the results... very strange.
January 14, 2010 at 3:38 am
Interesting. Going to have to play with this a bit...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply