MosherData

Boost SQL Server Performance: Identify and Fix Anti-Patterns in Your Query Cache

SQL Server queries aren’t all created equal, and some of them are straight up bad. They might be designed without necessary indexes or in a non-SARGable way. But this post will focus on anti-patterns, which are common practices that lead to crappy performance and even incorrect results.

First, I’ll outline some of the common anti-patterns and then provide a query to check your plan cache for potentially problematic queries.

Common SQL Server Anti-Patterns

  1. SELECT * Queries: Because who needs efficiency when you can retrieve everything?
  2. Improper Indexing: Slow queries are just a sign of character, right?
  3. Overusing Cursors: Why be fast when you can be resource-intensive?
  4. JOIN Explosion: More rows than expected? Surprise!
  5. Mixing UI Logic with Data Access Logic: Giving your data a stylish new look, right in the query.
  6. Hardcoding Values: Flexibility is overrated.
  7. Ignoring Normalization: Turning your database into a wild west of data.
  8. Overusing Triggers: Adding a little mystery to your data operations.

Query to Identify Anti-Patterns

To help you identify some of these anti-patterns in the query cache, you can use the following query:

Example of query results:

-- Query to identify common anti-patterns from queries in cache
WITH QueryStats AS (
    SELECT
        qs.sql_handle,
        qs.plan_handle,
        qs.execution_count,
        qs.total_logical_reads,
        qs.total_logical_writes,
        qs.total_worker_time,
        qs.total_elapsed_time,
        qs.creation_time,
        qs.last_execution_time,
        qs.statement_start_offset,
        qs.statement_end_offset,
        SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
            ((CASE qs.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.text)
                ELSE qs.statement_end_offset
            END - qs.statement_start_offset)/2) + 1) AS query_text
    FROM
        sys.dm_exec_query_stats AS qs
    CROSS APPLY
        sys.dm_exec_sql_text(qs.sql_handle) AS st
)
SELECT
    CASE
        WHEN qs.query_text LIKE '%SELECT *%' THEN 'SELECT * Query'
        WHEN qs.query_text LIKE '%CURSOR%' THEN 'Overusing Cursors'
        WHEN qs.query_text LIKE '%JOIN%' AND qs.query_text LIKE '%ON%' THEN 'JOIN Explosion'
        WHEN qs.query_text LIKE '%HARDCODED_VALUE%' THEN 'Hardcoding Values'
        WHEN qs.query_text LIKE '%DISTINCT%' THEN 'Overusing DISTINCT'
        WHEN qs.query_text LIKE '%UNION%' AND qs.query_text NOT LIKE '%UNION ALL%' THEN 'Overusing UNION'
        WHEN qs.query_text LIKE '%SUBQUERY%' THEN 'Overusing Subqueries'
        WHEN qs.query_text LIKE '%TEMPORARY TABLE%' THEN 'Overusing Temporary Tables'
        WHEN qs.query_text LIKE '%VIEW%' THEN 'Overusing Views'
        WHEN qs.query_text LIKE '%FUNCTION%' THEN 'Overusing Functions'
        WHEN qs.query_text LIKE '%DYNAMIC SQL%' THEN 'Overusing Dynamic SQL'
        WHEN qs.query_text LIKE '%HINT%' THEN 'Overusing Hints'
        WHEN qs.query_text LIKE '%TRANSACTION%' THEN 'Overusing Transactions'
        WHEN qs.query_text LIKE '%AGGREGATE%' THEN 'Overusing Aggregates'
        WHEN qs.query_text LIKE '%SELF JOIN%' THEN 'Overusing Self-Joins'
        WHEN qs.query_text LIKE '%NESTED LOOP%' THEN 'Overusing Nested Loops'
        WHEN qs.query_text LIKE '%CROSS JOIN%' THEN 'Overusing Cross Joins'
        WHEN qs.query_text LIKE '%OUTER JOIN%' THEN 'Overusing Outer Joins'
        WHEN qs.query_text LIKE '%CASE%' THEN 'Overusing Case Statements'
        WHEN qs.query_text LIKE '%STRING FUNCTION%' THEN 'Overusing String Functions'
        WHEN qs.query_text LIKE '%DATE FUNCTION%' THEN 'Overusing Date Functions'
        WHEN qs.query_text LIKE '%MATH FUNCTION%' THEN 'Overusing Mathematical Functions'
        WHEN qs.query_text LIKE '%SYSTEM FUNCTION%' THEN 'Overusing System Functions'
        ELSE 'Other'
    END AS anti_pattern,
    qs.query_text,
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.total_worker_time,
    qs.total_elapsed_time,
    qs.creation_time,
    qs.last_execution_time,
    qs.sql_handle,
    qs.plan_handle
FROM
    QueryStats AS qs
WHERE
    qs.query_text LIKE '%SELECT *%'
    OR qs.query_text LIKE '%CURSOR%'
    OR qs.query_text LIKE '%JOIN%'
    OR qs.query_text LIKE '%HARDCODED_VALUE%'
    OR qs.query_text LIKE '%DISTINCT%'
    OR qs.query_text LIKE '%UNION%'
    OR qs.query_text LIKE '%SUBQUERY%'
    OR qs.query_text LIKE '%TEMPORARY TABLE%'
    OR qs.query_text LIKE '%VIEW%'
    OR qs.query_text LIKE '%FUNCTION%'
    OR qs.query_text LIKE '%DYNAMIC SQL%'
    OR qs.query_text LIKE '%HINT%'
    OR qs.query_text LIKE '%TRANSACTION%'
    OR qs.query_text LIKE '%AGGREGATE%'
    OR qs.query_text LIKE '%SELF JOIN%'
    OR qs.query_text LIKE '%NESTED LOOP%'
    OR qs.query_text LIKE '%CROSS JOIN%'
    OR qs.query_text LIKE '%OUTER JOIN%'
    OR qs.query_text LIKE '%CASE%'
    OR qs.query_text LIKE '%STRING FUNCTION%'
    OR qs.query_text LIKE '%DATE FUNCTION%'
    OR qs.query_text LIKE '%MATH FUNCTION%'
    OR qs.query_text LIKE '%SYSTEM FUNCTION%'
ORDER BY
   
	qs.execution_count DESC,
    qs.total_worker_time DESC,
    qs.total_logical_reads DESC;

How to Use the Query

  1. Run the Query: Fire up SQL Server Management Studio (SSMS) and execute the query to pull the list of queries from the cache along with the identified anti-patterns.
  2. Review the Results: Check out the results to see the queries and their corresponding anti-patterns. The queries are sorted by total worker time, total logical reads, and execution count to highlight the most impactful ones.
  3. Address the Anti-Patterns: Go through the identified anti-patterns and make the necessary tweaks to optimize the queries. For instance, replace SELECT * with specific column names, avoid using cursors, and ensure proper indexing.

By using this query, you can quickly spot potential anti-patterns in the query cache and take steps to fix them, and possibly improve the performance.

Warning: Maybe don’t run random queries off the internet on your production machines and then attempt to optimize everything. Avoid making changes to queries you don’t fully understand. 

Leave a comment