8

I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the statistics? Or can I calculate it somehow if I include live statistics or the actual execution plan? Maybe in the query store?

1 Answer 1

8

There are many ways :

  • If you can run the query
    • you can use set statistics time on which will give you:

      SQL Server parse and compile time: CPU time = xx ms, elapsed time = xx ms.

  • If you cant run the query, then mine the plan cache to check for query compile time along with many other significant insights.

There are many other ways like profiler, server side trace or XEvents - but they are expensive and might have negative perf impact on your sql server.

You can use sp_BlitzCache to find out Query Compilation Timeout from plan cache.

1
  • Nice, thank you for all of these. Also, is there a way to calculate how long it took for the data to be returned to the client? In SSMS is it just the total time metric displayed in the bottom right of the application minus the "elapsed time" SQL Server Execution Time statistic?
    – J.D.
    Commented Nov 4, 2019 at 16:11

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.