4

I have a single server running a .NET web application and a SQL Server database (2008 Standard). I'm planning to move the database onto a separate server but in order to provision the network hardware I'd like to benchmark the data throughput between the web application and the database. Can port 1433 be monitored internally? If so is there any tool native to Windows 2008 R2 that can do this, or would I need some 3rd party application like WireShark? My connection string is referencing the database using server=localhost, is it still possible to tap into 1433 to see the bandwidth used on this port?

Essentially I'm trying to determine if I need a Gbit or 100 Mbit connection between the web server and database server. Any thoughts on this would be much appreciated.

UPDATE 8th July

I hashed out the above as I realise it's largely irrelevant. For some reason I thought there would be a big cost difference between a 100 Mbit and 1 Gbit switch. Only the cheapo home user switches are 100 Mbit. As others have pointed out, Wireshark will not pickup activity between IIS and the SQL server on the same box. I'm putting in a 1Gbit managed switch for now and I'll use either Wireshark or the built in monitoring on the switch to see what's going on later. I don't think it will be anywhere close to the physical transfer limits imposed by the hardware.

| improve this question | |
  • 2
    1. You're probably using shared memory right now, so it's not possible, as there is no actual network traffic. 2. We have no idea what your future requirements are. 3. If 1 Gbit is available on the network, why is this even a question? Is the network team thinking about doing something crazy like deploying 100 Mbit hardware? – Jon Seigel Jul 7 '13 at 19:19
  • 4
    btw you can force TCP even locally by prependign tcp: in the connection string: server=tcp:localhost – Remus Rusanu Jul 8 '13 at 7:04
6

As I understand it, you want to monitor network traffic in between a web application hosted in IIS and SQL Server, both of which are running on the same server. This is going to be complicated by a few things:

  • Most packet sniffers don't support monitoring traffic over a loopback interface like you are using if your web app connects to SQL Server on localhost or 127.0.0.1. Microsoft's Message Analyzer is an exception, but it is beta, doesn't appear to have much in the way of visualisation or analysis tools, and doesn't appear to be exportable to a format that network sniffers with decent throughput analysis tools can read.

  • The traffic won't be analysable if you're using shared memory or local named pipes instead of TCP to connect to SQL Server.

  • Wireshark also doesn't know what process IDs the packets were sent or received from, which could be a problem depending on your filtering requirements.

Wireshark has good visualisation and analysis tools compared to other network sniffers, though, including throughput graphs and statistics on conversations. What I've done in the past where I needed to filter on process ID was to capture with nmcap or Network Monitor, and then import to Wireshark to do the analysis. None of these will work if you're connecting over the loopback, though, or connecting via local named pipes or shared memory.

I agree with the advice to just get a 1 gbps switch if possible, even if you don't need it today. If you don't need it now, you might later, and it's easier to upgrade in a planned manner while your app is performing well rather than in an emergency where the network is saturated.

A few times a year I encounter a web app that is managing to saturate a 100mbps link to a database server, so I don't think this is an outlandish scenario. Many of the applications I support are not well designed, though, so this may never be a problem for you if you're using common-sense design principles like not retrieving giant results on every page view.

| improve this answer | |
8

Not aware of any built-in method to record bytes over a particular port so if you really need that level of detail, Wireshark is a good bet.

If the server is dedicated to SQL Server I'd be reasonably confident that the majority of traffic over the network was related, so the perfmon counter Network Interface\Bytes Total/Sec should give you a broad guideline.

To be honest it's a mute point in my book. Unless there is a large and nonsensical additional cost for the gig port where your servers are hosted, take it.

| improve this answer | |
  • Price difference? SHow me a current in production enterprise grade 100mbit switch and we talk. 100mbit is "low level end user" these days, not enterprise grade. 1gb with 10gb uplink is enterprise. – TomTom Jul 7 '13 at 15:03
5

The DMV sys.dm_exec_connections has per-connection statistics for traffic, including bytes send/read. But it gets reset every time a physical connection is closed and also is a bad indicator of throughput, specially spikes.

That being said, traffic between client and server should never be a concern. Specially for a web app, where large return sets are not appropriate anyway. Much sooner you will have problems from the interrupt rate of the network card, not from the throughput.

However throughput will play a major role in many of the other activities related to the database operations:

  • copying backup files out to separate storage
  • ETL uploads
  • mirroring or log shipping
| improve this answer | |
  • I suspected the throughput on 1433 wouldn't go anywhere near the physical limit of the network hardware. I just wanted to confirm this before I chose a suitable network switch. I need a switch as I plan to add another DB slave server in a few months. – QFDev Jul 7 '13 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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