I'm surprised at the current behavior I'm having and I can't figure out why statistics don't update themselves when an SP run to properly update the plan if needed.
I've attached 3 scripts.
One to create the setup (CreateTable.sql)
One to query statistics metadata (QueryStatistics.sql) (yes some query overlap themselves)
One to quickly load data into the test table and run 2 SPs (DataLoad.sql)
1 - Run CreateTable.sql
2 - Run Section 1 of DataLoad.sql
At that point 2000 rows will be added in the table
3 - Run QueryStatistics.sql shows "rowcnt: 2000" and "rowmodctr: 2000"
so far so good.
4 - Run Section 3 of DataLoad.sql (SPs get compiled for the first time)
5 - Run QueryStatistics.sql
rowmodctr is down to 0 which is ok (statistics were refreshed) and last_updated is now set because we have refreshed the statistics
Still ok up to that point
6 - Run Section 1 of DataLoad.sql 3 times
6000 rows should have been added for a total of 8000
7 - Run QueryStatistics.sql
"rowcnt: 8000" and "rowmodctr: 6000"
Index row sampled: 2000, discrepancy of 6000 rows!
Even when running Section 3 of DataLoad.sql to invoke the SPs, statistics are not refreshed.
Now this is bugging me. At that point I would have expected the statistics to be refreshed as per:
where statistics get refreshed when 500 rows + 20% gets modified
Even if I update all of them (using the commented section of DataLoad.sql) statistics don't get refreshed when running the SPs. rowmodctr still goes higher.
Of course if I alter one procedure, a new plan is generated for that procedure and statistics get refreshed for the proper objects used by that SP.
I've tested this on SS2k8R2 RTM (10.50.1600), SP1 (10.50.2500) and 10.50.1617
What am I missing or doing wrong?