Lesson Learned #367: Query Antipattern and Ring Buffer in Azure SQL Database

A few days ago, a customer asked us how they could capture statistics changes that result in a new execution plan, receive a notification when queries have anti-patterns, or changes in automatic SQL Server optimization. Next, I would like to show you some examples.

The script is used to create an Extended Event session named "CaptureChangesOnQuery" on a database. This session captures several different events related to automatic tuning, plan regression, settings changes, missing column statistics, query anti-patterns, and SQL statement recompilation. Let's go through each event and its details:

This is the definition of Extended Event:

CREATE  EVENT SESSION [CaptureChangesOnQuery] ON DATABASE 
ADD EVENT qds.automatic_tuning_config_change(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.compile_plan_guid,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT qds.automatic_tuning_error(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.compile_plan_guid,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT qds.automatic_tuning_plan_regression_detection_check_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.compile_plan_guid,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT qds.automatic_tuning_plan_regression_verification_check_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.compile_plan_guid,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.automatic_tuning_settings_changed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.compile_plan_guid,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.missing_column_statistics(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.compile_plan_guid,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.query_antipattern(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.compile_plan_guid,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.sql_statement_recompile(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.compile_plan_guid,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)

1. qds.automatic_tuning_config_change:

   • This event captures automatic tuning configuration changes.
   • The captured actions include the client application name, client hostname, compile plan GUID, database name, plan handle, query hash, query plan hash, session ID, SQL text, and username.

2. qds.automatic_tuning_error:

   • This event captures automatic tuning errors.
   • The captured actions are similar to the previous event and include the client application name, client hostname, compile plan GUID, database name, plan handle, query hash, query plan hash, session ID, SQL text, and username.

3. qds.automatic_tuning_plan_regression_detection_check_completed:

   • This event captures the completion of plan regression detection checks performed by automatic tuning.
   • The captured actions include the same set of properties as the previous events.

4. qds.automatic_tuning_plan_regression_verification_check_completed:

   • This event captures the completion of plan regression verification checks performed by automatic tuning.
   • The captured actions are the same as the previous events.

5. sqlserver.automatic_tuning_settings_changed:

   • This event captures changes made to automatic tuning settings.
   • The captured actions include the same set of properties as the previous events.

6. sqlserver.missing_column_statistics:

   • This event captures instances where column statistics are missing for a query execution.
   • The captured actions include the same set of properties as the previous events.

7. sqlserver.query_antipattern:

   • This event captures occurrences of query anti-patterns, which are inefficient query patterns that should be avoided.
   • The captured actions include the same set of properties as the previous events.

8. sqlserver.sql_statement_recompile:

   • This event captures SQL statement recompilations, which occur when a stored procedure or query plan needs to be recompiled.
   • The captured actions include the same set of properties as the previous events.

The target for all these events is the package0.ring_buffer, which is a target type used to store event data in a ring buffer memory area.

Once the event session is running, the specified events will generate data in the ring buffer, which can be queried or analyzed to gain insights into automatic tuning, plan regressions, settings changes, missing column statistics, query anti-patterns, and SQL statement recompilations occurring in the database.

I would like to share an example about it. For example, following the previous articles: 

Lesson Learned #45: CPU at 100% using nvarchar parameter data type in the filter against varchar col...

Lesson Learned #354: Why is Python using a lot of CPU of Azure SQ Database? - Microsoft Community Hu...

We are going to define the following Extended Event definition:

• In order to capture this, we are going to start the extended event ALTER EVENT SESSION [CaptureChangesOnQuery] ON DATABASE STATE = START 

• Run the query, for example, select * from [MSxyzTest].[_x_y_z_MS_HighCPU] where TextToSearch = N'Text 23123123' to generate an antipattern query, filtering using NVARCHAR in a column VARCHAR generating an implicit conversion.

• After it, running the query: select *, TRY_CONVERT(XML, target_data) from sys.dm_xe_database_session_targets we could see the following information:

This information helps us a lot to review any aspect that may cause performance issues in our queries. Finally, once we finished the process, we need to stop the extended event running the following query: ALTER EVENT SESSION [CaptureChangesOnQuery] ON DATABASE STATE = STOP 

Enjoy!