Solved

Blocking

Posted on 2009-05-17
8
224 Views
Last Modified: 2012-05-07
I'm evaluation performance manager and am getting email notifications of "stuff"
Before I act on this particular type of notification I wanted to run it past you here and find out...
Is it as scary as it sounds?
The app in question is run by our sister company that I look out for.
I am getting one of these notifications around once per hour but no-one seems to be complaining at the client end.

The relevant stuff is:
[Monitor Type]:  SqlServerBlockDetector
[Response Ruleset]:  Block Notification
  Wait Type:            Intent-Exclusive [0x0800]
  Wait Time:            00:00:10

I Googled the lock type but was hoping for a definition in plain English!
Intent exclusive (IX) locks are used to indicate the intention of a transaction to modify some resources lower in the hierarchy by placing Exclusive (X) locks on those individual resources.

Is this something I should be bringing to the attention of the admin of the comapny that runs the app or the vendor themselves? Is this normal DB behaviour and the locking is simply helping to ensure data integrity?
0
Comment
Question by:QPR
  • 4
  • 3
8 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 24409076
These locks are normal DB behavior when data mofidications are occurring.  The question is...what statements are running inthe background when the blocking occurs.
0
 
LVL 29

Author Comment

by:QPR
ID: 24409095
the top statement is a select which apparently is blocking an update statement (2 different users)
The most recent email said the duration was 5 seconds. I'm assuming this means it took 5 seconds between the user submitting their update statement till the time the block was lifted and they could resume?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24411868
To explain more about the Lock escalations you have mentioned, kindly provide the Isolation level set for your database and then the queries involved.

Ideally UPDATE or DELETE statements will be having Exclusive locks as you mentioned above.

Hope this helps you on more detailed information about Locks in SQL Server.

http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx
0
 
LVL 29

Author Comment

by:QPR
ID: 24416193
UPDATE ar_invoice_treatment
      SET    trinv_active = 'N'
      FROM   ar_invoice ar
      WHERE  ar_invoice_treatment.seq_ar_invoice_id = ar.seq_ar_invoice_id
      AND    ar.seq_party_id = @seq_party_id
      AND    ar.allocated = 'Y'
      AND    ar_invoice_treatment.trinv_active = 'Y'

INSERT INTO ar_invoice_treatment (
            treat_det_id,
            seq_ar_invoice_id,
            trinv_date,
            trinv_actioned,
            trinv_active,
            insert_datetime,
            insert_user,
            insert_process)
                  SELECT      treat_det_id,
                                @seq_invoice_header_id,
                           dbo.dbf_date_add_bus_days(@invoice_due_date, td.treat_det_bus_days),
                           'N',
                           'Y',
                           GetDate(),
                           host_name(),
                           'ARPOSTTREAT'
                  FROM     ar_treatment_detail td,
                           ar_treatment_type tt,
                           nc_client nc
                  WHERE    td.treat_type_id = tt.treat_type_id
                  AND      nc.treat_type_id = tt.treat_type_id
                 AND      nc.seq_party_id = @seq_party_id

Where can I check the isolation level? This is a vendors backend and I have had no experience with the design of the DB or the application that uses the data
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24416206
almost certainly your isolation level is read committed, the default.

Make sure the fields in which you're searcing and joining on are indexed.
0
 
LVL 29

Author Comment

by:QPR
ID: 24416462
thanks, the design of the db is out of my control. It's a 3rd party applications backend.
I'm trying to work out whether I should be passing this alert (approx 6 times a day) to the admins over at this branch or just ignore it and put it down to DB behaviour.

As an aside... I was looking at a free tool called SQL Heartbeat that shows all the connected users, their status and their last command. Most of them sit at "idle" with the last statement being begin tran.
This sounds like a terrible way to write an app(??) and asking for trouble.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24417273
If its out of your hands, not much you can do really.  The tables need indexes if they don't already have them.  So, I would pass this alert to any admins that you can....put it in someone elses hands.  :)
0
 
LVL 29

Author Comment

by:QPR
ID: 24417457
did that and they have tried to justify it by saying it's the nature of the work that is happening at the time. Most recent locks have been 5 and 7 minutes which seems pretty unacceptable to me.
Oh well have passed it on and told them I will disable the alerts for this action as they are happy to put up with it.
0

Featured Post

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

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 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

10 Experts available now in Live!

Get 1:1 Help Now