Wednesday, July 13, 2011

SQL Server 2008 Table Hints

Sometimes your query executes very slowly. One of the ways to increase the queries performance
is using the SQL Server 2008 table hints. The table hints specify a locking method to be used by
the query optimizer with this table, specify the locking granularity for a table, or one or more
indexes to be used by the query optimizer when querying this table. If you want to use the table
optimizer hints, you should specify the WITH keyword with the hints list after the table name in
the FROM clause. You can specify several table hints and separate them by a comma.

If you want to set any table hints, do not remember to test the query with hint
and without the hint and compare results.
Because SQL Server 2008 query optimizer usually proposes the best execution plan, you should
test the query with table hint and use this hint only when it provides the best result. If the
table hint provides the better execution plan not stable, avoid using this hint and rely on the
SQL Server query optimizer.

Use the NOLOCK or READUNCOMMITTED table hint if you need the highest concurrency.
These hints specify that dirty reads can be allowed. When these hints are used, no shared locks
are issued and no exclusive locks are honored.
This example shows how you can use the NOLOCK table hint:
SELECT * FROM titles WITH (NOLOCK)

Consider using the READCOMMITTED table hint to specify that statements cannot
read data that has been modified but not committed by other transactions.
The READCOMMITTED table hint specifies that read operations comply with the rules for the
READ COMMITTED isolation level by using either locking or row versioning. When the database
option READ_COMITTED_SNAPSHOT is OFF (by default), the shared locks are used, when the
READ_COMITTED_SNAPSHOT is ON, the row versions are used instead of shared locks.
This example shows how you can use the READCOMMITTED table hint:
SELECT * FROM authors WITH (READCOMMITTED)

Use the REPEATABLEREAD table hint if you need more consistency, than default
(READCOMMITTED) and higher concurrency, than with SERIALIZABLE.
By default, SQL Server uses READ COMMITTED isolation level. With this isolation level SQL Server
uses shared locks while reading data. It ensures that not committed data will not be read, but it
not ensures that the data will not be changed before the end of the transaction. If you need the
current data will not be changed before the end of the transaction, you can use the REPEATABLEREAD
table hint.
This example shows how you can use the REPEATABLEREAD table hint:
SELECT * FROM authors WITH (REPEATABLEREAD)

Consider using the NOWAIT table hint if you need to return a message when a lock is
encountered on the table.
When the NOWAIT table hint is used, SQL Server 2008 returns a message as soon as a lock
is encountered on the table. Using this hint is equivalent to use SET LOCK_TIMEOUT 0 for
a specific table.
In this example, SQL Server 2008 will return a message when a lock is encountered on the
authors table:
SELECT * FROM authors WITH (NOWAIT)

Use the INDEX hint if you need to specify the name or ID of the indexes to be
used by SQL Server 2008 when processing the statement.
This example shows how you can use the INDEX hint:
SELECT au_fname FROM authors WITH (INDEX(0))
If a clustered index exists, INDEX (0) forces clustered index scan, if no clustered index
exists, INDEX (0) forces a table scan.
Note. You can use only one index hint per table, but multiple indexes can be used in the
single hint list.

Consider using the XLOCK table hint to take and hold exclusive locks until
the transaction completes.
When you use the XLOCK table hint with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks
apply to the appropriate level of granularity.
This example shows how you can use the XLOCK table hint with the PAGLOCK hint:
SELECT * FROM titles WITH (XLOCK, PAGLOCK)

Use the READPAST table hint if you need to return only the pasted rows.
When the READPAST table hint is used, the locked rows will be skipped.
This is the example to return only the pasted rows from the titles and titleauthor tables:
SELECT * FROM titles a JOIN titleauthor b
WITH (READPAST) ON a.title_id = b.title_id

Consider using the ROWLOCK table hint.
This hint specifies that a shared row lock will be taken instead of shared page or table lock.
The ROWLOCK provides the higher concurrency, but at the cost of lower performance because
SQL Server 2008 should hold and release locks at the row level.
This example shows how you can use the ROWLOCK table hint:
SELECT * FROM titles WITH (ROWLOCK)

Use the HOLDLOCK or SERIALIZABLE table hint if you need the highest consistency.
These hints specify shared locks will be holding until the completion of a transaction.
By default, the shared locks are released as soon as the required table or data page is
no longer needed. This results in higher consistency, but at the cost of lower concurrency.
This example shows how you can use the HOLDLOCK table hint:
SELECT * FROM titles WITH (HOLDLOCK)

Consider using the PAGLOCK table hint.
The PAGLOCK hint specifies that a shared page lock will be taken instead of table lock.
This example shows how you can use the PAGLOCK table hint:
SELECT * FROM titles a JOIN titleauthor b
WITH (PAGLOCK) ON a.title_id = b.title_id

Consider using the UPDLOCK table hint.
The UPDLOCK hint specifies that update locks are taken while reading the table and that
they are held until the end of the Transact-SQL statement or end of the transaction.
This example shows how you can use the UPDLOCK table hint:
SELECT * FROM titles a JOIN titleauthor b
WITH (UPDLOCK) ON a.title_id = b.title_id

Use the FASTFIRSTROW table hint if you need to quickly return first row.
You can quickly return first row and work with it, when the query continues execution and
produces its full result set. The FASTFIRSTROW table hint has the same effect as specifying
the FAST 1 query hint. Keep in mind, that when you use the FASTFIRSTROW hint the rest of
the rows are often returned more slowly that they would have been if the hint had not
been specified.
This is the example to quickly return first row from the titles and titleauthor tables:
SELECT * FROM titles a JOIN titleauthor b
WITH (FASTFIRSTROW) ON a.title_id = b.title_id

Consider using the TABLOCK table hint.
The TABLOCK hint specifies that a shared lock will be taken on the table until the end of
the Transact-SQL statement.
This example shows how you can use the TABLOCK table hint:
SELECT * FROM titles a JOIN titleauthor b
WITH (TABLOCK) ON a.title_id = b.title_id

Consider using the TABLOCKX table hint.
The TABLOCKX hint specifies that an exclusive lock will be taken on the table until the end
of the Transact-SQL statement or the end of the transaction. The TABLOCKX provides the worst
concurrency, but, in this case, SQL Server 2008 will make minimum work to hold and release locks.
This example shows how you can use the TABLOCKX table hint:
SELECT * FROM authors WITH (TABLOCKX)

If your query is very slow, try the following before using table hints:
- rebuild indexes used in the query (or defragment them using the DBCC INDEXDEFRAG command),
- update statistics on the relevant tables,
- consider creating new indexes for this query,
- rewrite your query to provide better execution plan.
Use the table hints only when the actions above do not provide good performance.

1 comment:

  1. try
    {

    con1.ConnectionString = SQLConnectionString;
    if (con1.State == ConnectionState.Closed)
    {
    con1.Open();

    }
    trans = con1.BeginTransaction();
    SqlCommand cmd1 = new SqlCommand(“select name from testinfo WITH(XLOCK) where ID=’” + textBox1.Text + “‘”, con1,trans);

    SqlDataReader dr1 = cmd1.ExecuteReader();

    if (dr1.HasRows)
    {
    while (dr1.Read())
    {
    label1.Text = dr1["name"].ToString();

    }

    }
    catch (Exception ee)
    {

    trans.Commit();
    con1.Close();
    MessageBox.Show(“Access By Other User “);
    }
    }
    this is working properly ,it generate an exception which is correct , gives an message “access by another user ” ,but this system define exception required lot of time min 10 sec it take , so i want to find any other condition which will show that this row is access by another user.is there is any other parameter so i can identify that this row is exclusive lock by other user.

    ReplyDelete