Statistics is initially created while retrieving the data from table using the RowSample as 1000 records which are initially inserted, when we insert the Next 10000 records and retrieving the data with same Filter criteria, Query optimizer will reuse the Same Statistics, so its not triggering the engine to update Statistics

If you check the Statistics Updated time after inserting 10000 records, it will be same as before.

If we change the Filter criteria like this

SELECT FirstName, LastName, City FROM ExecTable WHERE FirstName=’Bob’ and City=’New York’

It will trigger the Statistics Update process and create a new statistics based on FirstName and also update the Statistics based on City

I think the issue lies with the fact that the plan generated for the query is a “Trivial Plan”. This can be checked by clicking on the Select icon on the Actual Execution plan and looking at the “Optimisation Level” value in the properties window.

In this case the statistics do get marked for update by the background auto-update stats process but aren’t actually updated since the Trivial Plan present in the cache is never recompiled irrespective of the number of times it is used. This is true even if we encapsulate the select into a stored procedure.

The statistics update on the city column is triggered by operations like (though not limited to) – Explicit Procedure/Plan recompilation – DBCC FREEPROCCACHE – OPTION RECOMPILE as per John’s suggestion above – New query compiled which makes use of the statistics created on this column as per Arunraj’s suggestion above

More information can be found under the heading “Skipping the Recompilation Step” on this link:

Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and has written over 4000 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 16+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.