T SQL Antipatterns - SQL Antipattern #010

During SQL Server performance tuning it is common to find patterns in t sql stored procedures and t sql programming which can lead to less than optimal performance.

These need to be considered in the context of the application . There may be legitimate reason for these t sql usages , but it’s worth tracking down and reviewing . I work with the developers \ programmers to suggest alternatives and workarounds

Some methods to use in tracking down t sql which cause problems

1. Set statistics IO ON - Troubleshoot high disk activity with STATISTICS IO (SQL Server DBA)
2. SQL Profiler . I normally set a 80 ms threshold, for filtering
3. Extended Events

T SQL Antipatterns

1) Fn_split . In creating estimates on Exceution Plans usually the estimates are wildly inaccurate

2)  Joining to Table Value Function or table variables . Use temp tab;les, using a temp table , statistics will be accurate Multi-statement table valued function and inefficient Excecution

3) Every reference to a CTE will create an execution

4) Dynamic SQL – make sure you apply parameters correctly

5) Non SARGABLE queries  - WHERE LEFT, UPPER, LIKE, YEAR, VARCHAR = @NVARCHAR  - classic examples  in a non-sargable queries. Beware of table scans Query to find non – sargable sql statements – SQL Antipattern #009

6) Nested Views

7) Correlated subquery

T SQL Antipatterns  part of the  SQL Antipattern series. 

Read More

SQL Server - Stored Procedure Checklist (SQL Server DBA)