SQL Server Application and Transact-SQL Performance Checklist

Performance Audit Checklist

Application and Transact-SQL Code Greatly Affect SQL Server Performance

Of all the areas that can negatively affect the performance of SQL Server, the application code used to access SQL Server data, including Transact-SQL code, has the biggest potential of hurting performance. Unfortunately though, this is an area that a lot of DBAs don't directly control. And because of this, this area if often neglected when performance tuning SQL Server-based application.

As with previous articles in this series, the purpose of this part of the audit is to catch the "easy" performance-related issues of your application and Transact-SQL code that accesses SQL Server data. Besides the tips listed here, there are a lot more factors that affect SQL Server's performance, but the ones listed here are a good beginning.

Of course, if you are using third-party software, then this part of the performance audit doesn't affect you as you can't do much about the code. But if you have developed your own applications, or if the applications have been developed in-house, then you should be able to take part in this portion of the SQL Server performance audit.

As you review the audit items, and their discussion below, you will quickly discover that identifying some of these issues, or even fixing them, is no small task. Because of this, it is much better to build your applications with these performance tips in mind instead of having to fix them after the application has been written. You may want keep this article around when building new applications so that you build them for performance the first time around.

Transact-SQL Checklist

Does the Transact-SQL Code Return More Data Than Needed?

The less data returned by SQL Server, the less resources SQL Server needs to operate, helping to boost the overall performance of SQL Server. This may sound obvious, but returning unnecessary data is a performance problem that I see over and over.

Here are some of the most common mistakes made by coders when returning data from SQL Server that results in more data than necessary:

• The absence of a WHERE clause. Unless you want to return all data from a table, which is a rare activity, the use of a WHERE clause is necessary to reduce the number of rows returned.
  
   
• As an adjunct to the above advice, a WHERE clause needs to be a selective as possible. For example, if you only need to return records from a particular date, don't return all the records for the month, or year. Design the WHERE clause so that exactly only those rows you need returned are returned, and not one extra row.
  
   
• In the SELECT clause, only include those columns that you need, not all of them. Along the same line, don't use SELECT *, as you will most likely be returning more rows that you need.
  
   
• I will refer to this one again later on this page, but it also applies here. Don't perform SELECTs against views, instead, bypass the view and get the data you need directly from the table. The reason for this is that many views (of course, not all) return more data than is necessary for the calling SELECT statement, which just ends up returning much more data than necessary.

In case you are not aware of them, here are some of the performance issues caused by returning unnecessary data: Sometimes, returning too much data forces the Query Optimizer to perform a table scan instead of an index lookup; extra I/O is needed to read data; buffer cache space is wasted, which could be better used by SQL Server for other purposes; unnecessary network traffic occurs; on the client, additional data has to be stored in memory which might be better used for other uses; and so on.