vendredi 2 janvier 2015

Understanding statistics, execution plans, and 'ascending key problem'


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





Aucun commentaire:

Enregistrer un commentaire