July 13, 2012 at 8:08 am
Hi,
We have a 2005 standard SQL Server here with 16x 15k SAS600 disks, 16GB RAM, 2x teamed broadcom net extreme 1Gb/s network cards and a hex core xeon of I think ~3 Ghz.
We are running an OLTP database with an application on a physically separate server that takes all the client connections. The system is running slow and we don't know what is to blame as all servers app/db etc are idling <20% cpu, not paging out to disk etc etc.
The only unusual thing I see is that we have about 200 TCP connections open on it and most of those are SQL Server connections related to this application that can be seen in Activity Monitor. This is higher than usual. NICs are averaging about 5-10Mb/s utilisation. I know that isn't a lot for a 2Gb/s connection but I also know that lots of tiny requests have a higher impact than a few big ones - I just don't know how much of a difference that makes.
This is all a bit higher than usual but I don't think it's excessive - especially as CPU on SQL Server is rarely going over 6 or 7% utilisation.
I don't know an awful lot about networking, but 200 connections doesn't sound like a huge amount to me. Am I wrong there? Does that sound excessive to anyone?
Thanks,
Ben
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
July 13, 2012 at 8:52 am
Per this article each user connection to SQL Server 2005 uses Approximately (3 * network_packet_size + 94 KB).
I say 200 idle connections is nothing for a machine with those specs, and is quite normal actually in my experience when serving data to applications that use connection pooling. Now, if 200 is outside your baseline numbers then you should look into why the number has increased. More users? Change in connection pool settings on the app side? Did they add a server to the web farm, or the like?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 13, 2012 at 9:13 am
Thanks that's very useful information that I will file away to the back of my mind for future reference! Does assist to prove the point that our SQL Server is operating within normal parameters.
Our DBA has just identified a failed disk in a RAID5 array on the application server so the system slowness being reported by the end users is being caused by this and it may also explain the increase in connections - if processes are taking longer to complete and users are issueing requests to the system at the same rate we're going to have more concurrent stuff going on!
Cheers.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply