It’s not very often that I feel that Microsoft needs to be beat about the head and shoulders a little about something, but there needs to be a warning label that pops up when going through the properties screen for your databases for the Auto_Update_Statistics option….maybe something akin to a cigarette package:
|Surgeon General Warning!
Use of this option on highly transactional systems does not necessarily guarantee that statistics will always be updated. SQL Server will do its best to manage the statistics but be advised that not keeping an eye on them will inevitably lead to crappy performance and a call to a consultant around 2:00 am in the morning. This will lead to a grumpy consultant for at least the next 24-48 hour period.
I don’t know how many times I walk into a database shop that is having performance issues on a system that has transactions flying all over the place. The conversation usually goes something like this after I have had time to look at the system:
AJ: “Okay, so what are you doing about indexing and statistics updates?”
DBA: “We are doing a maintenance plan to update the indexes”
AJ:”What about the statistics?”
DBA:”We have Auto_Update_Statistics turned on.”
AJ:”That doesn’t necessarily guarantee….”
DBA:”Microsoft says blah,blah,blah…”
AJ:”Just humor me and try this..”
DBA:”Amazing it worked!”
How about it Microsoft? Can you throw me this one little bone in the next service pack release?! One less phone call at 2:00 in the morning would really make my day!