Introduction

During the process of performance tuning queries and stored procedures there comes a time when you will notice that the execution plan selected by SQL Server is not the best plan. Often, you can make changes to the database structure and/or the query to correct this problem. On occasion, everything you try doesn’t cause SQL Server to choose the best way to execute your code. These are the times when hints can improve performance. Hints tell SQL Server to execute certain portions of your SQL code the way you believe is best.

There are many things that influence how SQL Server executes SQL code. Among these things are: Primary keys (or lack thereof), indexes, joins, number of rows in tables, versions of SQL Server, and hardware (such as one or more processors). Any change on your SQL Server can influence how it executes a query or stored procedure. A hint you use today that makes a query run in under a second could cause the same query to run more than 10 seconds a few months from now. It all depends on what changes occur on your SQL Server.

Once you place a hint in your code SQL Server will use it even if the reason it chose a poor performing execution plan goes away. In this article I will describe the usage for a few of the hints I consider useful. Before continuing let me caution you to use hints only as a last resort. I have been working with SQL Server for about 3 years now and have used hints in only 3 or 4 stored procedures (out of about 600) in our production environment.

Using The Index Hint

The index hint is useful when you need to force SQL Server to use a specific index to increase query performance. It is best to only use this hint when you know SQL Server is not selecting the index it should and all efforts to help SQL Server decide to use the correct index fail.The hint will accept the ID of the index or the index name. Here is an example of a query that specifies the ID for one index:

USE pubs
 
SELECT *
FROM authors WITH (INDEX(0))

Using ID of 0 for the index ID will force SQL Server to perform a clustered index scan if a clustered index exists. If there is no clustered index then a table scan will be done.

USE pubs
 
SELECT *
FROM authors WITH (INDEX(aunmind))

Example 2 – Force Order Hint

The order SQL Server joins each table in a FROM clause has a direct effect on how a query or stored procedures performs. Joining tables in the wrong order will slow a query down. If you believe SQL Server is joining tables in a way that degrades performance then you will need to find the best order and us the force order hint. For the following examples it will be helpful to view the execution plans to see the order SQL Server joins tables. You can either look at the execution plans on your Query Analyzer or click on the links provided with the queries in this section. This first example will show what happens when tables are joined in a less than optimal order.

Query 1

USE pubs
 
SELECT *
FROM titles t
INNER JOIN roysched r ON t.title_id = r.title_id
INNER JOIN titleauthor ta ON t.title_id = ta.title_id
OPTION (FORCE ORDER)

Now execute the below query (which is the same as query 1 without the force order hint).

Query 2

USE pubs
 
SELECT *
FROM titles t
INNER JOIN roysched r ON t.title_id = r.title_id
INNER JOIN titleauthor ta ON t.title_id = ta.title_id

When we remove the force order hint from the query you will notice that it joins the tables in a different order than the order in which they occur in the query. The order SQL Server uses is better. It starts with the table titleauthor and joins the table titles to it. Then it joins the table roysched to the resulting join of the other two tables.

A quick way to see which of the above queries is quicker is to execute them both in the same Query Analyzer window and view the execution plan. Now lets look at the execution plans to see why the second query is faster than the first query. Click here to view the execution plan for the first query. Click here to see the execution plan for the second query.

For the first query you will notice that the join between the table titles and the table roysched produces a result set with 86 rows. Those 86 rows are kept until the next nested loop join. At that point you have the 123 records that are included in the result set you see on your screen.

The first join in the second query is between the tables titleauthor and titles. This join only produces a result set with 25 records in it. Plus there is no intermediate step before it joins the third table to the 25 records to produce the final result set with 123 records.

The second query ran faster because it dealt with less rows at the beginning. The number of rows it dealt with was a direct result of the order it joined the tables to each other. This demonstrates the importance of the order tables are joined together. SQL Server considers many factors before determining which order it will use to join tables together. Number of rows in a table, primary keys, indexes, number of processors, and current load on the server are some of these factors. On occasion, SQL Server will join tables in the wrong order. When this happens and nothing you try corrects this issue then you should find the order that works best and use the force order hint.

Example 3 – Nolock Table Hint

The nolock table hint is the only hint I use for almost every table used in SELECT queries in almost every stored procedure we have. This hint allows queries to run even while updates are being performed on the tables you want to view. It allows you to do what is called a “Dirty Read” on the data. This term is used because you could potentially view data that has not been committed yet and could get rolled back. We use this hint to avoid blocking on our site. One of the reasons this is acceptable is because most of the updates run on our site only affect one row; so the amount of uncommitted data viewed will be very low.

Here is how you can implement the nolock table hint:

USE pubs
 
SELECT *
FROM titles t WITH (NOLOCK)
INNER JOIN roysched r WITH (NOLOCK) ON t.title_id = r.title_id

The words WITH (NOLOCK) are always placed immediately after the table name or the alias name when one is used.

The following code can be used to demonstrate how you won’t be able to view the data without the nolock hint and the viewing of data before it is committed when using the nolock hint. Execute the following code in one window of Query Analyzer:

USE pubs
 
BEGIN TRANSACTION
 
UPDATE titles SET title = 'UNCOMMITTED' + title

The above code will take out an exclusive lock on the table titles and will not commit the changes until you execute a COMMIT TRANSACTION command. Now in another Query Analyzer window connected to the same server execute the following code:

USE pubs
 
SELECT *
FROM titles

Feel free to stop the execution of this code as soon as you are tired of waiting for the result set. You will never get a result until you close the other window or commit the update you ran using the update command above.SQL Server won’t let you see the data until it is committed or rolled back because that is a default setting which the exclusive lock helps to enforce. To circumvent this you can modify your select statement to look like this:

USE pubs
 
SELECT *
FROM titles WITH (NOLOCK)

Now you will see all the titles with the word UNCOMMITTED at the beginning. Now go to the first window and type ROLLBACK TRANSACTION and execute that line. Then go back and execute the query without the nolock hint. You will see the title names without the word UNCOMMITTED in front of them.If allowing these “Dirty Reads” is not a problem then I recommend using the nolock hint everywhere you can. Such usage will reduce blocking. If “Dirty Reads” are unacceptable then you should never use this hint.

Conclusions

Using hints in your queries has its place other wise Microsoft wouldn’t have included their use with SQL Server; however most uses of hints should be limited to almost none except in my third example. Performance adding hints can help when no other solution can be found.