Compile time of SQL statement

Query recompile and stored procedure recompile is not a good sign for performance for certain situations. The SQL statement by default cached for reuse, if the same query is requested next time the cached plan will be used instead of compiling again, it will save query execution time.

The compile time can be identified from the plan. There is a property CompileTime in the top node of the SQL plan, it is stored in millisecond (1/1000 to seconds).

The same can be retrieved from XML Plan QueryPlan node, as shown in the below snippet.

<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason
="CouldNotGenerateValidParallelPlan"
MemoryGrant="3560" CachedPlanSize="136" CompileTime="16" 
CompileCPU="16" CompileMemory="1104">

Leave a Reply

Your email address will not be published.