Sharpen Your SQL Server Skills

Q: What do you recommend for optimizing queries?

A: You should optimize every query so it runs as efficiently as possible. I follow seven generic guidelines when I optimize queries. Look at every query carefully and optimize based on additional factors such as I/O cost, current workload on the server, and so on.

1. Review the query execution plan. To locate the execution plan, in SQL Server Management Studio (SSMS) Query Editor, click Query on the top menu bar, then select Include Actual Execution Plan. Run the query, and select the Execution Plan tab in the results pane to view the execution plan for the query. Look for the most expensive operation and start optimizing there.

2. Update column statistics. If a column in a table isn’t indexed, the SQL Server query optimizer uses the statistics for that column to create an optimal execution plan. This improves the performance of the query even though there’s no index on the column. Sometimes using column statistics results in better performance than using an index on a column, but only if the statistics are current. To obtain current statistics, use the UPDATE STATISTICS command. In most cases, column statistics are already created when columns are used in query conditions (e.g., when using WHERE clauses or JOINS).

3. Optimize Index Scan, Table Scan, and Bookmark Lookup. If you find Index Scan, Table Scan, or Bookmark Lookup in the query execution plan, it typically means that your query needs improvement and your indexes aren’t being used. Find columns without indexes used in the query and create an index on each of those columns. Test each column to see if performance improves. Where more than one column is used to create an index, you can create a covering index. A query that contains the same columns as a covering index can be optimized if the covering index is used in that query’s execution plan.

4. Index columns used in an ORDER BY clause. Often, data returned from a query is expected in a certain order. If you need data ordered by a specific column, then index that column. If you need data ordered by a specific column, then indexing that column will provide better performance. For example, in a table containing a column of country names, if the names of the countries need to be in descending order, create an index on the country column in the table. In the following example, I specified the DESC option when I created the index:

CREATE INDEX i_CountryNames ON Country

This index helps resolve a query such as

SELECT CountryNames FROM Country ORDER   BY CountryName DESC

5. Avoid functions on columns. If you use a function in the WHERE clause or in a JOIN clause, doing so will force the query optimizer to scan the whole table instead of taking advantage of an available index. When you use a function on the column, the query optimizer doesn’t know what the resulting column value will be until it runs the function on the whole column, which creates a table scan.

The use of a function on a column might reduce performance. If this happens, here’s a workaround that I use: I create a computed column in the table that applies the function on the desired column. Then I create an index on the computed column and use the computed column wherever I need to use the function. By doing this, I reduce the need for a table scan, the query optimizer can choose to use the index, and query performance is improved. the following are examples of functions on columns; both of which will create table scans:

HAVING MAX(Column) > 1INNER JOIN … ON Table1.Column = LTRIM(@  SearchVar)

6. Use a separate hard disk and hard disk controller for storing indexes and the tempdb database. I recommend that you create tempdb and all the indexes on a separate hard disk. Doing so will improve performance for queries because multiple disk controllers will improve the performance for disk I/O.

7. Follow your intuition and test with many methods. There isn’t a single method by which all queries can be optimized. The query optimizer selects the best execution plan (the most efficient index) for the queries, but sometimes it’s not the optimal plan. You can have the query optimizer create alternative optimized plans.