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?
Add a comment
|
3 Answers
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.
-
1Unfortunately dm_exec_connections shows the total bytes transferred over the life of each connection. So if your application uses connection pooling (most do), the query shown in statement_text is not likely to be the source of the network traffic. Commented Aug 21, 2023 at 16:43
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.
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
-
1sp_whoisactive does not include columns for network traffic. Commented Aug 21, 2023 at 16:42