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)
- CREATE INDEX – identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.
- 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.
- 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