One of the features Microsoft wants us to use for Azure SQL Database  is Automatic Tuning. Automatic Tuning is a feature where you can think of it as entering the world of self-running and self-tuning databases.

It is said to be safe, reliable and proven using complex algorithms and built-in intelligence where it can do the following (see this link for more details: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning)

1. CREATE INDEX – identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.
2. DROP INDEX – identifies redundant and duplicate indexes daily, except for unique indexes, and indexes that were not used for a long time (>90 days). Please note that at this time the option is not compatible with applications using partition switching and index hints.
3. FORCE LAST GOOD PLAN – identifies SQL queries using execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan.

Personally I don’t  enable the option where it is allowed a “free-for-all” when creating/dropping indexes and forcing certain query plans. I like controlling the change, especially for production databases. To force this concept I wanted to use Extended Events to know when / if someone changed my settings for automatic tuning against my database.

For this example I use the ring buffer (and query it), really we should be using Azure storage but I just wanted to prove how we can track these changes. If you want to setup writing your sessions to Azure storage please click this link (https://blobeater.blog/2018/05/08/extended-events-using-azure-storage/)

The code to create the event session is shown below.

CREATE EVENT SESSION azure_autotune
ON DATABASE

ADD EVENT sqlserver.automatic_tuning_settings_changed
( ACTION (	sqlserver.database_name,sqlserver.database_id,
			sqlserver.client_hostname,sqlserver.client_pid,sqlserver.username)),
			ADD EVENT sqlserver.sql_statement_completed

ADD TARGET package0.ring_buffer
(SET max_memory = 500, max_events_limit = 10 );
 

Then to start it, quite simple.

 ALTER EVENT SESSION azure_autotune
ON DATABASE
STATE = START;

I then connect to the database via a different query window and change some settings for automatic tuning, such as

ALTER DATABASE current SET AUTOMATIC_TUNING (CREATE_INDEX = ON )

I then run the following query to query the ring buffer.

 DECLARE @Shredit XML;
	SELECT @Shredit = CAST(target_data AS XML)
	FROM sys.dm_xe_database_sessions AS s
	JOIN sys.dm_xe_database_session_targets AS t
	ON t.event_session_address = s.address
	WHERE s.name = N'azure_autotune';

	SELECT
	qp.query('.') AS event_data
	FROM @Shredit.nodes('RingBufferTarget/event') AS q(qp);

It does work!

Click on the XML to get the details.

Well I do hope you found this useful, do not forget to cleanup after.

DROP EVENT SESSION azure_autotune ON DATABASE