How do I control the transmit speed of each SQL server connection?

[former_member211576]

Hi experts,

  I found a lot of short dumps and system logs

errors: "SQL error 10054 occurred" at 3:01:00am recently.

I used perfmon and wireshark and I found out SQL server tried to send

a huge data out(600MB+) each time at 3:01:00am. It caused no buffers errors and

suddently mostly active connections were broken because of no buffers.

I called HP and asked if they can solve this issue by increase transmit

and receive buffers but they suggested me to identify the root cause -

which application.

So, firstly, how do I know which connection try to exhaust my network

resource in SQL server?(DMV?) Secondly, could I slow down the transmit speed

of each SQL server connection?

[clas_hortien]

Hi,

thats good, involve the vendor to do the tests, as they have special tools for it.

Regards

  Clas

P.S.: Nice little box you have there, I love this 1TB DRAM

[clas_hortien]

Hi,

when the blowout happens every day at 3:01 am, it is most probabaly a background job. Check the SAP scheduler and SQL Server Agent for scheduled jobs at this time.

Slowing down the network throughput of the SQL Server will slow down the complete system and is not the right way to solve this (beside the fact that it is not possible). The root question is, who is dropping the connections when the huge dataload comes through ? I would suspect a router between SQL Server and SAP. If your network can not cope with the dataload, you should upgrade the network, at least between SQL and SAP.

Are you running 32 or 64bit SQL Server ?

Regards

Clas

[former_member211576]

Hi Clas,

  Thanks. I have created a customer message to SAP OSS and one very professional SQL server consultant said it is normal that bandwidth utilization went from 10MBps to 300MBps.

  I saw another perfmon data and I found that our bandwidth utilization could go up to 1GBps at peat time. I suspect it is the NIC issue/bug. I am capturing the network information periodically for NIC vendor to analyze.

  64 bits. Windows server 2008 R2, SQL server 2008 R2, HP dl980, 8p64c, 1TB DRAM.

2 *  dual ports 10GbE, NC550SFP.