3

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:

enter image description here

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

  • 1
    What exact issue you are trying to address by doing above exercise ? – Kin Shah Sep 24 '15 at 13:21
  • @kin I want to update statistics in the most controlled mode, all the execution plans that I remove from the cache I want to put them back there, before the next morning, and without running the procs, if possible. – Marcello Miorelli Sep 24 '15 at 14:11
  • 2
    Other than plan guides (not fun!) I don't think you can do anything like that. And in most cases, like Grant said, you want new plans based on current, up-to-date statistics, not old plans that were based on different data. How have you confirmed that your slowdowns have anything to do with updating stats or recompiling plans? Have you measured compilation/recompilation events and their durations? Is it possible (even more likely) that actual changes to the data and/or shuffling of data in the buffer pool due to overnight ETL-style processes is actually what's causing your slowdown? – Aaron Bertrand Sep 24 '15 at 14:57
  • 3
    I have a suggestion, if this is something you see happening every day, then you should experiment tonight with not touching any statistics at all, and see if you still have your slowdown tomorrow morning. If you do, stop blaming statistics. – Aaron Bertrand Sep 24 '15 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.