작성자 : Randy Dyess
내용 : SQL2000 Table Hints
음음.. 여러가지.. SQL서버 쿼리의 힌트 들에 대한 정보 입니다.
참고 하시길 바랍니다.
SQL Server 2000 Table Hints |
by Randy Dyess
Visit his website at: www.TransactSQL.com
As you advance in your skills as a Transact-SQL developer, or SQL Server database administrator, there will come a time when you need to override SQL Server's locking scheme and force a particular range of locks on a table. Transact-SQL provides you with a set of table-level locking hints that you can use with SELECT, INSERT, UPDATE, and DELETE statements to tell SQL Server how you want it to lock the table by overriding any other system-wide or transactional isolation levels. After saying that, it is sometimes necessary to correct the optimizer and use a hint to force the optimizer to use an index other than the one it picks, or to simply control the behavior of the locks. An example of choosing an alternative index would be when the query optimizer chooses an index that is constantly out of date and you do not have the cycles to bring the index up to date before you run the query. An example of controlling the locking behavior would be to lock a table to optimize a BULK INSERT. Table Hints May Not Always Be Used By the Query Optimizer In addition, the query optimizer will often choose an indexed view over a table. In case your table-level hint will be ignored, you can override the query optimizer's preference for indexed views by using the OPTION (EXPAND VIEWS) query hint. Another reason the query analyzer may ignore your hint is due to the fact that the table may contain computed columns and the computed columns are computed by expressions and functions referencing columns in other tables, and the table hints are not specified for those tables. Table hints are not propagated on tables with computed columns, so the hint will not be used on tables referenced by computed columns, table-level hints are propagated on base tables and views referenced by another view though. SQL Server also does not allow more than one table hint from either the Granularity hint group (PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, TABLOCKX), or the Isolation Level hint group (HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE) to be used in the FROM clause for each table. This basically means that you cannot specify ROWLOCK and TABLOCK for a table in the same FROM clause. SQL Server will also not allow the NOLOCK, READUNCOMMITTED, or READPAST hints to be used against tables that are the targets of a DELETE, INSERT or UPDATE statement. Table Hint Syntax SYNTAX [ FROM { < table_source > } [ ,...n ] ] < table_source > ::= table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] < table_hint > ::= { INDEX ( index_val [ ,...n ] ) | FASTFIRSTROW | HOLDLOCK | NOLOCK | PAGLOCK | READCOMMITTED | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }Microsoft encourages the use of the WITH keyword, even though it is optional, as they state that a future release of SQL Server may require this keyword. Microsoft also encourages the use of the optional commas between different hints, as spaces are only allowed for backward compatibility reasons. You can see that there are fifteen hints defined in this syntax listing. Thirteen of these hints (HOLDLOCK, NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, TABLOCK, TABLOCKX, UPDLOCK, XLOCK) are considered table-level locking hints while (INDEX and FASTFIRSTROW) are considered table hints. This doesn't mean much to most developers, but I thought I would tell you how Microsoft groups them. Table Hint Descriptions INDEX is used to specify the name or object ID of an index or indexes that will be used by SQL Server when processing the statement. SQL Server will chose an index by default if one is not specified by the use of the INDEX keyword, but sometimes you will need to force SQL Server to use a particular index. Only one index hint can be used per table, but you can specify more than one index in this hint. If a clustered index exists on the specified table, using INDEX(0) will force a clustered index scan and INDEX(1) will force a clustered index scan or seek. If no clustered index exists on the table, then INDEX(0) will force a table scan while INDEX(1) will be interpreted as an error. If you chose multiple indexes to be used for the hint, any duplicates will be ignored. Be careful in the order you place indexes when you use multiple indexes with the index hint. SQL Server try to apply as many conditions as possible on each index, so if you place the broader indexes first, you may have all your conditions covered and SQL Server will not have to create AND statements for all the specified indexes. You can use a maximum of 250 non-clustered indexes in an index hint. Be aware that if an index hint with multiple specified indexes is used on a fact table in a star join, then SQL Server will ignore will return a warning message and ignore the hint. FASTFIRSTROW will optimize the query to retrieve the first row of the result set. HOLDLOCK (equivalent to SERIALIZABLE) applies only to the table specified and only for the duration of the transaction, and will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required. HOLDLOCK cannot be used in a SELECT statement with the FOR BROWSE option specified. NOLOCK (equivalent to READUNCOMMITTED) permits dirty reads. Dirty reads will not issue shared locks and will ignore exclusive locks placed by other processes. It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back. PAGLOCK will force the use of a page lock instead of a table lock. READCOMMITTED specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, which will result in nonrepeatable reads and may cause phantom data. READCOMMITTED is the default table hint in SQL Server. READPAST specifies that locked rows be skipped during the read. READPAST only applies to transactions operating at the default READ COMMITTED isolation level, and will only read past row-level locks. READPAST can only be used in SELECT statements. Normal blocking can be worked around by having transactions read past rows being locked by other transactions. READUNCOMMITTED (equivalent to NOLOCK) permits dirty reads. Dirty reads will not issue shared locks and will ignore exclusive locks placed by other processes. It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back. REPEATABLEREAD specifies that locks be placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. ROWLOCK forces the use of row-level locks instead of page or table level locks. SERIALIZABLE (equivalent to HOLDLOCK) applies only to the table specified and only for the duration of the transaction, and it will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required. TABLOCK specifies that a table lock to be used instead of a page or row level lock. This lock will be held until the end of the statement. TABLOCKX specifies that an exclusive lock be held on the table until the end of the statement or transaction, and will prevent others from reading or updating the table. UPDLOCK specifies that update locks will be used instead of shared locks, and will hold the locks until the end of the statement or transaction. XLOCK specifies that an exclusive lock be used and held until the end of the end of the transaction on all data being processed by the statement. The granularity of XLOCK will be adjusted if it is used with the PAGLOCK or TABLOCK hints. Table Hint Usage 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. 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 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 [출처] SQL2000 Table Hints 관련 문서|작성자 필립박 |
'데이터베이스 > SQL Server' 카테고리의 다른 글
[mssql]임의의 행 무작위 추출 (0) | 2008.04.29 |
---|---|
MS SQL Server 성능 조정 및 최적화 (0) | 2008.04.29 |
mssql 함수모음 (0) | 2008.04.29 |
[SQL 서버 2005 실전 활용] ① 더 강력해진 T-SQL (0) | 2008.04.29 |
[SQL 서버 2005 실전 활용] ② 닷넷과의 통합 (0) | 2008.04.29 |