{"id":96796,"date":"2023-06-01T17:00:27","date_gmt":"2023-06-01T17:00:27","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96796"},"modified":"2024-06-05T10:08:11","modified_gmt":"2024-06-05T10:08:11","slug":"sql-server-2022-capture-sql-anti-patterns","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/sql-server-2022-capture-sql-anti-patterns\/","title":{"rendered":"SQL Server 2022: Capture SQL Anti-Patterns"},"content":{"rendered":"
\u00a0<\/div>\n
One of the new Extended Event<\/strong> available in SQL Server 2022<\/strong> is the query_antipattern<\/strong>. This extended event allows to identify anti-patterns on the SQL queries sent to the server.\u00a0 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).<\/div>\n
\u00a0<\/div>\n
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<\/strong> (Software Development Life Cycle).\u00a0 Let’s replicate some examples and check how this works.<\/div>\n
\u00a0<\/div>\n

Requirements<\/h2>\n
If you want to follow along with the examples, you need to install the sample database WideWorldImporters<\/code>. You can download it on this link https:\/\/github.com\/Microsoft\/sql-server-samples\/releases\/tag\/wide-world-importers-v1.0<\/a><\/div>\n
\u00a0<\/div>\n
After downloading and restoring the database,\u00a0 you need to create a new index on Sales.Orders<\/code> using the code below:<\/div>\n
USE\u00a0[WideWorldImporters]\r\nGO\r\n\r\nCREATE\u00a0NONCLUSTERED\u00a0INDEX\u00a0[indPurchaseOrder]\r\n\u00a0\u00a0ON\u00a0[Sales].[Orders]\u00a0(\u00a0[customerpurchaseordernumber]\u00a0ASC\u00a0)\r\nGO<\/pre>\n
Prepare the Extended Events Session<\/h2>\n
Create the Extended Event Session<\/strong> using the following code. (The Extended Event output will be solely to the ring buffer<\/a>, which lets you view the data easily in SSMS and is good for quick viewing of extended session output.)<\/div>\n
USE\u00a0master;\r\nGO \r\n\r\nIF\u00a0EXISTS\u00a0(SELECT\u00a0* \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0sys.dm_xe_sessions \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE\u00a0\u00a0NAME\u00a0=\u00a0'query_antipattern_xe') \r\n \u00a0BEGIN \r\n \u00a0\u00a0\u00a0\u00a0\u00a0DROP\u00a0event\u00a0session\u00a0[query_antipattern_xe]\u00a0ON\u00a0server; \r\n \u00a0END\r\nGO \r\n\r\nCREATE EVENT SESSION [query_antipattern_xe] ON SERVER\u00a0 \r\n     ADD EVENT sqlserver.query_antipattern ( \u00a0 \r\n        ACTION(sqlserver.client_app_name,sqlserver.plan_handle, \r\n               sqlserver.query_hash,sqlserver.query_plan_hash,\r\n               sqlserver.sql_text)\r\n          ) ADD TARGET package0.ring_buffer(SET max_memory=(500)) \r\nGO<\/pre>\n
Start the Extended Event Session using the following code:<\/div>\n
ALTER EVENT\u00a0SESSION\u00a0query_antipattern_xe ON\u00a0SERVER\r\nSTATE\u00a0=\u00a0START;\r\nGO\r\n<\/pre>\n
Then verify it is started in management studio:<\/div>\n
\u00a0<\/div>\n
\"\"<\/div>\n
\u00a0<\/div>\n
Right-click the session and select the Watch Live Data<\/strong> menu item. This will let you see as new events occur.<\/div>\n
\u00a0<\/div>\n
\"\"<\/div>\n
\u00a0<\/div>\n
\"\"<\/div>\n
\u00a0<\/div>\n
Antipattern #1 – Implicit conversions<\/h2>\n
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.\u00a0 For an example, in a new query window, execute the script to cause the first anti-pattern:<\/div>\n
USE\u00a0WideWorldImporters\r\nGO\r\n\r\nSELECT *\u00a0 \r\nFROM\u00a0 \u00a0 Sales.Orders \r\nWHERE \u00a0CustomerPurchaseOrderNumber=10014;<\/pre>\n
Check the Live Data<\/strong> window to see the anti-pattern capture<\/div>\n
\u00a0<\/div>\n
\"\"<\/div>\n
\u00a0<\/div>\n
You can see in the antipattern_type<\/code> row that this is a TypeConvertPreventingSeek<\/code>event.\u00a0 SQL Server<\/strong> is capable of automatically converting data types in a query. The field type is NVARCHAR<\/strong><\/code> and the query parameter is INT<\/strong><\/code>. Some automatic query conversions prevent the use of index seek, typically where the conversion may be lossless.\u00a0<\/div>\n
\u00a0<\/div>\n
\"\"<\/div>\n
\u00a0<\/div>\n
Because of how conversions work, if you look a the query plan for this query, you will see that the CustomerPurchaseOrderNumber<\/code> 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.<\/div>\n
\u00a0<\/div>\n
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<\/strong>. For example, if you changed the predicate to WHERE CustomerPurchaseOrderNumber=CAST('10014' as char(10));<\/code> you will still see the implicit conversion, but since the ASCII<\/code> value can be losslessly changed to a UNICODE<\/code> value, the index can be used, and no anti-pattern event is raised.<\/div>\n
\u00a0<\/div>\n
Antipattern #2 – Large IN expressions<\/h2>\n
To demonstrate this antipattern, I will use the following query that has 150 items in the IN expression of the WHERE clause.<\/p>\n
SELECT * \r\nFROM\u00a0\u00a0\u00a0Sales.Orders \r\nWHERE  CustomerPurchaseOrderNumber IN ( 10001,10002,10003,10004,10005,\r\n10006,10007,10008,10009,10010,10011,10012,10013,10014,10015,10016,10017,\r\n10018,10019,10020,10021,10022,10023,10024,10025,10026,10027,10028,10029,\r\n10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,10041,\r\n10042,10043,10044,10045,10046,10047,10048,10049,10050,10051,10052,10053,\r\n10054,10055,10056,10057,10058,10059,10060,10061,10062,10063,10064,10065,\r\n10066,10067,10068,10069,10070,10071,10072,10073,10074,10075,10076,10077,\r\n10078,10079,10080,10081,10082,10083,10084,10085,10086,10087,10088,10089,\r\n10090,10091,10092,10093,10094,10095,10096,10097,10098,10099,10100,10101,\r\n10102,10103,10104,10105,10106,10107,10108,10109,10110,10111,10112,10113,\r\n10114,10115,10116,10117,10118,10119,10120,10121,10122,10123,10124,10125,\r\n10126,10127,10128,10129,10130,10131,10132,10133,10134,10135,10136,10137,\r\n10138,10139,10140,10141,10142,10143,10144,10145,10146,10147,10148,10149,\r\n10150);<\/pre>\n
Note: this query was created using by taking\u00a0the following query code:<\/div>\n
SELECT * \r\nFROM\u00a0\u00a0\u00a0Sales.Orders \r\nWHERE  CustomerPurchaseOrderNumber IN ( ); <\/pre>\n
And generating the IN<\/span><\/span>\u00a0expression using\u00a0<\/div>\n
SELECT STRING_AGG( CustomerPurchaseOrderNumber,',')\r\nFROM ( SELECT DISTINCT TOP 150 CustomerPurchaseOrderNumber\r\n       FROM Sales.Orders) Orders;<\/pre>\n
Just paste the output into the parenthesis. Next, select and execute the query.\u00a0Check the Watch Live Data<\/strong> window and you should see some new activity.<\/div>\n
\u00a0<\/div>\n
\"\"<\/div>\n
\u00a0<\/div>\n
The Anti-Pattern identified is the LargeNumberOfOrInPredicate<\/code>. <\/strong>The IN<\/code> predicate in fact is translated as a series of OR<\/code> logical conditions which cannot be optimized, resulting in this anti-pattern. Part of why this is noted as an anti-pattern is that the TypeConvertPreventingSeek<\/code>event occurs as well because we are again comparing NVARCHAR<\/code> to INT<\/code> types.\u00a0<\/div>\n
\u00a0<\/div>\n
If you change the IN<\/code> 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:<\/div>\n
SELECT STRING_AGG( 'N''' + CustomerPurchaseOrderNumber + '''',',')\r\nFROM ( SELECT DISTINCT TOP 150 CustomerPurchaseOrderNumber\r\n       FROM Sales.Orders) Orders;<\/pre>\n
Identifying Additional Anti-Patterns<\/h2>\n
When looking at the output from the Extended Event, the anti-pattern is identified on the antipattern_type<\/code> field. We can get the possible values of the antipattern_type<\/code> field from the extended events system tables and in this way identify what are the possible anti-patterns this event can track.<\/div>\n
\u00a0<\/div>\n
Execute the query below:<\/div>\n
SELECT\u00a0map_value \r\nFROM\u00a0sys.dm_xe_map_values \r\nWHERE\u00a0name\u00a0=\u00a0N'query_antipattern_type'; <\/pre>\n
This returns:<\/div>\n
\u00a0<\/div>\n
\"\"<\/div>\n
\u00a0<\/div>\n
Let’s analyze the result of the query for what the other types of anti-patters might be::<\/div>\n