I'm looking forward to the next major version of SQL Server, and Glenn Berry has asked us to blog about it for this month's T-SQL Tuesday. Lots of the enhancements and new features have been publicized well:

• What's New (Official Documentation)
• My Favorite T-SQL Enhancements
• Itzik Ben-Gan on Windowing functions and DISTINCT FROM / approximate percentile

Some others are more obscure, like new system objects, minor changes to setup, and some new info in showplan.

A couple of features that are very promising for us here at work are contained availability groups (so we can stop spending so much time and effort syncing logins and jobs between replicas) and Link for Managed Instance (so we can test Managed Instance with the ability to fail back to SQL Server 2022 if we need to).

Others are less exciting to me personally, like the (IMHO unnecessary) enhancements to [L|R]TRIM, and ledger tables – which, on first glance, look to me like someone asked, "how can we get in on some SEO from blockchain?"

Anti-Patterns

But one thing that caught my eye in all the ruckus is a new Extended Event called query_antipattern, which is a lot more up my personal alley. You may have come across my Bad Habits series, or might have seen me rant about some of these in person. I think this is a promising compromise between manually parsing every single query and just not realizing how many little things are collectively bringing SQL Server to its knees.

This event is not yet documented, but it seems like it could be some combination of runtime code analysis and execution plan inspection. Right now, all we have to go on is a set of cryptic values in sys.dm_xe_map_values:

SELECT map_value
  FROM sys.dm_xe_map_values
  WHERE name = N'query_antipattern_type';

map_value
-----------------------------------------
LargeIn
LargeNumberOfOrInPredicate
Max
NonOptimalOrLogic
TypeConvertPreventingSeek

We can make educated guesses about some of these – the first two are too many values in an IN () clause and too many OR conditions, respectively, but I'm less sure about what Max refers to, or how "NonOptimalOrLogic" might be defined (I can think of several factors). The last one is the most self-documenting among them and recognizes when an explicit or implicit type conversion causes a scan when a seek would have been possible.

Naturally, I have tried setting up this event and capturing these anti-patterns in every build of SQL Server 2022 I've installed so far:

CREATE EVENT SESSION [Find bad habits] ON SERVER 
  ADD EVENT sqlserver.query_antipattern
  (
    ACTION ( sqlserver.client_app_name, sqlserver.sql_text )
  )
  WITH
  (
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE   = ON
  );

But I have yet to find the secret handshake for most other than for TypeConvertPreventingSeek. When I first started trying some massive IN () clauses, I saw this come across in the session:

SELECT u.name AS [Name] 
  FROM sys.database_principals AS u 
  WHERE (u.type in ('U', 'S', 'G', 'C', 'K' ,'E', 'X'))

Which actually wasn't the IN () query I wrote, it was something from SSMS. That type of thing happens a lot, even when I'm not doing it on purpose. It's not SSMS' fault, and not something the tools team can fix. I thought initially the issue was the missing N prefix on those type literals, but some of these warnings actually come from underlying places like the very definition of sys.database_principals. Even this simple query:

SELECT name, type FROM sys.database_principals;

Yields this warning in the execution plan:

And is in fact captured via query_antipattern:

So for these reasons, I strongly recommend a filter, like this one:

CREATE EVENT SESSION [Find bad habits] ON SERVER 
  ADD EVENT sqlserver.query_antipattern
  (
    ACTION ( sqlserver.client_app_name, sqlserver.sql_text )
    WHERE  ( sqlserver.client_app_name <> N'Microsoft SQL Server Management Studio' )
  )
  WITH
  (
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE   = ON
  );

And maybe to also exclude IntelliSense noise:

WHERE  
( 
  sqlserver.client_app_name <> N'Microsoft SQL Server Management Studio'
  AND 
  sqlserver.client_app_name <> N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
)

And if you want to ignore the type conversion warnings altogether, you can filter the various anti-pattern types by name:

WHERE  
( 
  sqlserver.client_app_name <> N'Microsoft SQL Server Management Studio'
  AND 
  sqlserver.client_app_name <> N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
 
  AND antipattern_type <> N'TypeConvertPreventingSeek'
  ----^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)

Just be aware that the entire predicate is limited to 4,000 characters.

But like I said, unless you know the magic sauce, a session that only expects the other four anti-pattern types is going to be pretty quiet on current builds.

Hopefully forthcoming builds will make it easier to capture these events, and more anti-pattern types will be added over time. To me this addition alone is an exciting glimpse into a bright future, where it doesn't matter if you write your queries or they are constructed with dynamic SQL or by some ORM or other tool – you can watch for problematic queries regardless of the source, often identifying them before they become a problem, and without babysitting Query Store or monitoring tools which typically only highlight the ones that have already become a problem.

As for more anti-patterns, I have a whole list of things to suggest if they've run out of ideas – how about LEFT JOIN + WHERE that effectively makes it an INNER JOIN? TOP without ORDER BY? WHERE CONVERT(col, 112) = CONVERT(GETDATE(), 112)? Missing statement terminator, schema prefix, or alias references? This could be someone's full-time job. And after that, what else could be done? Feedback into the optimizer to generate additional plans, like with Parameter Sensitive Plan optimization or like Query Store hints but more advanced and more automatic? Bring it on! I am starting to see a world where we can tell SQL Server:

Here are the bad habits I don't like; when you see them, just fix them!

Anyway, plenty more SQL Server 2022 resources here, and don't forget to watch for all the other contributions to T-SQL Tuesday!