Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dcarva
Posting Yak Master
140 Posts |
 Posted - 2007-01-03 : 16:33:53
|
| Hi,I am working on a product that takes files in a queue. Those queues read in SQL statements and execute them. They are mostly individual insert statements. When doing performance testing, I started running into timeouts. Looking at the SQL process activity, I noticed a lot of PAGE locking. One thing I did was to use the ROWLOCK hint in my insert statement for testing purposes. I also used NOLOCK hint for any inserts that use a select clause. Things have improved since I made the changes and timeouts were reduced greatly. Are there any negative side-effects that may come from row locking vs page locking?ThanksDanny |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-03 : 16:36:18
|
| "Are there any negative side-effects that may come from row locking vs page locking?"Can't think of any, in this scenario, but NOLOCK may give you some - I forget the exact message, but its something to do with a record being retrieved under NOLOCK that physically moves, or is deleted, in the interim.Kristen |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-03 : 16:38:38
|
| The rowlock can cause it to need to use up extra locks.The nolock could cause it to insert invalid data.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dcarva
Posting Yak Master
140 Posts |
Posted - 2007-01-03 : 17:05:43
|
| Sounds like it's a trade off between resources and concurrency. I can see that ROWLOCK on an update can be farreaching since it may affect multiple rows. I think if I limit it to insert statements I can avoid using too many resources with a ROWLOCK hint.Thanks! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-03 : 17:20:21
|
quote:
Originally posted by dcarva
Sounds like it's a trade off between resources and concurrency. I can see that ROWLOCK on an update can be farreaching since it may affect multiple rows. I think if I limit it to insert statements I can avoid using too many resources with a ROWLOCK hint.Thanks!
That may cause it to use more resources, because it will need to create locks on individual rows, instead of pages or tables. It really depends on the appliczation.You should stay away from hints. SQL Server will more than likely do a better job than you of selecting the best locking plan.CODO ERGO SUM |
|
|
|
|
|
|
Hints