T SQL Antipatterns - SQL Antipattern #010

11 February,2017 by Jack Vamvas

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)

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on T SQL Antipatterns - SQL Antipattern #010


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer