Find out what queries are causing the biggest amount of network traffic

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.

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! :-)

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 series for a full explanation of this feature:

http://whoisactive.com/docs/01_background/

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

http://whoisactive.com/docs/24_output/

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

http://whoisactive.com

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