SQL Server Performance Metrics – Impact of Enabling Auto-Stats

metricssql serversql-server-2008-r2

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.

Best Answer

If SET AUTO_CREATE_STATISTICS ON improved performance (anecdotally) for your end users, it likely means that queries with predicates on columns without statistics have gotten better execution plans, and thus shorter runtimes. A "better" execution plan could result in one or more of these improvements:

  • Fewer logical / physical reads
  • Lower CPU usage
  • Shorter query runtimes / duration

I'm not sure what metrics you're capturing. If you're able to get to the level of individual query execution time, you could find examples of the same queries before and after the change and confirm that they have reduced runtimes (or less CPU and reads, etc).

For example, if you're logging the contents of sys.dm_exec_query_stats to a table, you could find queries that have the same sql_handle but lower CPU, reads, etc after the setting was changed.

If you are logging execution plan XML, you might be able to look for the presence, and then absence, of warnings like this:

Screenshot of no stats warning

It shows up in the XML like this:

  <Warnings>
    <ColumnsWithNoStatistics>
      <ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Hist]" Column="Num" />
    </ColumnsWithNoStatistics>
  </Warnings>

If you just have server level aggregated metrics to look at, you might still see overall reads or CPU has gone down, but it's probably a lot harder to see that for sure if it's watered down by other workloads and maintenance activities.

Related Question