Av rating:
Total votes: 61
Total comments: 13

 send to a friend
 printer friendly version

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.