Database Stats in AlwaysOn Secondary Replicas

I’ve been doing a lot of presenting lately on the new feature in SQL Server 2012—AlwaysOn Availability Groups, though most of by presentations are more configuration and infrastructure related, I am always interested in system performance. So one day, while getting ready for a presentation I read this in books online. Little did I know the rabbit hole it would lead me down.

Statistics for Read-Only Access Databases

Statistics on columns of tables and indexed views are used to optimize query plans. For availability groups, statistics that are created and maintained on the primary databases are automatically persisted on the secondary databases as part of applying the transaction log records. However, the read-only workload on the secondary databases may need different statistics than those that are created on the primary databases. However, because secondary databases are restricted to read-only access, statistics cannot be created on the secondary databases.

To address this problem, the secondary replica creates and maintains temporary statistics for secondary databases in tempdb. The suffix _readonly_database_statistic is appended to the name of temporary statistics to differentiate them from the permanent statistics that are persisted from the primary database.

Only SQL Server can create and update temporary statistics. However, you can delete temporary statistics and monitor their properties using the same tools that you use for permanent statistics:

Delete temporary statistics using the DROP STATISTICS Transact-SQL statement.

Monitor statistics using the sys.stats and sys.stats_columns catalog views. sys_stats includes a column, is_temporary, to indicate which statistics are permanent and which are temporary.

So Microsoft says that the stats are created in TempDB. So let’s test that theory. I have test stats table in AdventureWorks 2012, and the column “Gender” is not indexed, so it doesn’t currently have a statistic associated with it. So when we query it a statistic will get created

The is_temporary column on sys.stats shows that the statistic indicates that the stat is temporary—also, the _readonly_database_statistics suffix on the name of the statistic also indicates that the stat is of a temporary nature. But why is it showing up in AdventureWorks instead or TempDB—let’s see if it shows up in TempDB.

So it’s not in TempDB—even though books online says it is. This is where the chase down the rabbit hole begins. First, I looked up the definition of sys.stats.

I found that sys.stats joined sys.sysidxstats and tempstatvals. Both of which I presumed where in ResourceDB (which is a hidden system database that we can’t easily query). A google search revealed:

To query ResourceDB you can put your instance into single user mode (-m) and issue a “use mssqlsystemresource” (Thanks Adam Machanic for that). But I decided to be really bold and break out a hex editor. (Using a backup copy of ResourceDB). That got me this:

The crux of that is tempvalstats is derived from tempdb.sys.sysobjvalues (specifically valclass = 54). I suspect that the SystemValueClass is related to hex value below that I haven’t been able to decode.

So what all this shows me is that tempdb.sys.sysobjvalues OBJID is equal to the database id, subobjid is the object_id in the child database, and I’m not sure what the valnum column represents. Value and imageval are hex that I was unsuccessful in trying to convert to anything. However, what this finally shows me is that indeed the column statistic has been created in TempDB, which makes perfect sense, since the replica database is read only.

I hope you enjoyed this trip down the rabbit hole. Special thanks to Jonathan Kehayias, Adam Machanic, and Erin Stellato for their assistance in figuring all of this stuff out.