Share via

Incremental statistics in SQL Server 2017

James Tuggey 1 Reputation point
2021-08-26T15:30:17.907+00:00

SQL Server 2017 CU 24

Table partitioned on a daily value

Incremental statistics show a modification_counter despite no DML impacting the table, how or why is this occurring.

I am trying to set up a periodic job that will run incremental statistics on large tables with just inserted data but the sys.dm_db_incremental_stats_properties job shows a modification_counter of the last insert repeatedly despite the fact that the stats have been run and reset the modification_counter to 0

Any help would be appreciated.

Thanks,
James

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2021-08-26T21:17:44.34+00:00

    How do you know that no DML is affecting the table?

    Did you add a DML trigger that writes to a log table, and it was empty the next day?

    0 comments
  2. CathyJi-MSFT 21,131 Reputation points Microsoft Vendor
    2021-08-27T08:04:53.217+00:00

    Hi @James Tuggey ,

    Agree with Erland. Suggest you to capture this and share us the result. For creating DML trigger, please refer to MS document to get detail steps.

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

    0 comments