Watch out for performance problems due to antipattern queries in SQL

[Guest bobward]

Both SQL Server and Azure SQL contain the query_antipattern extended event. The description for the event (from sys.dm_xe_objects) states that it “occurs when a query antipattern is present and can potentially affect performance.” You might be thinking – what is an antipattern? A commonly accepted academic definition is “…a commonly-used process, structure or pattern of action, despite initially appearing to be an appropriate and effective response to a problem, has more bad consequences than good ones.”[1] As it pertains to querying a SQL Server table, one can simply define an antipattern as a query that is written or designed in a way that prevents it from executing to its expected performance. Queries that contain one or more antipatterns can lead to severe, negative performance consequences by unnecessarily driving resource consumption higher.

 

 

 

If a query uses certain antipatterns, it will be detected during query optimization. For both SQL Server and Azure SQL (internally on by default), if these antipatterns are detected when optimizing the query, and the query_antipattern event has been added as part of a running extended event session, the output will be captured. The output will contain the relevant capture fields configured for the extended event session, allowing one to quickly identify which queries contain these antipatterns and are, therefore, prime candidates for tuning.

 

 

 

The following are the antipatterns that can be captured during query optimization:

 

 

 

• Column data type conversion preventing an index seek (implicit type conversion)*
  
• Non-optimal/unfriendly OR
  
• Large IN clause
  
• Large number of OR predicates
  

 

 

 

* This type of antipattern query may also trigger a warning in the query plan

 

 

 

There are other antipattern query scenarios that you should be aware of as you design your application such as:

 

 

 

• Non-parameterized/ad-hoc workloads
  
• Improper transaction management
  
• Non-SARGable predicates
  

 

 

 

Sources:

 

[1] Neill, Colin J.; Laplante, Philip A.; DeFranco, Joanna F. (2011). Antipatterns: Managing Software Organizations and People. Applied Software Engineering Series (second ed.). CRC Press. ISBN 9781439862162.

 

 

 

Authors: Riley Logan and Bob Ward, Microsoft

 

Continue reading...