Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can a process block itself?

Posted on 2006-11-05
8
Medium Priority
?
2,638 Views
Last Modified: 2016-11-17
First off, let me say that I'm a programmer not the DBA so please excuse any obvious stupidity. We have a moderately sized SQL2000 (SP4) environment (120GB) with a dozen databases the largest being about 30GB. All the MDF files are on our on one large SAN connected to the Server (Win2003) using fiber.

We are experiencing some serious performance issues. I don't know if this is related, but I have noticed something I can't explain. When checking for SQL process locks (using SQL Enterprise Manager) I often find something like:

spid 57 (Blocked By 57)
spid 57 (Blocking)

This can happen to almost any process. Almost everything in our environment runs as a stored proc and I know there isn't one or even a handful of procs that can exhibit this problem. Almost any proc appears exihibit this condition at one time or another. Most of our procs are written to run so quickly that a casual observer wouldn't notice the blocking. but it still happens occassionally to most procs.

The only commonality i can find is that all of the offending procs seem to cause some activity in tempdb as tempdb is usually listed in the objects associated with the process. I'm stumped. What the heck is going on here? Could this be because everything is on the SAN and we're I/O bound? Even if that's the case, how can a process block itself?

Thanks,
Ed
0
Comment
Question by:lippert
8 Comments
 
LVL 16

Accepted Solution

by:
Hillwaaa earned 2000 total points
ID: 17878870
Hi Lippert,

I've experienced this behaviour at one client where I work who has an almost identical setup.  After discussing this with a Microsoft representative, we were informed that this is not related to locks, but due to the latch waits you see in sysprocesses.  As you have assumed it is IO related - basically it is caused by the spid waiting for a page to load into memory.  The process (very roughly) involves the spid aquiring two latches to the same page, the first to prevent any processes (including itself) from attempting to read the page until it is loaded into memory (and the latch is relased) and the second to read the page once the first is complete.

If this isn't clear enough - or you'd like the full detail - please let me know.

Cheers,
Hillwaaa.

0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17879073
If thers a MSDTC involved , it can happen (MS DTC Transaction Commit Operation Blocks Itself)


Check this site http://support.microsoft.com/kb/821740/en-us?spid=2852&sid=global

if this is not the case, i assume ur sql server is sp4

sp3a doesnt report these issues it seems
0
 
LVL 1

Author Comment

by:lippert
ID: 17879117
To:  gauthampj

Per Microsoft:

"STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4."

We are running SP4.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17879143
Hi lippert,

Some further information that is most likely what you are seeing:

http://support.microsoft.com/kb/906344

Cheers,
Hillwaaa
0
 
LVL 1

Author Comment

by:lippert
ID: 17879268

To  Hillwaaa:

This sounds quite plausible. I have been experimenting som and have found a proc that is 'borderline'. If I run it "as-is" there is no locks reported, but if I add a very simple where clause, it reports it is blocking itself. Also, if I run it "as-is" and another proc is run (We have a night shift overseas that acces our database via a web application using ADO to call stored procs.) it will start out not reporting a lock, but as soon as the other proc starts, both will report locks. note here that neither proc is accessing the same database, just the same SQL server instance.

So, if you still think that your answer is the right one, I'll take you up on your offer to have you provide full details. (I'd also like to know what your solution was or what you think we need to do to solve our problem.) Send the details and I'll award the points...

Thanks,
Ed
0
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17885530
Hi Ed

Was the kb article sufficient? The info we got from Microsoft was pretty much a cut and paste job from that.

To confirm that your issue is definately this, when the run the scenario you described with the 'borderline' sp blocking itself, if you look in Enterprise Manager, under Current Activity and Process Info, you should see a wait type of PAGEIOLATCH_XX.  This just means that it is waiting for the information to be loaded from disk.  

In our client case we think that our performance issues are due to poor SAN performance (as running the same load on the server's local drive does not see the same wait times) - and that the blocking we are seeing is due to the additional information that SP4 is providing.

Cheers,
Hillwaaa.
0
 

Expert Comment

by:jmoreno8238
ID: 23544390
I have a similar situation but in my case the process is locked for an extended period of time, and this process shows information concerning an specific table. Now this table is not the biggest, but whenever we run a query that returns all rows this query might hang for more that half an hour before I just cancel it.
However if I do a query that will likely return newer results the query will respond almost immediately.

Now from what I gathered from Hillwaaa's first post and I quote "The process (very roughly) involves the spid aquiring two latches to the same page, the first to prevent any processes (including itself) from attempting to read the page until it is loaded into memory (and the latch is relased) and the second to read the page once the first is complete."

I'm guessing that at least one of the pages on which some of the data on that table is located in a damaged, too fragmented or bad sector; therefore while the process waits for the page to be loaded into memory it blocks itself but since the page is on a damaged sector is never loads so the process blocks itself for an extended period of time.

Hopefully my situation might be of some help to anyone.
0
 

Expert Comment

by:Paul; Amar
ID: 41891921
OMG, SQL 2000 SP4 was a complete nightmare, as was upg from 2000 -> 2005, and parameter sniffing issues.  Those days were many late nights and screaming users.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question