Post reply

monitor transaction flow

  • July 19, 2005 at 10:56 am

    #90319

    I have a problem with multiple transactions - 2, open by a sigle connection. I have a small script to report locks and blocks to me and today I had an alert for many PAGEIOLATCH_EX.

    This is a 3rd party app and I guess they have updated it!!!

    In EM, I see one session and under OpenTransaction there's a number 2. In the column "wait type" there's the latch name.

    what probably happen is that their process opens nested transactions which block each other, or their rollbacks/commits have changed. I tired to run a SQL trace and get the textdata, but it doesn't pull for me when transaction opens, rollbacks/or commits, what statements run inside and are commited or rolled back. I use counters like:

    SQLTransaction, SP:Stmt completed. I see some times if @@transcount>0 commit, but nothing else...

    Please, advise what will be the easiest way to make my monitoring efficiant and to pull the right transaction flow.

    Thank you veru much.mj

  • July 20, 2005 at 6:43 am

    #575771

    I'm not sure as to exactly what concerns you, you will often see lots of pagelatch_ex, as these are required for data changes. It's only if you have contention/blocking that you need to worry - transactions are needed to make data changes, if you don't have multiple transactions then you have a single user system - so ..... unless you have problems locks, transactions and latches are normal.

    Select more of the profile options for T SQL and the stored procs to see all the code being generated, beware that the more of these that you select the more lines you get into your trace ! Make sure you get all the satement starts and finish if you want timings.

    There are procs and stuff available at microsoft for tracking waits and blocks, I modified one to write to a table for checking this for me.

    Note that sometimes increased pagelatches may indicate memory issues, e.g. not enough of it !!

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 20, 2005 at 9:08 am

    #575831

    Thanks a lot. I just was wondering if I could optimize the insert/update showing this lock.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply