Sql-server – SQL Server Query Optimizer Time Spent and Numbers

execution-planoptimizationsql serverstored-procedures

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!

Best Answer

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 }

Displays the number of milliseconds required to parse, compile, and execute each statement. https://msdn.microsoft.com/en-us/library/ms190287.aspx

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.

Related Solutions

SQL Server – Different Plan for Ad-Hoc vs Stored Procedure

Usual suspects:

constants in adhoc, parameters in code
mismatch of data types in code
parameter sniffing

Point 1: the optimiser can choose the best plan for the constants.
Change the constants = change the plan. A parametrised plen is resuable

Point 2 will introduce implicit conversions because of datatype precedence
eg varchar column compared to nvarchar parameter

Point 3: use parameter masking or OPTIMISE FOR UNKNOWN
Edit: To test: run stored proc, run sp_updatestats, run again. This will invalidate cached plans which is better then clearing plan cache

Edit: after jcolebrand's comment

You can disable sniffing several ways. The main 3 are

RECOMPILE. This is silly IMO.
OPTIMIZE (sic) FOR UNKNOWN
Parameter masking

Parameter masking:

DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam

SELECT...WHERE column = @MaskedParam

Masking and the OPTIMIZE hint have the same effect (maybe for different reasons). That is, the optimiser has to use statistics and data distribution (Note: still under testing by Mark Storey-Smith) ~~evaluate the parameters on their own merits ?~~ , rather than what they were last call. The optimiser can recompile or not. SQL Server 2005 added statement level recompilation so there was less impact

Now, why a plan with "sniffed" parameters is "sticky" compared to masked/"unknown" parameters, I'm not sure.

I've used parameter masking since SQL Server 2000 for all but the simplest code. I've noted that it is liable to happen with more complex code. And at my old job I has some report procs that I could change the plan parameter defaults. I reckon the "cargo cult" approach was easier than a support call.

Edit 2, 12 Oct 2011, after some chat

Parameter masking and OPTIMISE FOR UNKNOWN have the same effect as far as I can tell
The hint is cleaner than masking but was added with SQL Server 2008.
Parameter sniffing happens at compile time.
WITH RECOMPILE generates a new plan each execution. This means a poor choice of defaults will influence the plan. At my last job, I could demonstrate this easily with some report code: changing parameter defaults altered the plan regardless of supplied parameters.
This MS Connect article is interesting: Suboptimal index usage within stored procedure (mentioned in one of the SO answers below)
Bob Beauchemin mentions it too

Outstanding issues

Does sniffing still apply with WITH RECOMPILE? That is, if the optimiser knows to discard the plan does it aim for re-use?
Why are sniffed plans "sticky"?

Links from SO:

https://stackoverflow.com/q/6919166/27535
https://stackoverflow.com/q/5236631/27535
https://stackoverflow.com/q/1392307/27535
https://stackoverflow.com/q/2905440/27535
https://stackoverflow.com/q/467513/27535
https://stackoverflow.com/q/272726/27535
Remus Rusanu (MS SQL team) and I had a bunfight here on SO
Whitepaper on MSDN in conjunction with my SO analysis: https://stackoverflow.com/questions/414336/why-does-the-sqlserver-optimizer-get-so-confused-with-parameters/414368#414368

Sql-server – Monumental difference in execution time between queries when using RECOMPILE query hint

As documented in the article Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a reduced-quality estimate, or a guess for selectivity of the predicate. Use parameters or literals in the query instead of local variables

When the optimiser has no usable statistics at all for a column it will guess that an = predicate will match 10% of rows, BETWEEN 9%, and any of >, >=, < and <= will match 30%. If there are column statistics available an = predicate will be treated differently as below.

Even when local variables are used in a query, an estimate that is better than a guess is used in the case of equality predicates. Selectivity for conditions of the form "@local_variable = column_name" is estimated using the average value frequency from the histogram for column_name. So, for example, if the column column_name contains all unique values, then a selectivity estimate of 1/(number of unique values in column) will be used, which is accurate.

So this is essentially the same as using for OPTIMIZE FOR (UNKNOWN). It may well be more accurate than a flat 10% guess but it isn't tailored to the specific values that you are querying.

To force SQL Server to optimize a query every time it is run, and use the values of local variables to estimate cardinality and cost during optimization of the query, add the RECOMPILE query hint to the query.

With the use of RECOMPILE you presumably are getting more accurate cardinality estimates and therefore a different plan with join orders/ join types more suited to the number of rows returned from different parts of your actual query.