One of the new Extended Event available in SQL Server 2022 is the query_antipattern. This extended event allows to identify anti-patterns on the SQL queries sent to the server. An anti-pattern in this case is some code that the SQL Server optimizer can’t do a great job optimizing the code (but cannot correct the issue automatically).
This is a very interesting possibility: Including this event in a session allow us to identify potential problems in applications. We can do this in development environments to the the problems earlier in the SDLC (Software Development Life Cycle). Let’s replicate some examples and check how this works.
Requirements
If you want to follow along with the examples, you need to install the sample database
WideWorldImporters. You can download it on this link https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0After downloading and restoring the database, you need to create a new index on
Sales.Orders using the code below:|
1 2 3 4 5 6 |
USE [WideWorldImporters] GO CREATE NONCLUSTERED INDEX [indPurchaseOrder] ON [Sales].[Orders] ( [customerpurchaseordernumber] ASC ) GO |
Prepare the Extended Events Session
Create the Extended Event Session using the following code. (The Extended Event output will be solely to the ring buffer, which lets you view the data easily in SSMS and is good for quick viewing of extended session output.)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE master; GO IF EXISTS (SELECT * FROM sys.dm_xe_sessions WHERE NAME = 'query_antipattern_xe') BEGIN DROP event session [query_antipattern_xe] ON server; END GO CREATE EVENT SESSION [query_antipattern_xe] ON SERVER ADD EVENT sqlserver.query_antipattern ( ACTION(sqlserver.client_app_name,sqlserver.plan_handle, sqlserver.query_hash,sqlserver.query_plan_hash, sqlserver.sql_text) ) ADD TARGET package0.ring_buffer(SET max_memory=(500)) GO |
Start the Extended Event Session using the following code:
|
1 2 3 |
ALTER EVENT SESSION query_antipattern_xe ON SERVER STATE = START; GO |
Then verify it is started in management studio:
Right-click the session and select the Watch Live Data menu item. This will let you see as new events occur.
Antipattern #1 – Implicit conversions
One of the biggest silent performance killers in T-SQL code is certain implicit conversions where the comparison cannot be performed in a lossless manner. For an example, in a new query window, execute the script to cause the first anti-pattern:
|
1 2 3 4 5 6 |
USE WideWorldImporters GO SELECT * FROM Sales.Orders WHERE CustomerPurchaseOrderNumber=10014; |
Check the Live Data window to see the anti-pattern capture
You can see in the
antipattern_type row that this is a TypeConvertPreventingSeekevent. SQL Server is capable of automatically converting data types in a query. The field type is NVARCHAR and the query parameter is INT. Some automatic query conversions prevent the use of index seek, typically where the conversion may be lossless. 
Because of how conversions work, if you look a the query plan for this query, you will see that the
CustomerPurchaseOrderNumber column is converted to an integer for the comparison. Each row has to be converted, so the index cannot be used. Relying on automatic data type conversions in predicates is considered an anti-pattern. If any value in the column was not able to be converted to an INT value, the query would have failed.While it is always a good practice to match your parameter or search criteria to the datatype of your column, this anti-pattern is only captured when it actually prevents the Index Seek. For example, if you changed the predicate to
WHERE CustomerPurchaseOrderNumber=CAST('10014' as char(10)); you will still see the implicit conversion, but since the ASCII value can be losslessly changed to a UNICODE value, the index can be used, and no anti-pattern event is raised.Antipattern #2 – Large IN expressions
To demonstrate this antipattern, I will use the following query that has 150 items in the IN expression of the WHERE clause.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT * FROM Sales.Orders WHERE CustomerPurchaseOrderNumber IN ( 10001,10002,10003,10004,10005, 10006,10007,10008,10009,10010,10011,10012,10013,10014,10015,10016,10017, 10018,10019,10020,10021,10022,10023,10024,10025,10026,10027,10028,10029, 10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,10041, 10042,10043,10044,10045,10046,10047,10048,10049,10050,10051,10052,10053, 10054,10055,10056,10057,10058,10059,10060,10061,10062,10063,10064,10065, 10066,10067,10068,10069,10070,10071,10072,10073,10074,10075,10076,10077, 10078,10079,10080,10081,10082,10083,10084,10085,10086,10087,10088,10089, 10090,10091,10092,10093,10094,10095,10096,10097,10098,10099,10100,10101, 10102,10103,10104,10105,10106,10107,10108,10109,10110,10111,10112,10113, 10114,10115,10116,10117,10118,10119,10120,10121,10122,10123,10124,10125, 10126,10127,10128,10129,10130,10131,10132,10133,10134,10135,10136,10137, 10138,10139,10140,10141,10142,10143,10144,10145,10146,10147,10148,10149, 10150); |
Note: this query was created using by taking the following query code:
|
1 2 3 |
SELECT * FROM Sales.Orders WHERE CustomerPurchaseOrderNumber IN ( ); |
And generating the IN expression using
|
1 2 3 |
SELECT STRING_AGG( CustomerPurchaseOrderNumber,',') FROM ( SELECT DISTINCT TOP 150 CustomerPurchaseOrderNumber FROM Sales.Orders) Orders; |
Just paste the output into the parenthesis. Next, select and execute the query. Check the Watch Live Data window and you should see some new activity.
The Anti-Pattern identified is the
LargeNumberOfOrInPredicate. The IN predicate in fact is translated as a series of OR logical conditions which cannot be optimized, resulting in this anti-pattern. Part of why this is noted as an anti-pattern is that the TypeConvertPreventingSeekevent occurs as well because we are again comparing NVARCHAR to INT types. If you change the
IN expression to use a character type (either Unicode or ASCII), you will not get the warning because just like with the previous example, if it has no direct negative on the optimization of the query, it will not raise the event. You can generate the IN expression as Unicode values using the following query:|
1 2 3 |
SELECT STRING_AGG( 'N''' + CustomerPurchaseOrderNumber + '''',',') FROM ( SELECT DISTINCT TOP 150 CustomerPurchaseOrderNumber FROM Sales.Orders) Orders; |
Identifying Additional Anti-Patterns
When looking at the output from the Extended Event, the anti-pattern is identified on the
antipattern_type field. We can get the possible values of the antipattern_type field from the extended events system tables and in this way identify what are the possible anti-patterns this event can track.Execute the query below:
|
1 2 3 |
SELECT map_value FROM sys.dm_xe_map_values WHERE name = N'query_antipattern_type'; |
This returns:
Let’s analyze the result of the query for what the other types of anti-patters might be::
- TypeConvertPreventingSeek and LargeNumberOfOrInPredicate: These are the two anti-patterns we tested
- LargeIn: The name explains a lot, but when executing a query with a lot of values in an
INexpression, the anti-pattern identified is the LargeNumberOfOrInPredicate. It’s not clear what makes theLargeInto be identified. - Max and NonOptimalOrLogic : There is not enough documentation about these two.
Azure SQL Databases
This extended event is also available in Azure SQL Databases. You can read more about how to capture this event on this link https://www.red-gate.com/simple-talk/blogs/azure-sql-extended-events-and-the-use-of-slash/
Conclusion
This extended event has a considerable potential and we should enable it in SQL Servers 2022. However, the anti-patterns identified are still limited and lacking documentation, we should follow the evolution of this event on future SQL Server cumulative updates.
One additional thing to note. this can be a very noisy event, especially if you have these events occuring frequently in your application for one, but simple queries of the system views may also cause events to fire. For example, Aaron Bertrand notes in his T-SQL Tuesday Blog from Septemever 2022 that
SELECT * FROM sys.database_principals; will cause an implicit conversion (Query_Antipattern_Type: TypeConvertPreventingSeek) event to occur. Aaron provides some advice on how to set up your events that can help mitigate the noisiness.
Load comments