For disk-based tables, query plans for interpreted/traditional stored procedures will be recompiled when statistics have changed. That’s because when you update statistics, cached query plans for interpreted stored procedures are invalidated, and will automatically recompile the next time they’re executed. That’s true for an interpreted stored procedure that references disk-based tables, and/or memory-optimized tables.
As of SQL 2016, the database engine automatically updates statistics for memory-optimized tables (documentation here), but recompilation of native modules must still be performed manually. But hey, that’s way better than SQL 2014, when you couldn’t recompile at all; you had to drop/recreate the native module. And natively compiled stored procedures don’t reside in the plan cache, because they are executed directly by the database engine.
This post attempts to determine if the requirement to manually recompile native modules is any different for AG secondary replicas.
Stats on the primary
Statistics that are updated on the primary replica will eventually make their way to all secondary replicas. This blog post by Sunil Agarwal details what happens on the secondary replica if the statistics are stale (relative to any temporary statistics that were created on the secondary).
How do we…?
The first question we must answer is: how do you determine when the last time a natively compiled stored procedure was compiled?
We can do that by checking the value of the cached_time column from the following query:
|
1 2 3 |
SELECT * FROM sys.dm_exec_procedure_stats WHERE OBJECT_NAME(object_id) = '<YourModule>' |
The query is simple, but you won’t get any results unless you enable the collection of stored procedure execution statistics for natively compiled procedures. Execution statistics can be collected at the object level or instance level.
NOTE: Enabling the collection of stored procedure statistics for natively compiled procedures can crush your server, potentially resulting in disastrous performance impact. You must be extremely careful with this method of troubleshooting.
Once you’ve enabled stats collection for native procedures, you should get results from the query above.
How I tested
Here are the steps I executed, after creating an AG that used synchronous mode (containing a single database with a memory-optimized filegroup):
- Create a sample table
- Insert some rows
- Create a natively compiled procedure that selects from the sample table
- Execute the native procedure on the primary and secondary (it must be executed at least once in order to have usage stats collected)
- Enable collection of stored procedure execution statistics on the primary and secondary replicas
- Again execute the native procedure on the primary and secondary
- Note the value of sys.dm_exec_procedure_stats.cached_time on the primary and secondary
- Recompile the native procedure on the primary
- Execute the native procedure on the primary and secondary
- Again note the value of sys.dm_exec_procedure_stats.cached_time on the primary and secondary
Results
The cached_time value on the secondary did not get updated when the native module was recompiled on the primary.
What does this mean for DBAs that are responsible for maintaining AGs that use native compilation? It means that when you recompile native modules on the primary replica (which you would always do after updating statistics on the primary), those modules must be recompiled on all secondary replicas. The recompilation on the secondary can be performed manually or perhaps through some automated mechanism. For example, if you have a SQL Agent job on the primary replica to update statistics, one of the job steps might be for marking all natively compiled stored procedures on the secondary for recompilation, using sp_recompile.
How would that job step handle the recompile for all secondary replicas?
Perhaps after defining linked servers, you could do something like:
EXEC SecondaryServer1.msdb.dbo.sp_start_job @job_name = N’Recompile native procs’;
EXEC SecondaryServer2.msdb.dbo.sp_start_job @job_name = N’Recompile native procs’;
But it might be involved to define this for all secondary replicas – it sounds like a topic for another post…..
Happy recompiling –