Sql-server – Understanding statistics, execution plans, and ‘ascending key problem’

execution-plansql serversql-server-2012statistics

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).

Best Answer

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.

Related Solutions

Sql-server – SQL Server using incorrect plan

It looks like you have mostly answered your other questions in your monologue, and the things that made a difference are the standard list of things to do to force a recompile after being afflicted by parameter sniffing.

To answer your last question, the one marked my question is this:

What would make SQL Server produce a totally different plan, based on totally incorrect statistics when called with the same parameters and same data through the method I described?

The answer is that the plan cache is very specific about SET settings that are in effect on the session that is presenting the query. A different SET setting could need a very different plan, such as the setting SET ANSI_NULLS. Very likely, your connection from SSMS has different settings from what your .Net application. This is easily verifiable by using SQL Server Profiler to trace for Existing Connections and Login Audits, which show you the SET settings.

The effect is that the seemingly same query will result in a Cache Miss and a new one has to be created, which will be somewhat based on the parameters being presented to SQL Server for this instance of the query.

Sql-server – Understanding execution plans using TVPs

I don't think this query is about TVPs so much as complexity. The plan has an optimizer timeout. You can see it in the F4 properties of the SELECT operator as "Reason for Early Termination of Statement = Time Out" or StatementOptmEarlyAbortReason="TimeOut" in the plan xml. These are not always a disaster, it just means the optimizer has run out of time (more iterations) to come up with a plan and picks the lowest cost one it has at that stage. Optimizer timeouts are often a sign of over-complexity in a query and the common recommendation is to simplify, eg break it up, remove unnecessary parameters and joins etc

Looking at your query, there are 13 tables. Looking in the plan, many of them just do a "SELECT all records" from the lookup table ( eg all records from dbo.Station for the @from and @to tables, all ticketFilter and ticketClass records ), so actually you don't need these tables and removing them does not change the result. I understand this is a user-driven query, so you might look at approach where you only join to the tables if the user has actually applied a filter.

I can reproduce the timeout, and remove it by commenting out 4 of the tables, eg:

SELECT d.Month_Year[PeriodName], qg.Title [Service_Area], AVG((CAST(sa.Answer AS smallmoney) * 10.00)) [Average]
FROM [dbo].StaticDataView d WITH (NOEXPAND)
    INNER JOIN [dbo].[Survey] s  ON (s.CustomerJourney = d.Journey_Id)
        INNER JOIN [dbo].[SurveyAnswer] sa   ON (sa.Survey = s.ID)
            INNER JOIN [dbo].[Question] q  ON (q.ID = sa.Question)
                INNER JOIN [dbo].[QuestionGroup] qg  ON (qg.ID = q.QuestionGroupID)

    --INNER JOIN @StationFrom sfl ON (sfl.ID = d.[Source])
    --INNER JOIN @StationTo stl ON (stl.ID = d.[Dest])
    INNER JOIN @InitialScores sc ON (s.Score = sc.ID)
    INNER JOIN @RouteList rl ON (rl.ID = d.Route_ID) -- route list
    --INNER JOIN @TicketClass tc ON (tc.ID = d.Class_ID)
    --INNER JOIN @TicketTypeFilter ttl ON (ttl.ID = d.Ticket_Filter_Type_ID)
    INNER JOIN @TicketDiscount td ON (td.ID = d.Discount_Type_ID)
    INNER JOIN @Days dy ON (dy.ID = d.[DayOfWeek])

WHERE s.DateCompleted IS NOT NULL AND sa.Answer != -1
AND (d.RSID = COALESCE(@RSID, d.RSID))
AND (d.[Date] BETWEEN @Start AND @End) -- between dates
AND (d.[Time] BETWEEN @StartTime AND @EndTime) -- between times
AND ((@Direction IS NULL AND d.Direction IN (0,1)) -- Both
        OR (@Direction = 1 AND d.Direction = 1) -- North
        OR (@Direction = 0 AND d.Direction = 0)) -- South
AND ((@PassengerJourney = 0 AND d.Journey_Type_ID IN (1,2,3)) -- ALL
    OR (@PassengerJourney IN (1,2) AND @PassengerJourney = d.Journey_Type_ID) -- Single or Return
    OR (@PassengerJourney = 3 AND NULLIF(d.RSID_LEG_2,'') IS NOT NULL)) -- multi part
GROUP BY d.Month_Year, qg.Title
OPTION ( RECOMPILE )

Does this make for a better plan? Hard to say, as in my simple rig, I can't get this query to run in more than half a second, with or without the timeout. Even SQLFiddle doesn't seem to struggle. Can you see much difference between the SQLFiddle repro and your setup?

Best Answer

Related Solutions

Sql-server – SQL Server using incorrect plan

Sql-server – Understanding execution plans using TVPs

Related Question