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

What are the best ways to identify and avoid stored procedure recompiles?

The best way to identify stored procedure recompiles is using the SQL Server Profiler or Coefficient. With Coefficient, you can see the actual SQL statement that causes the recompile.

SQL Server recompiles a stored procedure when:

• The WITH RECOMPILE option is used on the procedure or execution.
• A schema change occurred on any referenced objects.
• A user ran SP_RECOMPILE on the procedure.
• A referenced table is dropped and re-created.
• The procedure was dropped and re-created.
• All copies of the execution plan in the cache are in use.
• A sufficient percentage of the data in a referenced table has changed.
• The procedure interleaves DML and DDL statements.
• Certain operations are performed on temporary tables.

To avoid recompiles, consider the following:

• Fully qualify executions (e.g. Exec pubs.dbo.spTest).
• If a particular statement in the procedure causes a recompile, execute that statement using SP_EXECUTESQL instead.
• Consider using sub-procedures for any statements that are causing recompiles. It’s faster to recompile a small procedure than a large one.
• Consider using the KEEP PLAN option on DELETE, SELECT, and UPDATE statements on permanent tables if many rows are affected and this is causing a recompile.
• Issue all DDL statements (e.g. Create Table) before any DML statements (e.g. Insert).
• Consider adding more memory to increase the SQL Server buffer cache size.

What are some of your tips for making the most out of indexes?

Here are some that I suggest every DBA and developer consider:

• Examine queries closely and keep track of column joins and columns that appear in WHERE clauses. It’s easiest to do this at query creation time.
• Look for queries that return result sets based on ranges of one or more columns and consider those columns for the clustered index.
• Avoid creating clustered primary keys if the PK is on an IDENTITY or incrementing DATETIME column. This can create hot-spots at the end of a table and cause slow inserts if the table is “write” heavy.
• Avoid excessive indexes on columns whose statistical distribution indicates poor selectivity, i.e. values found in a large number of rows, like gender (SQL Server will normally do a table scan in this case).
• Avoid excessive indexes on tables that have a high proportion of writes vs. reads.
• Run the Index Tuning Wizard on a Coefficient trace file or Profiler trace file to see if you missed any existing indexes.
• Do not totally rely on the Index Tuning Wizard. Rely on your understanding of the queries executed and the database.
• If possible, make sure each table has a clustered index, which may be declared in the primary key constraint (if you are using a data modeling tool, check the tool’s documentation on how to create a clustered PK).
• Indexes take up extra drive space, slow down INSERTs and UPDATEs slightly, and require longer backup/replication times, but since most tables have a much higher proportion of reads to writes, you can usually increase overall performance creating the necessary indexes, as opposed to not creating them.
• Remember that the order of columns in a multi-column index is important. A query must make use of the columns as they are listed in the index to get the most performance increase. While you don’t need to use all columns, you cannot skip a column in the index and still receive index performance enhancement on that column.
• Avoid creating unique indexes on columns that allow NULL values.
• On tables whose writes far outweigh reads, consider changing the FILLFACTOR during index creation to a value that allows for adequate free space on the index pages to allow for optimal table inserts.
• Make sure SQL Server is configured to auto update and auto create statistics. If these options cause undue strain on the server during business hours and you turn them off, make sure you manually update statistics, as needed.
• Consider rebuilding indexes on a periodic basis, by recreating them (consider using the DROP_EXISTING clause), using DBCC INDEXDEFRAG (SQL 2000), or DBCC DBREINDEX. These commands defragment an index and return the fill factor space to the leaf level of each index page. Consider a mix/match of each of these commands for your environment.
• Do not create indexes that contain the same column. For example, instead of creating two indexes on LastName, FirstName and LastName, eliminate the second index on LastName.
• Avoid creating indexes on descriptive CHAR, NCHAR, VARCHAR, and NVARCHAR columns that are not accessed often. These indexes can be quite large. If you need an index on a descriptive column, consider using an indexed view on a smaller, computed portion of the column. For example, create a view:

  CREATE VIEW <view_name> 
  WITH SCHEMABINDING 
  AS 
  SELECT ID, SUBSTRING(<col>, 1, 10) as “<col>” 
  FROM <table>

  Then create an index on the reduced-sized column <col>:

  CREATE INDEX <name> on <view_name> (<col>) 

  This index can still be used by SQL Server when querying the table directly (although you would be limited in this example to searching for the first 10 characters only). Note: Indexed views are SQL Server 2000 only.

• Use surrogate keys, like IDENTITY columns, for as many primary keys as possible. INT and BIGINT IDENTITY columns are smaller than corresponding alpha-numeric keys, have smaller corresponding indexes, and allow faster querying and joining.
• If a column requires consistent sorting (ascending or descending order) in a query, for example:

  SELECT LastName, FirstName 
  FROM Customers 
  WHERE LastName LIKE ‘N%’ 
  ORDER BY LastName DESC

  Consider creating the index on that column in the same order, for example:

  CREATE CLUSTERED INDEX lastname_ndx 
  ON customers(LastName, FirstName) DESC

  This prevents SQL Server from performing an additional sort on the data.

• Create covering indexes wherever possible. A covering index covers all columns selected and referenced in a query. This eliminates the need to go to the data pages, since all the information is available in the index itself.