January 14, 2010 at 10:13 am
sql 2000 database which has a check mark on Auto Create Statistics.
If all the other option on th DATABASE OPTIONS setting is off, would this still causes compilation locks due to statistics changes?
Very curious about it.
Our application is gettig compilation locks on procedures. In the trace event it is caused by #2:
2 - Statistics changed (rowmodctr in sysindexes).
I thought only the option Auto Update Statistics would generate the statistics changes.
Does any one know?
January 14, 2010 at 4:49 pm
I thought only the option Auto Update Statistics would generate the statistics changes.
Does any one know?
Auto create statistics will create column statistics automatically if a query is hitting the column and SQL does not already have statistics for that column. Auto update statistics allows SQL to update either user or system created statistics that become outdated. I would recommend keeping both auto create and auto update statistics on unless there is a very compelling reason not to. Statistics are how SQL generates execution plans and if the stats are old or non-existent SQL may generate a poor execution plan which leads to poor performance.
As for the compilation locks, this KB article may help.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply