Best SQL Server Performance Tuning Tips

Application Design (VB/ASP)

Like database design, performance tuning of applications should begin during the application design stage, otherwise you may have to recode your application after it is put into production to fix performance problems. Most of the most serious performance problems I have run across have been related to poor application design and coding. It seems as if many developers just don’t know how to make the best use out of SQL Server. The following tips are geared mostly toward VB and ASP developers.

• Don’t use DAO to access SQL Server, it is performance suicide. Also avoid ODBCDirect. Use ADO or RDO.
• While ADO (and other VB object libraries) make database manipulation easy for the programmer, using these shortcuts can kill SQL Server performance. As a rule of thumb, encapsulate your DML (Data Manipulation Language) in stored procedures and run them from your VB application. This bypasses object library overhead (such as reducing cursors) and reduces the chatter between the VB application and SQL Server over the network. More

• When using an ADO recordset to return data from SQL Server, the most efficient way is to use what is often called a firehose cursor. More

• When accessing data on a SQL Server, write your VB code so as to minimize the number of round-trips between the application and SQL Server. Each and every time you use ADO to execute Transact-SQL code to get data from SQL Server, multiple, time-consuming steps have to occur. More

• When creating a connection using ADO, be sure you use the OLE DB provider, not the older ODBC provider for SQL Server, or the ODBC provider for OLE DB. More

• When SELECTing data from your application to be returned to it from SQL Server, limit the amount of rows returned to only those that are needed now. If necessary, force the user to enter selection criteria to limit the results set. More

• When calling SQL Server stored procedures from the ADO Command object, don’t use the Refresh method to identify the parameters of a stored procedure. This produces extra network traffic and slows performance. Instead, explicitly create the parameters yourself using ADO code. More

• ADO allows you to create four different types of SQL Server cursors. Each has its own place, and you will want to choose the cursor that uses the least possible resources for the task at hand. More

• When looping through recordsets, be sure you bind columns to field objects before the looping begins. Don’t use the Fields collection of the Recordset object to assign values for fields in a Recordset within each loop, it incurs much more overhead. More

• When making your connection to SQL Server, choose DSN-less connections for the fastest connection. More

• If you want to take the best advantage of database connection pooling, and optimize your VB application’s SQL Server data access, the best advice you can receive is to be sure that you only open a database connection just before you need it, and then close it immediately after you are done with it. Don’t leave database connections open if you are not using them. More

• Don’t instantiate, initialize, use, and then destroy an object within a loop. If the loop repeats itself much at all, you create an inordinate amount of overhead for your application. Instead, reuse the same object in the loop. One of the best ways to do this is to include a reinitialize method for the object that can be called from within the loop. More

Indexing

Without proper indexes, SQL Server performance can be terrible. And not just any kind of index will work, it has to be the right index for the right query. Badly selected indexes can actually slow down SQL Server’s performance. The following tips focus on critical knowledge that every DBA should know.

• Indexes should be considered on all columns that are frequently accessed by the WHERE or the ORDER BY clauses. But don’t automatically add indexes on a table because it seems like the right thing to do. Too many indexes can be as bad as having too few indexes. More

• Don’t accidentally add the same index twice on a table. More

• Drop indexes that are never used by the Query Optimizer. More

• To provide the up-to-date statistics the query optimizer needs to make smart query optimization decisions, you will generally want to leave the “Auto Update Statistics” database option on. More

• Keep the “width” of your indexes as narrow as possible, especially when creating composite (multi-column) indexes. More

• An index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. More

• When you create an index with a composite key, the order of the columns in the key is important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key. More

• If you have two or more tables that are frequently joined together, then the columns used for the joins should have an appropriate index. More

• The Query Optimizer will always perform a table scan or a clustered index scan on a table if the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed (or does not have a useful index). Because of this, if you use many queries with OR clauses, you will want to ensure that each referenced column has an index. More

• Since you can only create one clustered index per table, take extra time to carefully consider how it will be used. Consider the type of queries that will be used against the table, and make an educated guess as to which query (the most common one run against the table, perhaps) is the most critical, and if this query will benefit from having a clustered index. More

• If a column in a table is not at least 95% unique, then most likely the query optimizer will not use a non-clustered index based on that column. Because of this, don’t add non-clustered indexes to columns that aren’t at least 95% unique. More

• If you have to use a non-clustered index, and if you know that your application will be performing the same query over and over on the same table, consider creating a covering index on the table. More

• Periodically (weekly or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance. More