Table Hint Usage
Now that you know a little about each of the table-level hints, you may be wondering when you may need to use them. Where I work, we have a very large database that is used by approximately 10,000 customer service reps in a call center environment. While the reps are using the database, we have to load approximately 400,000 new rows of data into the database every three days. This load process can take up to 16 hours, so we are often forced to run the load during operational hours.
To optimize our BULK INSERT load process, we have added the TABLOCK hint to lock tables and speed inserts, and the READUNCOMMITTED hint to allow dirty reads of the data. All transactions generated by the reps are placed into smaller transactional databases so dirty reads are not a problem.
Our very large database and large number of monthly inserts caused our table statistics and indexes to be out of date. We just didn’t have enough operating cycles to keep the all of the statistics and indexes completely updated after every data load.
This problem sometimes caused the query optimizer to create incorrect query plans because it did not always have the most up-to-date information, resulting in poor performance. This forced me to use index hints to force a query to use an index or indexes that I knew were keep updated to solve the slow response times.
Another use of the index hint is to force the use of an index when the query optimizer insists on using a table scan. For example, the SQL Server 7.0 query optimizer seems to have a preference for table scans even though very few rows of the table will be returned by the query.
I don’t tend to use the many other hints at work, but in the past I have found use for the READPAST hint in work queues to allow a row of data to be returned to a client without waiting for locks to be releases by other processes. This is nice to use if you do not want end users to obtain the same row of data.
Others have told me that they often use the FASTFIRSTROW hint when they want to return the first row to the user quickly, to give them something to do, while the rest of the query catches up. I haven’t found too many uses for TABLOCKX, UPDLOCK, XLOCK, or SERIALIZE (HOLDLOCK) in the type of databases I’m accustomed to working with, but I have been told that they are great hints for financial and reporting situations when you need the data to be consistent throughout a transaction.
Different hints are needed for different types of databases or transactions, and you will eventually determine which ones are proper for your situation.
Summary
As you can see, table-level hints are available for use by Transact-SQL developers or SQL Server database administrators, but should only be used to fine-tune your code, not as a standard technique for writing queries. You should perform a strict review of the query plans procedure by the query optimizer before you decide that a table-level hint may be want you need to solve your problem. In addition, you should perform this strict review after the hint is in place.
While table-level hints are not for all levels of users, experienced administrators and developers can use them to solve a limited set of problems, as well as fine-tune a limited set of queries in which the query optimizer has failed in its job to optimize correctly.
Knowledge Based Articles
Q235880 INF: Optimizer Hint NOLOCK or Isolation Level READ UNCOMMITTED Generates Error 605
Q297466 BUG: READPAST Locking Hint Returns an Incorrect Number of Rows
Q308760 FIX: SQL Server Optimizer Ignores Index Hint for UPDATE If One or More Non-Clustered Indexes Exist
Q308886 PRB: NOLOCK Optimizer Hint May Cause Transient Corruption Errors in the SQL Server Error Log
Q310935 FIX: Use of a Dynamic API Server Cursor with a NOLOCK Hint Causes Error 1047
Q320434 FIX: Bulk Insert with TABLOCK Hint May Result in Errors 8929 and 8965 When You Run CHECKDB
Q247365 BUG: Dynamic Cursor With NOLOCK Hint, DELETE Activity, Causes Assertions in Error Log
Copyright 2002 by Randy Dyess. All rights reserved
]]>