04 July,2016 by Jack Vamvas
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 )