How to Diagnose and Fix Wait Locks

  • Scorpion_66

    SSCertifiable

    Points: 7891

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ckirkwood/wait_types.asp

  • ckempste

    SSCoach

    Points: 17983

    Fantastic article.. thanks for the insight into some of the lower level commands, most appreciated.


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • joachim.verhagen

    Hall of Fame

    Points: 3498

    Great article.

    Can you tell what to do with the resultset of dbcc sqlperf(waitstats)

    I saw that it is accumulative, so you have to take the up time into account.

    But what is the unit of measurement and the difference between "wait time" and "signal wait time"?

  • Klaas-Jan

    SSCrazy

    Points: 2267

    Good article , nice to have all waittypes described

  • skasarla

    Mr or Mrs. 500

    Points: 549

    Great Article and very good insight into tackling the lock waits in a transactional system.

  • Scorpion_66

    SSCertifiable

    Points: 7891

    Yes, joachim.verhagen, it is cumulative. The measurement is in milliseconds. The difference between Wait time and Signal Wait time is basically the difference in waiting on something and communicating about waiting on something, to put it in simple terms.

  • ckempste

    SSCoach

    Points: 17983

    Hi all

    Some things to remember..

    a) measures physical io, not logical, flush buffers when testing

    b) force checkpoints when testing writes to force log flush

    before trying it all on prod

    Cheers

    Ck


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Prakash Heda

    SSCertifiable

    Points: 5538

    Best diagnone i have ever seen. No one can beet this..............

    Thumbs up............Prakash

    Prakash

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • Scorpion_66

    SSCertifiable

    Points: 7891

    I'm blown away by the responses to my article, and very much appreciate the comments posted, but does anyone know anything about the waits I didn't talk about. I would very much like to "fill in the gaps" so to speak. What I have is simply what I've garnered over the years, and to date, I have been unsuccessful in ever finding any real documentation on any of the ones not in BOL.

    Edited by - Scorpion_66 on 11/28/2002 9:44:59 PM

  • Yukas

    Mr or Mrs. 500

    Points: 521

    This must be in BOL IMUO (In My UNHonest Opinion)

  • Alamir

    SSCertifiable

    Points: 6020

    I will say something .. which I don't know if it will help u or not

    I think here is the solution of the waitstats Problem (if it is locks on tables)

    When you want a report which based on complicated queries joined from several tables, These tables are updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang happened - because they are in the queue of lock of these tables)!!

    the best thing is to run the query on the server (by making Pass-Through Query like in Access) .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others) 🙂 .

    you will write something like this in your Query Analayzer and call it from your application

    select Invoices.*, Situation.*

    from Situation with (nolock) INNER JOIN Invoices with (nolock)

    ON Situation.SituationID = Invoices.Situation

    where Situation.SituationID =1

    Disadv:

    - when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.

    Adv:

    - no hang at all

    - very fast Response

    - Little summary for Locks in SQL Log file.

    also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to path-through queries.... becuase every transaction or query is done on server first, then processed data is passed to your PC.

    I hope this help you All

    bye

    Alamir_mohamed@yahoo.com

    Alamir Mohamed


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • jstein5000

    Grasshopper

    Points: 11

    I have been scouring the Internet for any information on the networkio wait type. Why? Because, I noticed that I have an issue with it on my database.

    Thank you IMMENSELY for pointing me in the direction to start fixing this. I've been hounding my DBA for the past 3 weeks about network performance, but I could not prove my case. As a database developer, I can now solve the issue programmatically (which is my preferred method anyway) rather than through the addition of hardware.

    The effects on my database server during the extended networkio waits --

    My 4 CPUs stay pegged near 100% utilization

    All other processes begin to crawl

    The cascading performance degradation contributes to many other transaction failures

    And, of course, my users are unhappy.

    I plan to write a stored procedure to detect the situation and kill the offending process(es). I'll probably just schedule the procedure to run every 10 minutes or so to prevent runaway clients from taking over the server. When I finish the procedure, which will be soon since I have users to please, I'll post my results here.

    I'll also spend some time profiling the existing networkio wait times to see what is "normal" on my system. I expect that the networkio wait should be ~much~ less than I'm seeing now.

    Ultimately, after fixing the offensive client code and implementing my networkio monitoring procedure/job, I will investigate if additional NICs are required to keep up with the traffic.

    Where did you find this information or did you figure this out on your own? I couldn't even get the wait type definitions from Microsoft; let alone what to do about them. However you got the information -- many, many thanks for valuable information NOT available anywhere else!!!

Viewing 12 posts - 1 through 12 (of 12 total)

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