Measure compilation time of stored procedure

Question

I need to measure how much time it takes for the MSSQL-2012 to compile a stored procedure.

Is it possible?

score 6 · Accepted Answer · answered Feb 29 '16 at 13:09

6

Yes, you can get this information. It is buried inside properties of the select statement in an actual execution plan. To do that:

Set Actual Execution plan to on
Execute your SP with recompile option
Go to Actual Execution plan, choose 'select' part
Go to properties (Press F4) and there you can see CompileTime

However, I'm not sure how accurate or what measures this information displays.

I am assuming, you want to discover how much it does affect your performance. You can search this article for 'compile time' https://technet.microsoft.com/en-us/library/cc966540.aspx

answered Feb 29 '16 at 13:09

Vladimir S.

465
5
12

Is it in milliseconds? – Yacov Feb 29 '16 at 13:14
It seems like it, according to this article: https://msdn.microsoft.com/en-us/library/ms190287.aspx Also you can use it to get an alternative method of knowing compile and parse time for a store procedure.
I would use method described in this article and compare it with what you see in the properties. If they are equal, then it's milliseconds.
– Vladimir S. Feb 29 '16 at 13:18

score 2 · Answer 2 · edited Apr 13 '17 at 12:42

Adding to @Vladimir's answer:

If you wanted to handle this in a more programmatic way, you should review @PaulWhite's answer to essentially the same question, also here on DBA.SE: How to measure or find cost of creating a query plan?. That answer shows how to query the DMV's for this data.

And, if you wanted to do this on a larger scale to possibly try variations of queries to later compare, check out the answers to this question which cover both storing the plans in a DB to compare via T-SQL as well as exporting them to disk: Export Query Plans

Measure compilation time of stored procedure

2 Answers2