Return to Previous Article in the Series
Performance Audit Checklist
| Transact-SQL Checklist | Your Response |
| Does the Transact-SQL code return more data than needed? | |
| Are cursors being used when they don’t need to be? | |
| Are UNION and UNION SELECT properly used? | |
| Is SELECT DISTINCT being used properly? | |
| Is the WHERE clause sargable? | |
| Are temp tables being used when they don’t need to be? | |
| Are hints being properly used in queries? | |
| Are views unnecessarily being used? | |
| Are stored procedures being used whenever possible? | |
| Inside stored procedures, is SET NOCOUNT ON being used? | |
| Do any of your stored procedures start with sp_? | |
| Are all stored procedures owned by DBO, and referred to in the form of databaseowner.objectname? | |
| Are you using constraints or triggers for referential integrity? | |
| Are transactions being kept as short as possible? | |
| Application Checklist | |
| Is the application using stored procedures, strings of Transact-SQL code, or using an object model, like ADO, to communicate with SQL Server? | |
| What method is the application using to communicate with SQL Server: DB-LIB, DAO, RDO, ADO, .NET? | |
| Is the application using ODBC or OLE DB to communication with SQL Server? | |
| Is the application taking advantage of connection pooling? | |
| Is the application properly opening, reusing, and closing connections? | |
| Is the Transact-SQL code being sent to SQL Server optimized for SQL Server, or is it generic SQL? | |
| Does the application return more data from SQL Server than it needs? | |
| Does the application keep transactions open when the user is modifying data? |
Enter your results in the table above.
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.
- 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 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.