Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

On a production SQL server I am seeing intermittent enormous spikes in data traffic. Up to 200Mbit/s which is causing NETWORK IO waits which in turn cause query timeouts. How can I find out what queries are returning big result sets?

share|improve this question
add comment

3 Answers

up vote 9 down vote accepted

You can find this from the DMVs:

SELECT session_id, num_writes, st.text AS statement_text
FROM sys.dm_exec_connections AS ec
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
ORDER BY num_writes DESC

Unlike tracing this should be perfectly safe to run on a Production server.

share|improve this answer
add comment

I would take a trace of the production data using SQL Profiler and I'd gather the code/batches that have biggest reads and writes. Filter the trace to get only procedures and batches that are high in reads/writes. Choose a sample that fits your eye: let's say more than 1 million reads or writes.

I'd take a sample of those calls to a dev/testing machine and run them in Management Studio with option 'Include Client Statistics' enabled (in menu Query - Include Client Statistics). Then you will have a separate window with client statistics info: Bytes Sent From Client, Bytes Received from Server.

DON'T ENABLE TRACING ON A PRODUCTION DB WITHOUT FILTERING THE DATA!!! Filter the most possible (by db, host name, what ever you believe) and only then start the trace. Don't forget to close the Profiler after :-).

PS: I remembered another nice option: along the trace for a period, you should also save data using Perfmon (chose only IO params). Profiler has a nice feature of importing together a trace file and a perfmon data file. And you can see there when you have the biggest IO spikes.

PS2: I agree that Gaius's option is more elegant. But I leave my bulky answer for posterity! :-)

share|improve this answer
add comment

If you haven't looked at it yet, you might want to check out Adam Machanic's sp_WhoIsActive. He recently did a series of blog posts explaining different features that are built into sp_WhoIsActive, one of which is @delta_interval.

Not only will this show you what's taking up the most CPU or I/O overall, but it can also show you what is taking up the most CPU or I/O right now.

Check out the following blog post for a full explanation of this feature:

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/26/delta-force-a-month-of-activity-monitoring-part-26-of-30.aspx

The following blog post explains how to sort the output of sp_WhoIsActive and select which columns to display:

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/24/the-output-of-your-dreams-a-month-of-activity-monitoring-part-24-of-30.aspx

This is one of the many posts available on the following updates link he has setup.

http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx

Version 11.0 is available as of the time of this answer, so if you're using an older version it might be time to upgrade :D

Hope this helps!

Jeff

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.