Solved

How can a process block itself?

Posted on 2006-11-05
8
2,415 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 500 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now