AUTO_UPDATE_STATISTICS_ASYNC on a database, what performance metric will best display the changes?
We just realized that the database for a legacy system has never had the
AUTO_UPDATE_STATISTICS_ASYNC turned on (
AUTO_UPDATE_STATISTICS was turned on).
As you would expect, after turning those on, our applications have performed much better. Response times are significantly better and the users are not waiting for the screen to refresh or pages to load.
The database is well indexed & we had already manually created stats where we thought they were needed. Immediately after enabling I checked
sys.stats (joined on
sys.objects) and it only needed to create stats on three objects. (I have these logged in case quantifying these changes lies with them.)
I capture a lot of performance data on this server and I have looked at that data from prior to the change and after, but I have yet to pinpoint where the actual gains are.
I have other similar systems where auto-Stats are still turned off for that database. Those systems would also benefit from enabling them, but I am waiting in case there is some metric that I do not yet have, I can add it before making the changes.
My real question
Aside from the user’s experience, how do I quantify the increases made by changing the Auto-Stats settings on a system?
SQL Server 2008R2 on on Windows Server 2008.