Performance Adding Hints
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.
Here is an example that uses an index name instead of an ID:
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.
