how to minimize the effect of updating statistics on the plan cache?

**The issue I am trying to address:** After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: http://www.sqlservercentral.com/Forums/Topic1310374-1550-1.aspx > If updating the statistics slows down the performance of the query, > it's likely because a new execution plan was created based on the > updated statistics. > > You'll need to look at the plans before & after the statistics update > to understand exactly why. > > Usually, in most circumstances, the reverse is true, out of date > statistics leads to poor performance. > > But sometimes this does happen. The exact solution depends on what's > causing the problem within your query and based on what's been posted > so far, I can't be sure. It might simply be bad parameter sniffing. Regarding When To Update Statistics? and the effects of doing this > Flushing a plan from cache is determined by memory pressure. > > Statistics updates cause plan recompilations from this link: SQL Server: Do Statistics Updates cause query plans to be flushed? Should they? and from this link: Execution Plan Caching and Reuse **Recompiling Execution Plans** The conditions that invalidate an execution plan include the following: > 1 - Changes made to a table or view referenced by the query (ALTER > TABLE and ALTER VIEW). > > 2 - Changes made to a single procedure, which would drop all plans for > that procedure from the cache (ALTER PROCEDURE). > > 3 - Changes to any indexes used by the execution plan. > > 4 - Updates on statistics used by the execution plan, generated either > explicitly from a statement, such as UPDATE STATISTICS, or generated > automatically. > > 5 - Dropping an index used by the execution plan. > > 6 - An explicit call to sp_recompile. > > 7 - Large numbers of changes to keys (generated by INSERT or DELETE > statements from other users that modify a table referenced by the > query). > > 8 - For tables with triggers, if the number of rows in the inserted or > deleted tables grows significantly. > > 9 - Executing a stored procedure using the WITH RECOMPILE option. What I would like to achieve is, **BEFORE** I update the statistics of a table, I go through the Plan cache, and I find **ALL** the procedures that are related to myTable. I can find all stored procedures that are related to a table, AND all tables that are related to a stored procedure using the script below: --------------------------------------------------- -- script to find: -- all the stored procedures that touch table @table -- all tables that are touched by @sp_name stored procedure -- when setting null to parametes show all -- marcelo miorelli 15-dec-2014 --------------------------------------------------- print @@servername print db_name() DECLARE @sp_name NVARCHAR(128) = NULL -- 'Custom_CSLA_GetCategoryHierarchyAsXML' ,@table NVARCHAR(128) = 'SizeGuide' --NULL-- select [Table Name] = schema_name(o.schema_id) + '.' + o.Name, [Found In] = sp.Name, sp.type_desc from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id inner join sys.objects sp on sd.referencing_id = sp.object_id --and sp.type in ('P', 'FN') where ((@table IS NULL) OR (o.name = @table)) AND ((@sp_name IS NULL) OR (sp.Name = @sp_name)) order by sp.Name the example above shows me all the stored procedures, and views, that touch a table called *'SizeGuide'*, on different schemas as you can see on the picture below: then before I update the stats of table *'SizeGuide'* I generate a query plan/execution plan for all procedures that touch that table AND had a plan on cache before I did the update stats. I would do this when the server is less busy, so the next day, I have a plan cache, exactly like I had PRIOR to update stats, but all recompiled (only for the plans that were in the cache). Is this question clear? basically what I would like to do is to: 1- find all the query plans I have for stored procedures related to myTable 2- update the statistics of myTable 3 - re-generate all the query plans I had before the update stats Next day no RECOMPILES