November 18, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ckirkwood/wait_types.asp
November 21, 2002 at 2:56 am
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"
November 21, 2002 at 4:11 am
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"?
November 21, 2002 at 6:30 am
Good article , nice to have all waittypes described
November 21, 2002 at 7:39 am
Great Article and very good insight into tackling the lock waits in a transactional system.
November 21, 2002 at 5:12 pm
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.
November 21, 2002 at 7:11 pm
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"
November 21, 2002 at 9:59 pm
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
November 23, 2002 at 9:06 am
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
April 12, 2003 at 7:24 am
This must be in BOL IMUO (In My UNHonest Opinion)
April 14, 2003 at 2:29 am
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
Alamir Mohamed
Alamir_mohamed@yahoo.com
May 15, 2003 at 9:48 pm
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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply