Query to find non – sargable sql statements – SQL Antipattern #009

When a predicate is SARGABLE we mean the sql statement is able to exploit the indexes supporting the sql statement.   If the sql statemet is non – SARGABLE – we mean the statement is not capable of exploiting the indexes

If a statement is non-SARGABLE it can return the correct data set – as intended by the developer – but the response rate can be slower than expected. As the statement cannot exploit the index – it’s forced into a full scan.     For the smaller data sets this may not appear to be a problem, but as the data sets get bigger , the impact may be severe.    In extreme cases , application timeouts occur .

There are many reasons why non SARGABLE statements creep into the code base.

a) Poor database design   may force a developer into all sorts of workarounds.

b) Lack of knowledge

c) Lack of quality control

are some of the reasons.

A statement such as the following will be non-sargable. The index will need to evaluate the Function for every row. if the index exists – it cannot exploit it

SELECT col1, col2 FROM myTable WHERE Function(Co) = ‘hdhdh’

You can apply different tactics to identify code following these patterns. This is an example of sql code reporting stored procedures which have LEFT or UPPER function. You can customise the code to your circumstances.

use mydatabase
GO

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
ON m.object_id=o.object_id
WHERE m.definition LIKE '%UPPER(%'
OR m.definition LIKE '%LEFT(%'
AND o.type='P'

Producing a list of stored procedures may take a long time to work through. Normally, I’ll focus on the stored procedures with the highest count. This could be something like taking the query above and checking to see if the stored procedures is in the TOP count of stored procedures.

use  myDatabase
GO
SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
ON m.object_id=o.object_id
WHERE m.definition LIKE '%UPPER(%'
OR m.definition LIKE '%LEFT(%'
AND o.type='P'
AND o.name IN ( 

SELECT
   TOP 30 OBJECT_NAME(qt.objectid)
  FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    qt.[dbid] = DB_ID()
ORDER BY
    qs.execution_count DESC
)

Query to find non – sargable  sql statements is part of the  SQL Antipattern series. 

Read More

 Monitor Performance

What is First Normal Form, Second Normal and Third Normal Form ...