August 20, 2002 at 10:31 am
I don't want my SQL server to honor any locks.
I had tried using Trasaction Isolation level to ReadUncommitted but found it uneffective. Providing NoLock hint with table is effective but with so many stored procedures already in place its very hard to go and make all these changes. Is there a easier way?
We are few developers working in these Stored procedures and its almost impossible for me to go through each and every query and make sure that we are using NoLock hints at each and every place. Its there a way in profiler I can set it to show me the queries that are making SQL server to acquire a lock.
When I run SQL profiler for Locks I don't see any SQL Text.
August 20, 2002 at 10:58 am
You won't completely avoid locks. There are cases where shared Schema locks will be put in, even with the nolock hint. Of course, these locks just keep you from modifying the structure of the database object, so they aren't typically a concern.
Also remember that a given connection can change their transaction isolation level even if you've set the default for the server. I believe MTS and COM+ by default uses SERIALIZABLE, so this may be a concern.
There are a couple of articles which will identify blocking, which is not what you're wanting to do, but they do help you get an idea of what's going on:
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
INF: How to Monitor SQL Server 7.0 Blocking (Q251004)
Here are the relevant links:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q271509
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q251004
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 20, 2002 at 11:11 am
Locks are an itegral part of the server. There are some that will always be honored.
If you have contention, then perhaps you have design or hardware issues.
Steve Jones
August 20, 2002 at 1:17 pm
Is there a way for me to find out why SQL server is trying to acquire a lock? I understand that I can't completely block all locks. But I want to block most of them where ever possible.
For that I need to find out which queries of mine are trying to get a lock.
August 20, 2002 at 2:22 pm
Just because Im endlessly curious, can you tell me why you are so concerned about avoiding locks?
Andy
August 20, 2002 at 2:39 pm
I had problems where just simple select queries take a lot of time on ocassions and runs fine on other occassions.
When I looked at the profiler I found that the problem was with the locking. As for Website model, I don't care about locks or dirty reads, so I started implementing hints "with (nolocks)" and it helped a lot. But I still see that problem once in a while but very very less as compared to earlier.
At present we get approx. 8 million hits per month. And all of our data change in real time.
Edited by - saugup on 08/20/2002 2:41:34 PM
August 20, 2002 at 8:50 pm
Run a check for locks periodically and either export it to a output file (the example from the articles I cited uses isql and outputs to a text file... it uses WAITFOR DELAY to do the periodic check) or a table (perfect for SQLAgent).
You can find out where the lock is being created and what locks may be in a waiting state by querying against master.dbo.syslockinfo.
Once you have what objects are having locks placed against them, you can then query against syscomments for the individual table names and figure out what stored procedures use those tables. This should narrow down your search considerably.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 21, 2002 at 3:12 am
The best way to avoid locks is to find out what sequence is causing them. What queries are the root and why. If the locks are still there when you open EM then look at the items involved in the locks and their queries. SQL Profiler locks does not tell you the query but it can tell you the objects. You need to look also at TSQL:StmtStarting and the queries involved should appear nearby but at that point unless you were running before the locks started you may not see the original point it began with.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply