Locking Hints and Trace Flags

Just as you can specify hints on queries to direct the query optimizer to choose a certain index or strategy in its query plan, you can specify hints for locking. For example, if you know that your query will scan so many rows that its page locks will escalate to table locks, you can direct the query to use table locks in the first place, which is more efficient. We'll look at locking hints and locking contention issues in detail in Chapter 16.

For now, armed with the knowledge of locking you've gained here, you can observe the locking activity of your system to understand how and when locks occur. Trace flag 1204 provides detailed information about deadlocks, and this information can help you understand why locks are occurring and how to change the order of access to objects to reduce them. Trace flag 1200 provides detailed locking information as every request for a lock is made. (But its output is voluminous.) You'll see details about these trace flags in Chapter 16.



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net