Blog Post

Azure SQL Blog
2 MIN READ

Watch out for performance problems due to antipattern queries in SQL

bobward's avatar
bobward
Icon for Microsoft rankMicrosoft
Feb 03, 2023

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

** These antipattern queries are detected by the query_antipattern Extended Event

 

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

Updated Feb 14, 2023
Version 2.0
azure sql
azure sql database
sql server
  • jchang6's avatar
    jchang6
    Copper Contributor
    Feb 03, 2023

    There is a type of query that happens in Relativity eDiscovery. There is a table of say10M records. The query is to find matches in a subset (A) of 100K records but not in a subset (B) of 1M records. What is the probability that the 1% (100K out of 10M) of A is not in 10% (1M of 10M) in B. There is no general rule that will help estimate the net of in A but not in B. You could say B represent 10% of All so exclude 10% of A, or you could argue only a small percentage of A meets the criteria of not in B. Either could happen. Says this the part of the query that contributes to filtering, followed by additional joins. 

    I would hope that the resolution is to detect this, evaluate this part of the plan, then re-optimize for the remainder of the query, even if means rexecuting the first part of the query.

  • m60freeman's avatar
    m60freeman
    Brass Contributor
    Feb 16, 2023

    Example script to set up the EE Session:

    CREATE EVENT SESSION FindAntipatterns ON DATABASE 
ADD EVENT sqlserver.query_antipattern (
    ACTION ( sqlserver.client_app_name, sqlserver.sql_text, sqlserver.query_hash
    )
    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 sqlserver.client_app_name <> N'DacFx Deploy'
    )
    ADD TARGET package0.ring_buffer (
        SET max_memory = 1024   -- Units of KB.
    )
    WITH (  EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
            TRACK_CAUSALITY = OFF,
            STARTUP_STATE   = ON
);

ALTER EVENT SESSION FindAntipatterns ON DATABASE STATE = START;

/*
ALTER EVENT SESSION FindAntipatterns ON DATABASE STATE = STOP;
DROP EVENT SESSION FindAntipatterns ON DATABASE;
*/