I have been a DBA for about 2 years now. Of course, most people who know me think I am an application developer because I still spend a lot of time writing .NET code. Anyway, over the course of the last 2 years I have really been focusing on how to performance tune SQL Server. This process has really changed my outlook as a developer. As a result, I am no longer happy with my code unless it performs well at the application and the database layer.

The best way to learn about SQL performance tuning is to learn how to read an execution plan. This is the easiest and most rudimentary way to find out what your query is doing under the covers. Most poorly performing queries are a result of a table scan or clustered index scan. A scan means that you are basically looking at the table row by row in order to find the results. A properly tuned SQL statement will typically use a index seek. I like to use the analogy of someone trying to find a phone number in a phone book.

An index scan would mean that I am going to flip through each page to find someone's phone number. Perhaps I only know the person's street address and first name and because the phone book is organized by last name I can not easily find a good starting point for my search. Therefore I will have to resort to flipping through each page, one by one. This is very inefficient and time consuming.

An index seek would come into play if you know the person's last name. If I am looking for "Joe Smith" then I know that I should look in the "S" section. Since the phone book is ordered by last name I can use the table of contents (index) to find their information.

I put together this short list which ranks the items seen in a typical execution plan from worst to best. The basic idea behind this list is to try to turn a table scan into something better such as an index seek. This could be accomplished by creating an index or modifying your query to properly use the index of maybe even updating your statistics.

1. Table Scans (Worst)
2. Clustered Index Scan
3. Index Scan
4. Index Seek (Best)

So by now you probably came up with the rule that we should turn always try to convert scans into seeks, right? Well not necessarily. If you are are searching a table with only 100 records in it then you probably do not need to waste your time optimizing the query. The entire table can probably be fit into a single page and adding an index to the table is just going to waste storage space. Also, when you do a “select *” without a where clause you are most likely going to do a scan. 90% of the time you should avoid “select *” but there are occasions when you may need to use it. For example, I have a application that stores configuration values in a table. When my app starts up I issue a “select * from configuration” and then I cache the values.

If you get stuck trying to tune a query then do not give up. SQL 2005 and SQL 2008 ship with the Database Engine Tuning Advisor (DETA). The DETA will run your query through several different scenarios and it will make suggestions on how you can improve the performance. Some of the possible solutions will be to add/drop indexes, update statistics or re-write your SQL to take advantage of the existing indexes. In SQL management studio you can right click on your query and send it to DETA for analysis.

In addition to DETA, SQL 2005 and above also have Dynamic Management Views. One of my favorite DMV queries is the “missing index query”. This query will tell you which queries could be improved by adding an index and it will even give you the DDL to create the index!

   1:  SELECT 

   2:      CONVERT (decimal(38,1), 

   3:      migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans)) AS improvement_measure, 

   4:      'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) 

   5:      + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

   6:      + ' ON ' + mid.statement 

   7:      + ' (' + ISNULL (mid.equality_columns,'') 

   8:      + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 

   9:      + ISNULL (mid.inequality_columns, '')

  10:      + ')' 

  11:      + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 

  12:      migs.*, 

  13:      mid.database_id, 

  14:      mid.[object_id]

  15:  FROM 

  16:      sys.dm_db_missing_index_groups mig

  17:      INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

  18:      INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

  19:  WHERE 

  20:      CONVERT (decimal(38,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10

  21:  ORDER BY 

  22:      migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Of course, you need to remember that DMVs are only useful when your instance has been running for a while. DMVs only have information about your SQL environment since the last startup. If your instance has only been running for a few minutes then chances are that your DMV data is useless. If you want to check how long your SQL server has been running then just look at the creation date of your tempdb database (it is recreated every time you restart SQL).