SQL Server 2022: Capture SQL Anti-Patterns

 

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:

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

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:

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.

 

 

 

Anitpattern #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:

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.

 

Anitpattern #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.

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:

SELECT * 
FROM   Sales.Orders 
WHERE  CustomerPurchaseOrderNumber IN ( );

And generating the IN expression using 

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:

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:

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

 

 

The post SQL Server 2022: Capture SQL Anti-Patterns appeared first on Simple Talk.

from Simple Talk https://ift.tt/X4uF8EA
via