How to identify the statistics used in a cached query plan

https://blogfabiano.com/2012/07/03/statistics-used-in-a-cached-query-plan/

http://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx

https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2017-showplan-enhancements/

Advertisements

Publication properties: immediate_Sync and allow_anonymous

What Immediate_sync means in Transactional Replication – Part1

Limit snapshot size when adding new article to SQL Server replication

What Immediate_sync means in Transactional Replication

How Replication setting Immediate_sync may cause Transactional Replication Distribution database growth

Replication – Adding New Article without Generating Snapshot

 

Statistics: Rowmodctr or modification_counter Key take aways

Statistics: Rowmodctr or modification_counter Key take aways

  1. Rowmodctr or modification_counter does not track the “percentage of the table that has been modified,” a common misconception. They really track the “number of times a DML operation affected key values in the index.”
  2. The counters know nothing about whether the modifications were to one row repeatedly or to many rows. They are not perfect – and they can’t exactly tell us “how much” of the table is changed.
  3. Auto update stats is triggered by query optimization – that is, the optimizer needs to take a “new” query, where the statistics used for optimization have been invalidated, and it will request new statistics, assuming auto update stats is on, which are collected just before this new query is optimized. On a real, running system this probably happens very quickly, but some cases exist where a plan might be out in cache and no optimization happens to make the stats update.