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?
Sql-server – reliable way to determine how long it took the optimizer to generate a query plan
execution-planoptimizationsql serversql-server-2016statistics
Related Question
- Sql-server – How SQL server generates a Query plan with Auto Create Statistics set to OFF
- Sql-server – How to determine index needed to remove hash match from exec plan
- Innodb – Why the query optimizer choose this bad execution plan
- Sql-server – Does SQL Server Management Studio 2017 Contain a New Explain Feature
- Sql-server – Is it normal for it to take up to a minute for an execution plan to be generated (even an estimated one) on simple queries
- Sql-server – way to see what were the Candidate Execution Plans generated by the Query Optimizer
Best Answer
There are many ways :
set statistics time on
which will give you: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.