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 |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2004-03-28 : 09:13:46
|
On every page-view on my website I verify that the user is logged in with a GUID storedf in a cookie and the ip-adress of the user, and I have created a procedure to do this for me. The procedure holds only one select and it's like this:SELECT Username FROM user WITH (NOLOCK), loggedin WITH (NOLOCK)WHERE user.UserID = loggedin.UserID AND loggedin.GUID = @GUID AND loggedin.Active = 1 However, every now and then there is quite a bit of blocking going on and when I view the properties of the blocking spid I get the call for this procedure 9 out of 10 times. How could this be when I use WITH (NOLOCK), and where should I start troubleshooting...? Not sure if indexes has anything to do with anything but I got a clustered index for GUID in loggedin and also a clustered index on UserID in user.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-28 : 12:46:56
|
| Are you catching this through profiler??? Is it the source blocking SPID, or are there others? What's the base blocking SPID, meaning the SPID that is blocking a SPID, but has nothing blocking it?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-03-28 : 14:48:37
|
| I'm basically clueless when it comes to profiler so I haven't used that. What usually happens is that we notice slow response-times and timeouts on the website and then I go to EM and check under Management -> Current activity -> Locks / Process ID. When this happens I find a few processes that are beeing blocked and one that is blocking. When I check the properties of the process that's blocking, the sp that I referred to over here is the one that usually is displayed as the last SQL batch. Then I usually just kill that process and activity on the database/website resumes to normal."Something has been telling me" that this method is a definite nono, but my lack of knowledge on the subject has made this the only option so far. I have been quite puzzeled about this since there is no locking in the sp and I would assume that there is something else that is causing the blocking but I don't know how to find what it is. Please enlighten me...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
|
|
Blocking and WITH (NOLOCK)