11

I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution.

Am I correct in saying that statistics are only used when creating the execution plan for a stored procedure, and they are not used in the actual execution context? In other words, if this is true, once the plan is created (and assuming it's properly reused), how important are "up to date" statistics?

I was particularly motivated by an article I read (Statistics, row estimations and the ascending date column) which describes a scenario very similar to one I face daily with several of our client's databases.

We have an ascending date/time column in one of our largest tables that we query regularly using a specific stored procedure.

How do you prevent execution plans from growing stale when you have a hundred thousand rows being added a day?

If we're updating statistics frequently to combat this issue, would it make sense to use the OPTION (RECOMPILE) hint on this stored procedure's query?

Any advice or recommendations would be appreciated.

Update: I'm using SQL Server 2012 (SP1).

5

Am I correct in saying that statistics are only used when creating the execution plan for a stored procedure, and they are not used in the actual execution context?

No, what happens is that the execution plan for a stored procedure is cached. Assuming there is enough available memory to continue holding the plan, it won't change unless one of the following happens (from Execution Plan Caching and Reuse in the SQL Server documentation, emphasis added):

  • Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • Changes to any indexes used by the execution plan.
  • Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • Dropping an index used by the execution plan.
  • An explicit call to sp_recompile.
  • Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • Executing a stored procedure using the WITH RECOMPILE option.

So if the statistics are updated, the cached plan will automatically take the new stats into account and be recompiled.

How do you prevent execution plans from growing stale when you have a hundred thousand rows being added a day?

One way is if there are a lot of updates to the table, as mentioned above. A few hundred thousand changed rows may satisfy this condition. But if you want to be sure or have more granular control: by updating your stats. You can allow SQL Server to auto create and manage statistics, or manually do it yourself. You can find more info on either method at SQL Server Auto Update and Auto Create Statistics Options. When/if you do a weekly rebuild of indexes, this will also trigger the plans to be updated as well. Do some testing to see what is most beneficial to you, as updating statistics too often may not yield any real performance results.

If we're updating statistics frequently to combat this issue, would it make sense to use the OPTION (RECOMPILE) hint on this stored procedure's query?

You don't need to use RECOMPILE, since based off the excerpt above you can see that the execution plan gets updated appropriately whenever new stats are available. You may be fine with an end of day statistics update (if you're truly concerned) but I don't think it's explicitly a need based on what you've said so far. Again, though, I'd test it to see what impact this may have on your stored procedure performance and plan accordingly.

6

Am I correct in saying that statistics are only used when creating the execution plan

No, out-of-date statistics may cause an optimality-related recompilation of the affected statement.

We have an ascending date/time column in one of our largest tables that we query regularly

Sub-optimal execution plans caused by predicate values being outside (specifically above) the range of values stored in the corresponding statistics histogram is known as the Ascending Key Problem. Rebuilding statistics is one possible solution, but it can be quite resource-intensive. Alternatives include:

  • Trace flags 2389 and 2390. This requires that an index exists with the problematic column as the leading key. It does not work with partitioned tables, and is only effective in SQL Server 2014 if the original cardinality estimator is used. Trace flag 4139 may also be required if the statistics object is branded stationary.

  • Upgrade to SQL Server 2014. The new cardinality estimator includes logic to estimate beyond the histogram using average density information. This can be less accurate than the 2389/2390 trace flags in some important circumstances.

  • Enable more frequent automatic statistics updates for large tables with trace flag 2371. With this trace flag, instead of updating after 20% + 500 changes, only SQRT(1000 * Table rows) modifications are required. This is not as complete a solution as those mentioned previously, as updates may still not be triggered often enough.

If the source of your problem is not so much frequent plan compilations based on beyond-histogram predicate values, but more about the effects of occasionally caching such a bad plan as a result of parameter sniffing, you could also consider:

  • Disabling parameter sniffing using trace flag 4136
  • Using OPTIMIZE FOR (@parameter = value) to compile a plan for a known representative value
  • Using OPTIMIZE FOR (@parameter UNKNOWN) to optimize using the average distribution
  • Using OPTIMIZE FOR UNKNOWN (same as 4136, but per-query)
  • Using OPTION (RECOMPILE) to compile every time, sniffing the particular value. If the vast majority of runtime values are within the histogram, this may be effective.

For more information about parameter sniffing, embedding and the recompile options, see my article on SQLperformance.com.

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.