SQL Optimization

1. Use views and stored procedures instead of heavy-duty queries.
Client sends only stored procedure or view name (with some parameters) instead of large heavy-duty queries text. You can also restrict user access to table columns they should not see.

2. Try to use constraints instead of triggers, whenever possible.
Use constraints instead of triggers, whenever possible.

3. Use table variables instead of temporary tables.
Table (available in SQL Server 2000) variables require less locking and logging resources than temporary tables.

4. Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows.

5. Try to avoid using the DISTINCT clause, whenever possible.
Results in performance degradation, you should use DISTINCT only when necessary.

6. Try to avoid using SQL Server cursors, whenever possible.
Use correlated sub-query or derived tables, if you need to perform row-by-row operations.

7. Try to avoid the HAVING clause, whenever possible.
Write your select statement so, that it contains only WHERE and GROUP BY clauses without HAVING clause.

8. If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.
Use the following select statement instead of SELECT COUNT(*):
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

9. Try to restrict the queries result set by using the WHERE clause.
SQL Server will return only particular rows, not all rows from the table(s).

10. Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
A smaller result set will be returned.

11. Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
SQL Server will return to client only particular columns, not all table's columns.

STORED PROCEDURES OPTIMIZATION

1. Include the SET NOCOUNT ON statement in your stored procedures.
This can reduce network traffic because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

2. Call stored procedures using their fully qualified name.
The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name.
Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

3. Consider returning the integer value as a RETURN statement instead of returning an integer value as part of a recordset.
The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using the RETURN statement can boost performance because SQL Server will not create a recordset.

4. Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend using the prefix "sp_" in user-created stored procedure names as SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, followed by the stored procedure using dbo as the owner (if one is not specified).
When you have the stored procedure with the prefix "sp_" in a database other than master, the master database is always checked first. If the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

5. Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improves readability of your code when many parameters are used.
When you use the sp_executesql stored procedure to execute a Transact-SQL statement that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

6. Use the sp_executesql stored procedure instead of temporary stored procedures.
Microsoft recommends using temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures in order to have a better chance of reusing the execution plans.

7. If you have a very large stored procedure, try to break down the stored procedure into several sub-procedures, and call them from a controlling stored procedure.
The stored procedure will be recompiled when any structural changes are made to a table or view referenced by the stored procedure (an ALTER TABLE statement, for example), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, there's a chance that only a single sub-procedure will be recompiled, while other sub-procedures will not.

8. Try to avoid using temporary tables inside your stored procedures.
Using temporary tables inside stored procedures reduce the chance to reuse the execution plan.

9. Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
Using DDL statements inside stored procedures also reduce the chance to reuse the execution plan.

10. Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used.

11. Use SQL Server Profiler to determine which stored procedures have been recompiled too often.
To check if a stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.1

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License