Automatic Tuning Monitoring and Diagnostics

Cool new toys/tools have been made available to the data professional. Among these tools are query data store and automatic tuning. These two tools actually go hand in hand and work pretty nicely together.

With most new tools, there is usually some sort of instruction manual along with a section on how to troubleshoot the tool. In addition to the manual, you usually have some sort of guide as to whether or not the tool is working within desired specifications or not.

Thanks to Extended Events (XE), we have access to a guide of sorts that will help us better understand if our shiny new tool is operating as desired.

Operationally Sound

XE provides a handful of events to help us in evaluating the usage of Automatic Tuning in SQL Server. To find these events, we can simply issue a query such as the following.

select xo.name, xo.object_type, xo.description
From sys.dm_xe_objects xo
where xo.name like '%autom%tun%'
or xo.description like '%autom%tun%'
order by xo.object_type;

When executed, this query will provide a result set similar to the following.

I have grouped the results from this query into three sets. In the red set, I have four events that are useful in the diagnostics and monitoring of automatic tuning. These events show errors, diagnostic (and performance) data, configuration changes and state changes.

For instance, the state change event will fire when automatic tuning is enabled and will also fire when the database is started (assuming the session is running). The automatic_tuning_diagnostics event fires roughly every 30 minutes on my server to gather performance and diagnostic data that can help me understand how well the feature is performing for my workload in each database.

Highlighted in the green section is a couple of maps that show the various values for the current phase or state of the automatic tuning for each database. One can view these different values with the following query.

select mv.name,mv.map_key,mv.map_value
From sys.dm_xe_map_values mv
where mv.name in ('aprc_state','aprc_task')
order by mv.name, mv.map_key;

This query yields these results.

We will see those values in use in the events in a session shortly.

We have seen some of the events and some of the maps at a very quick glance. That said, it is a good time to pull it all together and create a session.

USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'AutoTuneMonitor' )
DROP EVENT SESSION AutoTuneMonitor
    ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION [AutoTuneMonitor] ON SERVER
ADD EVENT qds.automatic_tuning_config_change(
ACTION ( sqlserver.database_id, 
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
  )
,ADD EVENT qds.automatic_tuning_diagnostics(
ACTION ( sqlserver.database_id, 
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
  )
  ,ADD EVENT qds.automatic_tuning_state_change(
  ACTION ( sqlserver.database_id, 
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
)
  ,ADD EVENT qds.automatic_tuning_error(
  ACTION ( sqlserver.database_id, 
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
)
ADD TARGET package0.event_file(SET filename=N'C:DatabaseXEAutoTuneMonitor.xel')
,ADD TARGET package0.ring_buffer
 WITH (MAX_DISPATCH_LATENCY=5 SECONDS,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);
GO
ALTER EVENT SESSION [AutoTuneMonitor] ON SERVER STATE = START;
GO

Seeing as this session won’t produce any results without Query data store being enabled and automatic tuning being configured for a database, I have set all of that up in a demo database and have some fresh results to display.

Here I show an example of the output filtered for just the diagnostics event. Note the phase_code shows some of those map values previously discussed. I can also see that roughly every 30 minutes each database undergoes a diagnostics check.

Now, looking at another event in that same session, I can see the following.

The state_code in this event payload demonstrates more values from the maps previously discussed (CorrectionEnabled and DetectionEnabled). In this case, the automatic_tuning_state_change fired a few times for database 6 because that database was intentionally taken offline and set back online to test the event.

The use of these particular events in this session is very lightweight. I don’t have a predicate configured for any of the events because I wanted to trap everything. Of course, the number of events can increase with an increased load and usage scenarios on different servers.

The Wrap

Automatic tuning can be a pretty sharp tool in your tool-belt on your way to becoming that rock-star DBA. As you start to sharpen your skills with this tool, you will need to have some usage and diagnostic information at your fingertips to ensure everything is running steady. This event session is able to provide that diagnostic information and keep you on top of the automatic tuning engine.

For more uses of Extended Events, I recommend my series of articles designed to help you learn XE little by little.

Interested in seeing the power of XE over Profiler? Check this one out!

This has been the eleventh article in the 2018 “12 Days of Christmas” series. For a full listing of the articles, visit this page.