SQL Server Performance Forum – Threads Archive
I/O
Hi, I’m new to this board and was wondering if anyone could help me out with this question. I recently built a SQL server for my company with the following specs; 2 Xeon 3.06, 4 GB memory (expandable to 16), Rocket drive, Various arrays for data, SQL enterprise edition, Dual Cu Gigabit NICs. The question I have it this, the software title we use for production is made by Kodak and is very, very poorly written. To do a basic function each workstation contacts the database a minimum of 7 times. Compound that with ~50 workstations and the transactions amounts are huge, on the level of 20 million transactions in one hour. At the present time it is on the network with a single Gigabit connection to a cisco 3550 switch. The gigabit line is saturated, there is no decrease in traffic around the clock as that plant works 3 shifts. I plan on have the network admin connect the other port and try to load balance the Nics, but I am starting to think that may not be enough. What kind of connection would you recommend? I priced out some 10Gb equipment, but the price is currently staggering. Any suggestions?The only way to know for sure if the additional NIC will help is to try it, and personally, I would. Are all of the workstations connected to the same switch? If not, I would try to do this to minimize the flow of data. Is the network your only bottleneck on the server, or are their other bottlenecks (as determined by Performance Monitor)? ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
The only bottleneck is the network connection. Currently the cpu’s work at ~8%, and ~2.5 GB of memory. Traffic on the drives is under control, temp stuff is on RAID 0. Not all workstations are on the same switch, the switches are all Cisco 3550 12 ports Gigabit over copper layer 3. One in the computer room connects the servers to other 3550’s. When we checked out the switches, they were all at ~ 15% usage average except for the one connecting the SQL server which was pegged at 100% on that port. Is there any 2-5Gb hardware out there. The price I came up with on a 1-10Gb 10-1Gb switch is in the $100,000 range. It’s fast, but the price sucks.
I’ve seen limited benefits of running with up to 4 Gigabit cards in a single box. One thing to remember is that SQL Server has onle 1 TCP-IP listener thread. So with 2 cards it will be swapping between the two ports. The only way to get multiple listener threads is to run with VIA protocol which I believe is only supported by Emulex.
You must be running some extremely lightweight transactions to be doing 20 Million an hour or 5,556 per second and only having ~8% CPU utilization. Or is that your theoretical expected amount and you’re bottlenecked on the NIC?
What are the Bytes/sec and Packets/sec values from Performance Monitor for your Gigabit card? Also how do those values correlate with Transactions/sec to the active database and Batch Requests/sec?
Have you checked the switch itself to see if there are any issues with it, such as old release of its OS? Also, have you done any Protocol Analysis with a sniffer to see what the network traffic looks like to and from the SQL Server? It just seems odd to me that your network traffic is so bad and running at 100%. My impression would be to checkout the network well just to ensure something odd is not happening with it. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
I can recommend a tool called Iris from eEye security, for packet monitoring.
i take it you must mean 20M network packets per hour ?
if so, is that in each direction or combined? I can drive 15K rpc calls/sec on my 2×2.0GHz so your 2x3GHz should handle ~35% more.
of that is for very lightweigth ops
figure the cpu overhead per rpc (both incoming and reply packet) is 250K CPU cycles so 5.5K/sec works out to 22% CPU util on your 2x3G, and that is excluding any real work (SELECT, INSERTs etc)
that is why i am guessing your 20M/hr (5.5k/sec) is packets in both directions, hence 2.8k rpc/sec
also that you are operating with hyperthreading enabled, so 8% means 8% of 4 CPUs when in fact you only have 2 physical CPUs. please keep in mind that interpreting CPU utilization is a bit tricky, it is safer to pretend that max sustain cpu util is 50% if HT is enabled, hence you are really at 16%. if you cannot fix your application to reduce round trips, rortloff’s suggestion of going to VIA is the only way to reduced the overhead of the massive number of network traffic, but every workstation need to link on it btw, i can drive over 12k rpc/sec on fast ethernet, hence i do not believe GE is any where near saturated at 15k/sec
]]>