We are running into an intermittent issue w/ our SQL Server running SQL2016. It's not that our tables are all that large (usually), but our server is trying to manage around 550 different DB's parked on it. Few of the DB's are open at any one time, but the server has to keep track of them at least to some extent.
When the problem arises our dev team runs into timeouts and sometimes the problem goes away, some time it doesn't and our IT guys out of desperation restarts the machine.
One of our devs suggested that even though we aren't past some theoretical limit on the number of DB's the server can manage, maybe we just have too many and getting rid of most, or at least detaching them, might help.
Any thoughts on this?
Not sure whether this will apply to you or not, but I recently had a client running an ERP for hundreds of client databases spread over multiple servers. We became aware of problem they were having with random timeouts to a specific server. Since all our other server connections were working fine (ie: never timing out), I suggested they check the network card, network cable & network port on the switch. After some testing, they ended up changing out the network card and the problem went away. Our assumption was we were getting early warning signs of an impending network card failure. This occurred almost a year ago and we've had no timeouts since then.
This may not be the issue since you are running on a single server, but stranger things have happened. Hope this helps!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of M Jarvis Sent: Friday, July 13, 2018 1:26 PM To: profoxtech@leafe.com Subject: [NF] SQL Server - too many databases?
We are running into an intermittent issue w/ our SQL Server running SQL2016. It's not that our tables are all that large (usually), but our server is trying to manage around 550 different DB's parked on it. Few of the DB's are open at any one time, but the server has to keep track of them at least to some extent.
When the problem arises our dev team runs into timeouts and sometimes the problem goes away, some time it doesn't and our IT guys out of desperation restarts the machine.
One of our devs suggested that even though we aren't past some theoretical limit on the number of DB's the server can manage, maybe we just have too many and getting rid of most, or at least detaching them, might help.
Any thoughts on this?
On Fri, Jul 13, 2018 at 2:44 PM, Paul H. Tarver paul@tpcqpc.com wrote:
Not sure whether this will apply to you or not, but I recently had a client running an ERP for hundreds of client databases spread over multiple servers. We became aware of problem they were having with random timeouts to a specific server. Since all our other server connections were working fine (ie: never timing out), I suggested they check the network card, network cable & network port on the switch. Report [OT] Abuse: http://leafe.com/reportAbuse/00c501d41af2$b7f324c0$ 27d96e40$@tpcqpc.com
<snip>
Thanks Paul....
After I posted our IT guys started talking about the server waiting on ASYNC_NETWORK_IO. More or less they said it seems like SQL is serving up the data but our client end software (our stuff) can't keep up. They said all indications on the server performance when this is happening is that everything is just dine, memory is dandy, all is well.
So perhaps the bottleneck is our software (quite possible) or the net card, or something else. *Something* is strangling the throughput and I'll bet swapping out the card is a quick and easy thing to do (easy for me to say since I'm not the one to do it).
I'll mention it to them. Thanks again...
Depending on the server design it may have multiple network ports built into the motherboard but I’d still recommend putting a high quality network card in the computer to eliminate any problems that might continue on these other ports. Relatively cheap, easy method to eliminate the network ports from the troubleshooting quagmire.
Paul
Sent from my iPhone
On Jul 13, 2018, at 5:55 PM, M Jarvis brewdaddy@gmail.com wrote:
On Fri, Jul 13, 2018 at 2:44 PM, Paul H. Tarver paul@tpcqpc.com wrote:
Not sure whether this will apply to you or not, but I recently had a client running an ERP for hundreds of client databases spread over multiple servers. We became aware of problem they were having with random timeouts to a specific server. Since all our other server connections were working fine (ie: never timing out), I suggested they check the network card, network cable & network port on the switch. Report [OT] Abuse: http://leafe.com/reportAbuse/00c501d41af2$b7f324c0$ 27d96e40$@tpcqpc.com
<snip>
Thanks Paul....
After I posted our IT guys started talking about the server waiting on ASYNC_NETWORK_IO. More or less they said it seems like SQL is serving up the data but our client end software (our stuff) can't keep up. They said all indications on the server performance when this is happening is that everything is just dine, memory is dandy, all is well.
So perhaps the bottleneck is our software (quite possible) or the net card, or something else. *Something* is strangling the throughput and I'll bet swapping out the card is a quick and easy thing to do (easy for me to say since I'm not the one to do it).
I'll mention it to them. Thanks again...
-- Matt Jarvis Eugene, Oregon USA
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
This is sticky that is for sure.
How much RAM on the server? Are there multiple instances to combine similar DBs in a group? Is the MAX server ram seeing on box set to default? Is the server pagenating swapping to disk for more ram?
Are there a certain combination of DBs in use with queries across dbs hat have subsets of data in the where clause?
What is the backend, local RAID or SAN?
What is free space on the log as well as database drives? Is logging turned on all over the place and if it is has it EVER been used in the last 5 years? Is there log shipping on the primary database(s) ?
A NIC was already brought up and it could be that as well? Are there multiple NICs if the backend is really a SAN? You may have an issue with the SAN NIC as I did a year+ ago.
HTH
On Fri, Jul 13, 2018 at 1:26 PM M Jarvis brewdaddy@gmail.com wrote:
We are running into an intermittent issue w/ our SQL Server running SQL2016. It's not that our tables are all that large (usually), but our server is trying to manage around 550 different DB's parked on it. Few of the DB's are open at any one time, but the server has to keep track of them at least to some extent.
When the problem arises our dev team runs into timeouts and sometimes the problem goes away, some time it doesn't and our IT guys out of desperation restarts the machine.
One of our devs suggested that even though we aren't past some theoretical limit on the number of DB's the server can manage, maybe we just have too many and getting rid of most, or at least detaching them, might help.
Any thoughts on this?
-- Matt Jarvis Eugene, Oregon USA
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
We are running into an intermittent issue w/ our SQL Server running SQL2016. It's not that our tables are all that large (usually), but our server is trying to manage around 550 different DB's parked on it. Few of the DB's are open at any one time, but the server has to keep track of them at least to some extent.
When the problem arises our dev team runs into timeouts and sometimes the problem goes away, some time it doesn't and our IT guys out of desperation restarts the machine.
The limit is much higher, indeed. For SQL Server the number of databases is also less a performance issue than many assume. Sql server stats should give you an indication of what goes wrong. With lots of waits on the network interface (outgoing, not the SAN one), you could have one of two issues:
You are transferring too much data, doing SELECT * queries when you only need a few columns, and such. If your server is connected to a single GBit network port and you have 1000 clients, that's 100 KB/sec maximum transfer rate per client while at the same time Sql server is serving data from memory a magnitude faster.
You have high latency on the network such as a VPN, a WAN, a client using Wifi, a defective switch or a misconfigured network with ARP floods, etc.
There another common scenario not related to network waits. The server slows down massively, but doesn't seem to consume any significant resources. This is usually due to locking issues with a larger number of users. In this scenario the application typically behaves great for the developer who is testing on their own, but not all great when many users start working.
I gave a session on these kind issues a few years ago at Southwest Fox.
<snipped>
Thanks for the replies fellas... Things have turned into a bit of a circus with that server so I'll pass along the questions and suggestions and let them sort it out.
My gut tells me it's a NIC card which I mentioned to them, but they poo-poo'd doing anything about it... Hey if they wanna stay nights and weekends fussing with it, that's their thing not mine...
Thanks again!
Matt
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
If you ever get a confirmed fix on this, please share!
Thanks!
Paul H. Tarver paul@tpcqpc.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of M Jarvis Sent: Tuesday, July 17, 2018 10:15 AM To: profoxtech@leafe.com Subject: Re: [NF] SQL Server - too many databases?
<snipped>
Thanks for the replies fellas... Things have turned into a bit of a circus with that server so I'll pass along the questions and suggestions and let them sort it out.
My gut tells me it's a NIC card which I mentioned to them, but they poo-poo'd doing anything about it... Hey if they wanna stay nights and weekends fussing with it, that's their thing not mine...
Thanks again!
Matt
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
Just to mess with their heads I'd tell them to consider putting in 2-3-4 instances to separate databases better going forward. I have 7 on my POR as well as Test servers Across those instances I have over 120 dbs for both Prod and Test. Prod data is just under 2TB now in total.
On Tue, Jul 17, 2018 at 10:15 AM M Jarvis brewdaddy@gmail.com wrote:
<snipped>
Thanks for the replies fellas... Things have turned into a bit of a circus with that server so I'll pass along the questions and suggestions and let them sort it out.
My gut tells me it's a NIC card which I mentioned to them, but they poo-poo'd doing anything about it... Hey if they wanna stay nights and weekends fussing with it, that's their thing not mine...
Thanks again!
Matt
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Correct Matt, They are paid to sort out those types of problems.... you aren't.. 😊 Dave
--------------------------------------------------------------- This communication and the information it contains is intended for the person or organisation to whom it is addressed. Its contents are confidential and may be protected in law. If you have received this e-mail in error you must not copy, distribute or take any action in reliance on it. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
Flexipol Packaging Ltd. has taken every reasonable precaution to minimise the risk of virus transmission through email and therefore any files sent via e-mail will have been checked for known viruses. However, you are advised to run your own virus check before opening any attachments received as Flexipol Packaging Ltd will not in any event accept any liability whatsoever once an e-mail and/or any attachment is received.
It is the responsibility of the recipient to ensure that they have adequate virus protection.
Flexipol Packaging Ltd. Unit 14 Bentwood Road Carrs Industrial Estate Haslingden Rossendale Lancashire BB4 5HH
Tel:01706-222792 Fax: 01706-224683 www.Flexipol.co.uk ---------------------------------------------------------------
Terms & Conditions:
Notwithstanding delivery and the passing of risk in the goods, the property in the goods shall not pass to the buyer until the seller Flexipol Packaging Ltd. ("The Company") has received in cash or cleared funds payment in full of the price of the goods and all other goods agreed to be sold by the seller to the buyer for which payment is then due. Until such time as the property in the goods passes to the buyer, the buyer shall hold the goods as the seller's fiduciary agent and bailee and keep the goods separate from those of the buyer and third parties and properly stored protected and insured and identified as the seller's property but shall be entitled to resell or use the goods in the ordinary course of its business. Until such time as the property in the goods passes to the buyer the seller shall be entitled at any time
-----Original Message----- From: ProFox profox-bounces@leafe.com On Behalf Of M Jarvis Sent: 17 July 2018 16:15 To: profox@leafe.com Subject: Re: [NF] SQL Server - too many databases?
<snipped>
Thanks for the replies fellas... Things have turned into a bit of a circus with that server so I'll pass along the questions and suggestions and let them sort it out.
My gut tells me it's a NIC card which I mentioned to them, but they poo-poo'd doing anything about it... Hey if they wanna stay nights and weekends fussing with it, that's their thing not mine...
Thanks again!
Matt
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAPT54rZioXb5o4=i009Dsm8yanvXoRN=pK+Q... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
-
Dave Crozier -
M Jarvis -
Paul H. Tarver -
Stephen Russell -
Wollenhaupt, Christof