[NF] SQL Server - too many databases?

[M Jarvis]

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: ***@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+=***@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.

[Paul H. Tarver]

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-***@leafe.com] On Behalf Of M Jarvis
Sent: Friday, July 13, 2018 1:26 PM
To: ***@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: ***@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.

[M Jarvis]

Post by Paul H. Tarver
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$

<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: ***@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+***@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.

[Paul H. Tarver]

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

Post by M Jarvis

Post by Paul H. Tarver
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$

<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: ***@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-***@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

[Stephen Russell]

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

Post by M Jarvis
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: ***@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+***@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.

[Wollenhaupt, Christof]

Post by M Jarvis
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: [phone]
http://www.foxpert.com
<http://www.foxpert.com>
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: ***@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=***@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

[M Jarvis]

<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: ***@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-***@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.

[Paul H. Tarver]

If you ever get a confirmed fix on this, please share!

Thanks!

Paul H. Tarver
***@tpcqpc.com


-----Original Message-----
From: ProfoxTech [mailto:profoxtech-***@leafe.com] On Behalf Of M Jarvis
Sent: Tuesday, July 17, 2018 10:15 AM
To: ***@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: ***@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.

[Stephen Russell]

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.

Post by M Jarvis
<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: ***@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_-***@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.

[Dave Crozier]

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:[phone]
Fax: [phone]
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-***@leafe.com> On Behalf Of M Jarvis
Sent: 17 July 2018 16:15
To: ***@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: ***@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-***@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.
_______________________________________________
Post Messages to: ***@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/***@EX2010-A-FPL.FPL.LOCAL
** 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 obviou