0

In Microsoft SQL Server, is it possible to find how much time the query optimizer spent to generate N numbers of execution plans? (Let's say for a specific stored procedure).

Also, is it possible to know how many execution plans the query optimizer generated and which execution plan it has selected for that specific stored procedure?

Or does the query optimizer completely works like a sealed box?

Thanks!

1

Yes, SQL Server can report how long it took to do any of those actions (though you may have to run it to get some additional details such as actual row counts returned)

Statistics Time

SET STATISTICS TIME { ON | OFF }

Execution Plans

To pull the execution plan you have many options:

  • Query the plan cache: (https://www.mssqltips.com/sqlservertip/1661/analyzing-the-sql-server-plan-cache/)
  • Trace/extended events the xml: https://www.mssqltips.com/sqlservertip/1264/capturing-graphical-query-plans-with-sql-server-profiler/)
  • Gather it in SSMS: via the "Include Actual Execution Plan" button in the toolbar https://msdn.microsoft.com/en-us/library/ms189562.aspx

    • On the SQL Server Management Studio toolbar, click Database Engine Query. You can also open an existing query and display the estimated execution plan by clicking the Open File toolbar button and locating the existing query.

    • Enter the query for which you would like to display the actual execution plan.

    • On the Query menu, click Include Actual Execution Plan or click the Include Actual Execution Plan toolbar button

    • Execute the query by clicking the Execute toolbar button. The plan used by the query optimizer is displayed on the Execution Plan tab in the results pane. Pause the mouse over the logical and physical operators to view the description and properties of the operators in the displayed ToolTip.

    • Alternatively, you can view operator properties in the Properties window. If Properties is not visible, right-click an operator and select Properties. Select an operator to view its properties.

    • You can alter the display of the execution plan by right-clicking the execution plan and selecting Zoom In, Zoom Out, Custom Zoom, or Zoom to Fit. Zoom In and Zoom Out allow you to zoom in or out on the execution plan, while Custom Zoom allows you to define your own zoom, such as zooming at 80 percent. Zoom to Fit magnifies the execution plan to fit the result pane.

  • I actually know these. I am more interested in finding the time query optimizer spent to generate N numbers of execution plan. syscacheobjects gives me the cached objects in the SQL server. The only thing I get from that is (for Compiled Plan cache objects) what objects are compiled and their stored procedures are generated and how many times they have been invoked. So I am literally interested in the working mechanism of the query optimizer, not some random query. – Michael Wayne Aug 5 '16 at 15:07
  • The CPU time is breaking out how long the plan takes to generate the plan, though I dont know if you can separate the algebrizer, optimizer, and other parts of building the request. Off the top I dont have a way to determine that for each cached plan, though it may be available. – ConstantineK Aug 5 '16 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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