Performance Metrics After Enabling Auto-Stats
5
votes
1
answer
135
views
### Short Version
After enabling
AUTO_CREATE_STATISTICS
& AUTO_UPDATE_STATISTICS_ASYNC
on a database, what performance metric will best display the changes?
### Long Version
We just realized that the database for a legacy system has never had the AUTO_CREATE_STATISTICS
& 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.stats_columns
& 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.
Asked by DBADon
(325 rep)
Aug 28, 2019, 05:57 PM
Last activity: Aug 29, 2019, 01:37 AM
Last activity: Aug 29, 2019, 01:37 AM