February 3, 2012 at 1:54 pm
Hello,
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)
Test:
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?
ty
February 6, 2012 at 7:27 am
I've also added trace flag 205 and started the profiler to gather missed information, nothing is triggered after the initial update.
(except when the plan is first run, statistics get updated once as per the test description in the initial post).
Also when I use sp_updatestats, it notice that statistics needs to be updated and does it.
rowmodctr is back down to 0 once done.
Still auto update stats never fired
February 6, 2012 at 7:40 am
February 6, 2012 at 8:11 am
johnitech.itech (2/6/2012)
Thank you for the link, I've met the threshold they refer to in my test example.
What's interesting however is this link:
http://msdn.microsoft.com/en-us/library/dd535534%28SQL.100%29.aspx
February 16, 2012 at 7:50 am
I'm mostly done with the white paper from: http://msdn.microsoft.com/en-us/library/dd535534%28SQL.100%29.aspx
and I've found with colmodctr what I need to go on.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply