Solved

Blocking

Posted on 2009-05-17
8
226 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 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

15 Experts available now in Live!

Get 1:1 Help Now