Friday, April 6, 2012

TIPS - Increase SQL Server stored procedure performance

a) Define / Use of variables within stored procedure

b) Use of uncommon values in parameter values on the first execution after a stored proc is created/recompiled, resulting in a bad query plan chosen by the optimizer.

c) Join order. What's an inner and outer table. What is nested join and/or merge join. And understand how the optimizer chooses the join order.

d) Lack of statistics on a non-leading column in a composite index resulting in optimiser choosing a table scan as opposed to an index scan.

e) Use of like with %%. The optimizer will chose an index if you can afford a like with only a trailing %. i.e. like 'Wa%' is much better than '%Wa%'

f) Creation of tables within the same stored proc and using it and the optimizer has no way to find best access method for the table.

g) Joins vs. sub queries. Usually updates/deletes benefit from sub queries.

h) Avoid self joins if possible.

i) Use "NOT NULL" where possible in table definition. It will help the optimizer.

j) Use fixed length instead of variable length for small length fields. Read about how DOL/All pages change data

No comments:

Post a Comment