SQL Server 2022: Capture SQL Anti-Patterns

Comments 0

Share to social media

 
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.0
 
After downloading and restoring the database,  you need to create a new index on Sales.Orders using the code below:

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.)

Start the Extended Event Session using the following code:

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:

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.

Note: this query was created using by taking the following query code:

And generating the IN expression using 

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:

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:

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 anIN expression, the anti-pattern identified is the LargeNumberOfOrInPredicate. It’s not clear what makes the LargeIn to 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.
 
 

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com

Dennes's contributions