An Exclusive Interview with David Gugick, SQL Server Performance Expert and Software Developer

What tips can you provide on the best way to optimize stored procedures?

Some of the best things you can do to boost stored procedure performance include:

• Limit the use of cursors wherever possible. Use temp tables or table variables instead. Use cursors for small data sets only.
• Make sure indexes are available and used by the query optimizer. Check the execution plan for confirmation.
• Avoid using local variables in SQL statements in a stored procedure. They are not as optimizable as using parameters.
• Use the SET NOCOUNT ON option to avoid sending unnecessary data to the client.
• Keep transactions as short as possible to prevent unnecessary locking.
• If your application allows, use the WITH (NOLOCK) table hint in SQL SELECT statements to avoid generating read locks. This is particularly helpful with reporting applications.
• Format and comment stored procedure code to allow others to properly understand the logic of the procedure.
• If you are executing dynamic SQL use SP_EXECUTESQL instead of EXEC. It allows for better optimization and can be used with parameters.
• Access tables across all stored procedures in the same logical order to prevent deadlocks from occurring.
• Avoid non-optimizable SQL search arguments like Not Equal, Not Like, and, Like ‘%x’.
• Use SELECT TOP n [PERCENT] instead of SET ROWCOUNT n to limit the number of rows returned.
• Avoid using wildcards such as SELECT * in stored procedures (or any SQL application for that matter).
• When executing stored procedures from a client, using ADO for example, avoid requesting a refresh of the parameters for the stored procedure using the Parameters.Refresh() command. This command forces ADO to interrogate the database for the procedure’s parameters and causes excessive traffic and application slowdowns.
• Break large queries into smaller, simpler ones. Use table variables or temp tables for temporary storage, if necessary.
• Understand your chosen client library (DB-LIB, ODBC, OLE DB, ADO, ADO.Net, etc.) Understand the necessary options to set to make queries execute as quickly as possible.
• If your stored procedure generates one or more result sets, fetch those results immediately from the client to prevent prolonged locking. This is especially important if your client library is set to use server-side cursors.
• Do not issue an ORDER BY clause in a SELECT statement if the order of rows returned is not important.
• Put all DDL statements (like CREATE TABLE) before any DML statements (like INSERT). This helps prevent unwanted stored procedure recompiles.
• Only use query hints if necessary. Query hints may help performance, but can prevent SQL Server from choosing the best execution plan. A query hint that works today may not work as well tomorrow if the underlying data changes in size or statistical distribution. Try not to out think SQL Server’s query processor.
• Consider using the SQL Server query governor cost limit option to prevent potentially long running queries from ever executing.

What are the benefits of using stored procedures over not using stored procedures?

Stored procedures are a powerful feature of SQL Server, and using them produces many benefits, including:

• Stored procedures facilitate code reuse. You can execute the same stored procedure from multiple applications without having to rewrite anything.
• Stored procedures encapsulate logic to get the desired result. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don’t remove any result sets columns).
• Stored procedures provide better security to your data. If you use stored procedures exclusively, you can remove direct Select, Insert, Update, and Delete rights from the tables and force developers to use stored procedures as the method for data access.
• Stored procedures are a part of the database and go where the database goes (backup, replication, etc.).
• Stored procedures improve performance. SQL Server combines multiple statements in a procedure into a unified execution plan.
• Stored procedures reduce network traffic by preventing users from having to send large queries across the network.
• SQL Server retains execution plans for stored procedures in the procedure cache. Execution plans are reused by SQL Server when possible, increasing performance. Note SQL 7.0/2000: this feature is available to all SQL statements, even those outside stored procedures, if you use fully qualified object names.