March 3, 2018 at 3:35 am
Hi,
I have a client that has almost 4.000 databases, each for a customer.
We are restructuring the server and migrating to SQL Server 2016.
The server has 16 cores and 72GB of RAM with RAID 10 SSD partitions (4 partitions).
What would be the best way of implementing this?
Create multiple instances with N databases in each and adjust MAXDOP, Max Memory, Cost threshold or just have one instance with all databases and set all resources for that instance?
If we go for multiple instances should we adjust the affinity CPU and affinity IO for each instance or set it automatic with all cores for all instances?
I would be more "comfortable" using just one instance, this because we could set all resources for that instance and as the work load changes it acquires the resources it needs.
By using multiple instances I would have to set max memory to either split equally so the sum wouldn't go over 69GB (let 3GB for OS and SSMS) but by doing this if an instance should need more resources it won't use then since they are limited and allocated to another instance that might be "sleeping"... Other solution for multiple instances would be to set the memory just a bit over, say 20% more, so there would be "shared buffer" of memory and cores (maxdop) (could not set lock pages in memory) and if an instance needs it would use that "shared buffer"... But if by any change all instances would be in full load the "shared buffer" would be a problem and SQL would use swap....
Thanks in advance,
Pedro
March 3, 2018 at 7:57 am
Without knowing db sizes, usage and load on the current server as well as what their customers do with the server its hard to tell you which is better.
Initial thoughts though.
put more memory on it - 72GB for a 16 core server seems low unless all the 4k databases normally used are less than that in size.
Use a single instance. No point in wasting resources in my opinion.
Max dop - 4 to 8 but this does depend on usage and queries.
Cost threshold - should be tested by yourself but change from default of 5 to 25-50.
As for balancing the load across the customers look at resource governor. this will allow you to set max resource usage per customer based on their logins/source hostnames
March 3, 2018 at 10:50 am
The databases vary from 150MB to 1GB, but most are 150MB, the average is 300MB.
There can be up to 150 users using the databases simultaneously, this is an ERP windows application.
I also think the one instance solution is best... Just one engine running meaning it can manage better the resources.
Thanks
Pedro
March 3, 2018 at 4:36 pm
PiMané - Saturday, March 3, 2018 10:50 AMThe databases vary from 150MB to 1GB, but most are 150MB, the average is 300MB.
There can be up to 150 users using the databases simultaneously, this is an ERP windows application.
I also think the one instance solution is best... Just one engine running meaning it can manage better the resources.Thanks
Pedro
If you don't wanna pay much for license, I think you can install multiple instances of Express Edition.
Otherwise, one instance for them all. BTW, you probably need Resource Governor to limit system resources.
GASQL.com - Focus on Database and Cloud
March 5, 2018 at 2:19 am
Alexander Zhang - Saturday, March 3, 2018 4:36 PMPiMané - Saturday, March 3, 2018 10:50 AMThe databases vary from 150MB to 1GB, but most are 150MB, the average is 300MB.
There can be up to 150 users using the databases simultaneously, this is an ERP windows application.
I also think the one instance solution is best... Just one engine running meaning it can manage better the resources.Thanks
PedroIf you don't wanna pay much for license, I think you can install multiple instances of Express Edition.
Otherwise, one instance for them all. BTW, you probably need Resource Governor to limit system resources.
The client has a SQL Standard Edition license so no problem with that...
Also you can limit resources by instance at the instance configuration level, resource governor is good to limit resources by different users using the same instance.
I'll suggest just one instance.
March 5, 2018 at 5:14 am
ahh.. forgot about this detail and didn't thought of asking about it.
Resource Governor only available on the Enterprise Edition and as far as I am aware there is no suitable replacement for it on Standard.
You will need to keep an eye on the server and adjust maxdop if you set it originally to 8 .
No hard rule here unfortunately as it all depends on the load/usage.
I still suggest one instance.
March 14, 2018 at 9:23 am
Hi,
One new "feature" to the installation...
The server has 16 cores and they are using STD licencing so only 4 cores are used...
Does the 4 instances make "sense" now since each instance will use 4 cores?
And if so should we set the cores affinity manually or will SQL do the job ?
Thanks,
Pedro
March 14, 2018 at 9:30 am
Pedro
You'll want to set the affinity manually, otherwise you may end up with each instance using some or all of the same processors as the others.
John
March 14, 2018 at 9:37 am
John Mitchell-245523 - Wednesday, March 14, 2018 9:30 AMPedroYou'll want to set the affinity manually, otherwise you may end up with each instance using some or all of the same processors as the others.
John
Also I have to review MAXDOP configuration..
This is a very intensive server so if I set MAXDOP high one query might just take all the CPU.. I probably will set it to 2...
So many resources and the bottleneck is licensing 🙁
March 14, 2018 at 12:26 pm
PiMané - Wednesday, March 14, 2018 9:23 AMHi,One new "feature" to the installation...
The server has 16 cores and they are using STD licencing so only 4 cores are used...
Does the 4 instances make "sense" now since each instance will use 4 cores?
And if so should we set the cores affinity manually or will SQL do the job ?Thanks,
Pedro
you sure about that? according to Microsoft it is lesser of 4 sockets / 24 cores https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016
March 14, 2018 at 12:33 pm
Pretty sure...
Dmv's says 16 cores but limited to 4 due to licensing or configuration and it's not configuration
March 14, 2018 at 1:50 pm
what is the output of this one?
sp_readerrorlog 0,1,'licensing'
and
select @@version
March 14, 2018 at 2:49 pm
The message SQL sends from the sockets call (EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';) is
SQL Server detected 16 sockets with 1 cores per socket and 1 logical processors per socket, 16 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
The version is SQL Server 2016 Standard Editon SP1 CU7
March 14, 2018 at 3:08 pm
Ahh,...
This is a VM which has been configured incorrectly from my point of view.
What type of VM is it? VMware or Hyper-V? or other...
from the licensing point of view SQL is doing what it says on the tin - you presented 16 sockets, license allows 4 sockets so that is what you get.
If you present 2 sockets with 8 cores each then it will use all 16 cores.
or 4 sockets with 4 cores each
March 14, 2018 at 3:12 pm
VMWare
Thanks for the tip.
I'll talk to the person responsible for the configuration.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply