SQL Server Performance Tuning : Pt. 2 - Page 2

Clustered Index

Rarely do I rely on a clustered index for improved performance, since a covered index can easily beat the performance of a clustered index. A clustered index is really just a fatter covered index. The books will tell you there is only one clustered index. That is true. But if you have the disk space and insert/update performance is not a problem, you can repeat all the table columns (up to 16) in as many indexes as you like (up to 249) for the same or better search performance than a clustered index.

Foreign Keys and Indexing

Foreign Keys can be a factor in insert, update and delete statements. Since foreign keys have to be checked, an update or delete in one table can result in a check in another table for the existence of related data.

Create table 
  appointment(apptID int, ...)
Create table 
  appointmentPerson(personID int, apptID int, ...)

When deleting an appointment, a check in the appointmentPerson table will be performed. If there is no index with a first column of apptID, an index or table scan will be performed checking every page in the table. As in previous examples, a useful index can make a huge performance gain in this operation.

Foreign key fields like this are also good candidates for indexing because of their likelihood of being involved in joins and used as search criteria. SQL Dev Pro, my freeware database utility, has a function to search your database for Foreign Key fields and suggest indexes based on FK's and the rowcounts involved. You can download the tool from www.itdevpros.com.

Move Functions Out of Search Criteria

Oftentimes, SQL functions will stop an index from being used. The getdate() function is a prime example:

From

Select * 
  from aTable 
  where datediff(day, theDate, getdate()) < 3

To

Declare @afterDate datetime
Select @afterDate = dateadd(day, -3, getdate())
Select * 
  from aTable 
  where theDate < @afterDate

Don't be afraid of Over Indexing

With storage becoming cheaper every day and most of the systems I'm working on being more search intensive than update intensive, I have not encountered problems adding extra indexes. While it is true they impede insert, delete, and update performance, usually these are single row operations which complete very quickly, while select operations more commonly require multiple tables and multiple rows. In most situations, the benefits of additional indexes will far outweigh their cost. YMMV.

From BOL:

Indexes not only speed up the retrieval of rows for selects, they also usually increase the speed of updates and deletes. This is because SQL Server must first find a row before it can update or delete the row. The increased efficiency of using the index to locate the row usually offsets the extra overhead needed to update the indexes, unless the table has a lot of indexes.

Then again, the indexes we have been discussing, are probably not for row retrieval in updates.

Reformat statements. Apply different strategy

I have noticed that some IN clauses can be quite intensive for no good reason. It seems the optimizer sometimes generates the entire recordset of the clause for each row of the query. I often rephrase an in clause to a join:

From

Select * 
  from aTable 
  where id in 
    (select id 
       from anotherTable)

To

Select * from
    ATable a join anotherTable t
                  On a.id = t.id

Not Ins can be even worse:

From

Select * 
  from aTable 
  where id not in 
    (select id from anotherTable)

To

Select * from
  ATable a left join anotherTable t
                     On a.id = t.id
                     Where t.id is null

Where possible use 'exists' instead of 'count' because exists stops on the first row where count must get an exact answer from any qualifying rows.

From

IF (select count(*) 
       from aTable 
       where criteriaCol) > 0

To

IF exists(select criteriaCol 
       from aTable 
       where criteriaCol)

Count(*) can be more efficient than count(colName) for that reason. While counting(colName) will eliminate nulls from the count, this is not always desired. Count(*) is optimized to count the rows in the table by the fastest means available.

Statistics - Update Statistics

This usually doesn't play a part in my optimizations because I like to have statistics updated regularly, daily if possible, on all tables with sp_updatestats run from a job. After that, sp_recompile all stored procedures and triggers. This is the code using my sp__execAllInTable procedure:

exec sp_updatestats
select 'SP_RECOMPILE ' + quotename(name ) stmt
  into #ExecMe
  from sysobjects where type in ('P', 'TR')
  exec sp__execAllInTable

SQL 2000 automatically updates statistics, but doesn't automatically recompile code. Stored procedures and triggers will be recompiled after the SQL Server is restarted in either version.

The subject of statistics leads to data distribution issues which I'll save for another article and percentages of qualifying rows which I mentioned.

Temp to Perm Table

I have often found that indexes on temporary tables (#tables) are not well used. Sometimes making a permanent user table in the database works better. For single user (likely batch) processes, you can truncate the table before each use. For multi-user processes, one way to do it is by adding the spid to the table and delete by spid before each use:

Delete permTempTable 
  where spid = @@spid

Denormalize

This is usually indicated by joining too many tables (at least 5) or often having functions like min and max. A multiple query strategy occasionally helps here where you break the statement up into two or three statements that run quickly, possibly joined by temp tables.

Denormalization is one of the least desirable courses of action. Denormalizations have to be maintained. This is a judgement call--a small amount of denormalization can tremendously help a difficult situation. Unfortunately, this can be a slippery road leading to a choking morass of code and a maintenance nightmare. In general, if the denormalization cannot be maintained by a single trigger or a single stored procedure, I try to avoid it.

I will shortly be doing performance testing on indexed queries in SQL 2000 in place of denormalizations.

Rearchitect

This is like denormalization, only worse from a labor standpoint. If the schema truly can't support the process at the required performance level and all else fails, a reworking of the basic schema may be in order.

Satisfice, Don't Optimize

What is acceptable performance varies from situation to situation. If I can't achieve orders of magnitude improvement (10, 100, 1000 times faster), I am usually disappointed. But, that amount of improvement will not be needed in every situation.

I used to work for a very smart man who always said "Satisfice, don't optimize." Since we've been attempting optimization all along, maybe we should step back. Our goal was to make things faster, not necessarily as fast as they can possibly be, but just fast enough to make the users happy. While it pains me to let a query go at only 2 or 3 times faster, sometimes, that's good enough.

SQL 2000

My recent testing suggests that quite noticeable gains can be had by upgrading to SQL 2000. It may even alleviate the need for some of the above fixes.

Please Comment

For comments or questions about this article, write me at aaron@itdevpros.com. If you have other optimization suggestions, I'd love to hear about them.