SQL Server Best Practices for Tuning

Tuning is more of an art than a science and the best way to learn how is to do it. The following points are some recommended best practices for tuning your applications:

Tuning an application is an effort of multiple teams, including front-end programmers, database developers, network and database administrators, middle-tier developers and other professionals. Some improvements can also be accomplished by upgrading the server and client hardware. Ensure that your programs are the definite cause of the sub-standard performance before spending much time and effort optimizing code.

Determine what level of performance users consider optimal. Also try to identify the most and less critical queries.

Identify the bottleneck in your code. Only a single poorly written code module can ruin the performance of the entire application.

If possible, try to optimize your database design before modifying code. If the design is sub-optimal issues will keep cropping up. Build a data model appropriate for your application and use the most efficient data types available. If the same application is used for data-entry and reporting, try splitting it in two separate applications. Also consider partitioning your tables or using distributed partitioned views to reduce the number of rows affected by your queries.

Research the current indexing strategy and try to improve it. Try to place the clustered index on a column (or a combination of columns) that has a range of distinct values and is rarely updated. Avoid clustered indexes on monotonically increasing columns. Try to use non-clustered indexes to "cover" important queries. Try to use short index keys when possible, except when creating covering indexes.

If you have appropriate indexes but they don't seem to produce the expected results examine their fragmentation level. Also ensure that statistics stay up to date. Use the index tuning wizard and study the textual and graphical query execution plans to determine the cause of inefficient queries.

Learn how the optimizer works. Examine and test various join types and table join order; use optimizer hints to override the query optimizer's behavior if necessary. Move all database maintenance tasks to the periods of limited user activity.

Try to avoid sub-queries in your programs; solutions with joins usually outperform sub-queries. Use UPDATE… FROM and DELETE … FROM extensions of Transact-SQL to eliminate sub-queries when updating and deleting data using multiple tables.

Consider using UNION ALL instead of UNION when appropriate.

Evaluate the impact of triggers and constraints on your performance. At times your programs might be fully optimized but a poorly written trigger on one of the affected tables can bring the server to its knees. Constraints that cannot take advantage of helpful indexes might impact performance as well.

Try to avoid using SELECT … INTO unless you are the only user in the database or the operation takes a minimal amount of time. Consider replacing all references to SELECT … INTO with INSERT … SELECT.

Use SET NOCOUNT ON in all of your code modules to reduce the amount of information going from the server to clients and reduce the network load.

When using built-in or user-defined functions the optimizer cannot take advantage of the index if index key is being referenced in a function. Try rewriting your queries so that the index key is not affected.

When possible try to rewrite the queries using local variables as stored procedures with input parameters. The optimizer is more likely to choose the appropriate execution plan with parameters.

When appropriate use temporary tables to reduce the number of rows affected by the queries. If temporary tables are joined to other temporary or permanent tables consider building an index on the temporary table.

Optimize loops - move all tasks that do not need to be executed repeatedly outside of the loop.

Do not use cursors unless absolutely necessary. Transact-SQL is a set based language and is not optimized for processing one row at a time.

Be aware that updates in-place are more efficient than a DELETE followed by an INSERT. Try to design your database such that in-place updates are likely to occur.