Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

How to Diagnose and Fix Wait Locks Expand / Collapse
Posted Monday, April 14, 2003 2:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 29, 2007 2:41 AM
Points: 380, Visits: 10
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

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

- 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

Alamir Mohamed

Alamir Mohamed
Post #46934
Posted Thursday, May 15, 2003 9:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 19, 2005 1:14 PM
Points: 1, Visits: 1
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!!!

Post #46935
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse