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
