How to monitor SQL Server's network activity

  • Although SQL Server does all the work, no user is supposed to be directly connected to it.
  • Everything is supposed to be done across the network. The users and their applications connect to the SQL Server through port 1433.
  • The job of an administrator / dba is also to keep track of the network activity the SQL Server is involved with.
  • Microsoft has an undocumented command: dbcc sqlperf(netstats)
  • It's undocumented but it works with SQL Server 7, SQL Server 2000, SQL Server 2005 and SQL Server 2008.

Applies to:

  • Microsoft SQL Server 7
  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Monitoring network activity

  1. DBCC sqlperf(netstats) WITH no_infomsgs;
  2. go
Statistic                        Value
-------------------------------- -------------
Network Reads                    3555
Network Writes                   3576
Network Bytes Read               928788
Network Bytes Written            1006194
Command Queue Length             0
Max Command Queue Length         0
Worker Threads                   0
Max Worker Threads               0
Network Threads                  0
Max Network Threads              0

(10 row(s) affected)
  • The network reads & writes and the number of packets transfered through the network.
  • The network bytes reads & writes are the number of bytes transfered over the network [dooh!].
  • The network threads are the current number of connections that SQL Server has to process.
  • These numbers are low. This SQL Server is for testing purposes only, running under a VMware virtual machine.