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?

Answer 1

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

1. Set Actual Execution plan to on
2. Execute your SP with recompile option
3. Go to Actual Execution plan, choose 'select' part
4. 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

Answer 2

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