SQL Server 2022 introduces the query_antipattern Extended Event (XEvent),
identifying anti-patterns in SQL queries sent to the server.
Including this event in a XEvent session helps us identify potential application issues early in the SDLC (Software Development Life Cycle). If a query contains certain antipatterns, they are detected during optimization. When this happens, and the query_antipattern event is part of an active XEvent session, the output will capture relevant fields. This allows for quick identification of queries that need tuning.
Query optimization antipatterns captured by the query_antipattern event include:
- Column data type conversion prevents index seek (implicit type conversion)
- Non-optimal OR clauses
- Large IN clauses
- Many OR predicates
Let's have an example and see how it works.
1. Open SSMS > connect to a SQL Server 2022 > Management > Extended Events > right-click Sessions > New Session...
2. Enter a Session Name, you can also tick the checkboxes of Start the event session at server startup and Start the event session immediately after session creation.
3. Go to "Events" page, under Event library enter "antipattern", then click on the "query_antipattern" row to highlight it, you will see the description and the event fields of this event.
4. Click the right arrow ">" button to add the query_antipattern event to the Selected events list, click on it, then click the Configure button.
5. In the "Global fields (Actions)" tab, tick the checkboxes of "plan_handle" and "sql_text".
6. Go to "Data Storage" page, choose "event file" type, enter the file path and file name, set maximum file size and rollover. Click OK button to create the event session.
7. The session is created. Right-click the event file under the created session, click View Target Data...
8. You can see there are some events being captured. Click on an event to show the details. Under the Details, you can see the antipattern type (a "local" event field), plan_handle and sql_text (both are Global fields).
9. Double-click on plan_handle to open a new window, click Copy All button to copy the plan handle.
10. Open a new query window, run the following query with the plan_handle value:
-- Get Execution plan by plan handle
USE master
GO
SELECT DB_NAME(dbid), * FROM sys.dm_exec_query_plan (<plan_handle value>);