SQL Server Performance Forum – Threads Archive
Recompilations due to Statistics-ChangedMy production database server (SQL Server 2005) hosts the main database (about 15GB).There are thousand of insert-update statements in this db during the day and the configuration of the db is:
Auto Update Statistics:True
Auto Update Statistics Asynchronously: False
Auto Create Statistics: True
Auto Shrink: True During the maintenance plan (which runs every night) there is an update-statistics task. Profiling the database, I found that there are many recompilations in some stored procedures due to Changes in Statistics (EventSubClass: 2-Statistics Changes) 1. Do you believe that the configuration of my production database is correct?
2. What could I do in order to reduce the recompilations caused by "statistics changed"?
Check this KB:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx a good article on how the performance issues can be addressed in SQL 2005. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.