Performance tuning tips for database developers

[alok pandey]

Performance tuning is not easy and there aren’t any silver bullets,
but you can go a surprisingly long way with a few basic guidelines.
In theory, performance tuning is done by a DBA. But in practice, the
DBA is not going to have time to scrutinize every change made to a
stored procedure. Learning to do basic tuning might save you from
reworking code late in the game.
Below is my list of the top 15 things I believe developers should do
as a matter of course to tune performance when coding. These are the
low hanging fruit of SQL Server performance – they are easy to do and
often have a substantial impact. Doing these won’t guarantee
lightening fast performance, but it won’t be slow either.
1. Create a primary key on each table you create and unless you are
really knowledgeable enough to figure out a better plan, make it the
clustered index (note that if you set the primary key in Enterprise
Manager it will cluster it by default).
2. Create an index on any column that is a foreign key. If you know it
will be unique, set the flag to force the index to be unique.
3. Don’t index anything else (yet).
4. Unless you need a different behaviour, always owner qualify your
objects when you reference them in TSQL. Use dbo.sysdatabases instead
of just sysdatabases.
5. Use set nocount on at the top of each stored procedure (and set
nocount off) at the bottom.
6. Think hard about locking. If you’re not writing banking software,
would it matter that you take a chance on a dirty read? You can use
the NOLOCK hint, but it’s often easier to use SET TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then
reset to READ COMMITTED at the bottom.
7. I know you’ve heard it a million times, but only return the columns
and the rows you need.
8. Use transactions when appropriate, but allow zero user interaction
while the transaction is in progress. I try to do all my transactions
inside a stored procedure.
9. Avoid temp tables as much as you can, but if you need a temp table,
create it explicitly using Create Table #temp.
10. Avoid NOT IN, instead use a left outer join - even though it’s
often easier to visualize the NOT IN.
11. If you insist on using dynamic sql (executing a concatenated
string), use named parameters and sp_executesql (rather than EXEC) so
you have a chance of reusing the query plan. While it’s simplistic to
say that stored procedures are always the right answer, it’s also
close enough that you won’t go wrong using them.
12. Get in the habit of profiling your code before and after each
change. While you should keep in mind the depth of the change, if you
see more than a 10-15% increase in CPU, Reads, or Writes it probably
needs to be reviewed.
13. Look for every possible way to reduce the number of round trips to
the server. Returning multiple resultsets is one way to do this.
14. Avoid index and join hints.
15. When you’re done coding, set Profiler to monitor statements from
your machine only, then run through the application from start to
finish once. Take a look at the number of reads and writes, and the
number of calls to the server. See anything that looks unusual? It’s
not uncommon to see calls to procedures that are no longer used, or to
see duplicate calls. Impress your DBA by asking him to review those
results with you.
If you take these 15 steps, you’ve made a really good first pass.
There’s more to learn next time as we build a model of how your
application, the network, and SQL Server all offer the potential for
bottlenecks. We will also look at the potential for improving
performance and some more steps that you can take without stepping too
far into the land of the DBA.