June 11, 2012 at 11:45 am
Hi all,
We have some queries that are taking users over 100 seconds to complete. So i thought i would try looking at the queries using Paul Randals Extended Events Query (link - http://sqlskills.com/BLOGS/PAUL/post/Capturing-wait-stats-for-a-single-operation.aspx) to look at the waits for the query.
Results are below: I ran the Query in SSMS on the Server.
SSMS finished in 16 seconds and Returns 44379 Rows
The Extended Events Returns:
Wait TypeWait CountTotal Wait Time (ms)Total Resource Wait Time (ms)Total Signal Wait Time (ms)
NETWORK_IO660072804527627418
SOS_SCHEDULER_YIELD42000
A couple of questions popped up looking at the results!
If a query takes 16 seconds to run in SSMS. Should the total of WAITs in = 16 seconds?
As i understood it Network_IO waits where waits for the application to load data from SQL, I dont have any disk read waits so im guessing that the query results where all in memory? and i had no wait at all for memory for over 44k rows , it just toke SSMS 15 seconds to display it?
Im a bit surprised to see only network_io the more i think about it ..
Hoping someone could explain! 🙂
Thanks for any help.
June 11, 2012 at 2:14 pm
Assuming there were no mistakes with the Sessioning code (which, by how few wait types are listed, there wasn't) I'd have to assume what you're seeing there is the total combination across parallelism.
Try it with MAXDOP(1) and see if they line up more intelligently.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 11, 2012 at 2:37 pm
Hi thanks for the reply.
Tried with MAXDOP 1 and got the same result..
June 12, 2012 at 2:33 am
Thought it might be network contention.. But SQL Monitor doesnt show the NIC ever going near 10% at any point in the day..
Hoping someone could explain..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply