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
---
_______________________________________________
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/CAPT54rZg5wagf4Q1d+=Q_TSu8zmk3AodR3rfrNSo0qaWZ4U1Yw@mail.gmail.com
** 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.
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?
--
Matt Jarvis
Eugene, Oregon USA
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
[excessive quoting removed by server]
_______________________________________________
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/00c501d41af2$b7f324c0$27d96e40$@tpcqpc.com
** 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.
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
---
_______________________________________________
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/CAPT54rYzmFYGaL-0C7E5tO9wg-8VcRM3+9TUgEsA5AWFmwDsgw@mail.gmail.com
** 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.
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]
_______________________________________________
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/44C9CDBE-2D47-4D9F-B193-49F051128D4B@tpcqpc.com
** 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.
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]
_______________________________________________
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/CAJidMY+z7P_e+Kna6S2St+jK4-po6GF-fWU7CEHPEWE80+wp2Q@mail.gmail.com
** 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.
Author: Wollenhaupt, Christof
Posted: 2018-07-15 02:41:36 Link
>
> 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.
--
Christof
--
foxpert GmbHUlzburger Straße 352
22846 Norderstedt
Tel. Zentrale: (040)
605 3373-70
Fax: (040) 605 3373-75
Registergericht: Amtsgericht Kiel, HRB 19260 KI
Sitz der Gesellschaft: Norderstedt
Geschäftsführer: Christof Wollenhaupt
--- 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/CAL4QJhiKPukv=97vs54rRwXjVQKvo5=1uSC7oL85u3z0jngPbQ@mail.gmail.com
** 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.
<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-zN4X0BWOPGDug@mail.gmail.com
** 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.
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]
_______________________________________________
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/009901d41de3$c3f6a550$4be3eff0$@tpcqpc.com
** 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.
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]
_______________________________________________
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/CAJidMY+L8TpGyHN+r6qS=uvpTHYpXEDm8K6hwPK24_-BsefHFw@mail.gmail.com
** 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.
Correct Matt,
They are paid to sort out those types of problems.... you aren't..