Query Performance Optimization: Escalate Locking with Table Hints 1

[tlbroadbent]

SQL BOL: "Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead." I

In theory, SQL Server escalates rowlocks and page locks into table locks when transactions exceed a dynamically determined escalation threshold. This threshold is not user configurable.

In practice, we've all seen SQL Server transactions that acquire hundreds or even thousands of page locks during an update transaction without ever seeming to escalate to table locking. If it is known that a transaction will update a large percentage of the rows in a table, we can use hints to escalate to table locking immediately. This will eliminate the need for SQL Server to acquire row or page locks and thus speed processing. The following example shows how to force table locking during an update transaction.

---------------------
Begin Transaction

Select count(*) From MyTblA With (tablockx holdlock) Where 1=2
/* tablockx forces an exclusive table lock
Use tablock to do a non-exclusive lock
holdlock tells SQL Server to hold locks for duration of the transaction */

Update MyTblA Set Col1 = b.Col3
From MyTblA a Inner Join MyTblB b On a.ID=b.ID
Where a.Col1 != b.Col3

Commit Transaction
---------------------

This technique should be used judiciously. Locking a table unnecessarily could have detrimental impact on other processes. : Terry

X-) "I don't have a solution, but I admire your problem."

 

[tlbroadbent]

This TIP has been added to the FAQ library as faq183-767. Terry

People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[fastmatt]

tlbroadbent:
This was a great help. I have been trying to purge off MILLIONS of records from a table, and kept running out of locks.

I did, however, have to change one detail with your table hints method.

The 'with' keyword was giving me syntax errors. I found on another site (

http://www.swynk.com/friends/achigrik/SQL65Locks.asp)

That the 'with' keyword is not necessary for using the table hints. Once I removed 'with', it started running.

I make no attempt to appear expert at this...I'm feeling around in the dark here but just thought I'd share this in case someone else is having the same problem.

Thanks,
Matt

 

[tlbroadbent]

Matt,

Thanks for the pointer. I assume that you are using SQL Version 6.5 as you referenced the 6.5 Locks document on SWYNK. I had not encountered any syntax errors in version 7.0. SQL Books Online for version 7.0 indicate that WITH is optional but encouraged. The following quote is from SQL BOL version 7.0.

The use of the WITH keyword is encouraged although it is not currently required. In future releases of SQL Server, WITH may be a required keyword.

I also found the following in BOL.

The use of commas between table hints is optional but encouraged. Separation of hints by spaces rather than commas is supported for backward compatibility. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[fastmatt]

Yup, I'm cursed with 6.5. The darn database is just about maxed out (running out of space) and the database backup dumps are pushing 3.2 gigs each.
I'm testing my purge process using a backup database server with restored database from primary server.
Even using the table hints, I'm still seeing very slow performance and also getting the following error in NT event viewer:

Error 1204, Severity: 19, State: 3
SQL Server has run out of LOCKS. Re-run your command when there are fewer active users, blah blah blah...

This is the syntax that I've used thus far, and it's still running (for 2 hours now :

Begin Transaction
go
select count(*) from H_pricedenvelopeshistory (tablockx holdlock)
go
Delete from H_PricedEnvelopesHistory
Where sequence between 53000000 and 53500000
go
Commit Transaction

Note that there IS an index on sequence, which is also primary key of the table.

(do I need the 'go' between each line?)

Why would I still be running out of locks, when I'm using the table hints (tablockx holdlock)?

Maybe 500,000 records at a time is still too many, even despite getting an exclusive lock on the table??

Thanks for any ideas
Matt

 

[tlbroadbent]

Read faq183-768 which explains a trick to speed range search queries. I'm not certain how effective it will be with 6.5 but it is a very effective technique for speeding range search queries in SQL 7.0.

500K records may be too many to attempt at one time. You might want to set a limit, say 100K records and dump the transaction log after every 100K deletes. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[Sepahram]

fastmatt ,

You may ommit WITH keyword in many cases. However , you'll get a error message, if you do it in an INSERT Statement as below:

INSERT INTO Table WITH ( LockHint ) VALUES ( ColumnList )

The messages is:

Server: Msg 110, Level 15, State 2, Line 1
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Amir,