4

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

Is it possible?

2 Answers 2

6

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

enter image description here

2
  • Is it in milliseconds?
    – Yacov
    Feb 29, 2016 at 13:14
  • It seems like it, according to this article: 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. Feb 29, 2016 at 13:18
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.