February 28, 2007 at 12:38 pm
We would have to edit a few hundred stored procedures if we have to use the WITH statement for ROWLOCKS
February 28, 2007 at 1:47 pm
if i understand you correct, you can try to use a different new isolation level for your DB in SQL Server 2005 -> take a look at BOL -> snapshot
March 1, 2007 at 8:30 am
If you can migrate to SQL 2005, snapshot isolation would probably be a great solution for whatever issue you are having.
You can't enforce ROWLOCKS at a database level because that would almost always be a spectacularly bad idea. Any time you use some kind of hint to force SQL to do things differently, you also have to question your approach and try to figure out why what you're doing is giving the SQL query optimizer problems. I'm not saying hints aren't useful, I'm just saying that they need to be used carefully, and suggesting that a hint should be applied wholesale to hundreds of procedures doesn't sound to me like an appropriate level of analysis has been done.
You have to have a detailed understanding of what queries are causing the locking issues, and then think about using a different approach for those queries. Breaking large updates into a series of smaller updates, and using more temp tables are two strategies that may increase concurrency. Using NOLOCK on lookup queries can have a huge benefit for concurrency, if you understand the possible pitfalls and know the application well enough to know when to use it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply