1

Topic: sql server 2016 enterpise. Determination of optimal number of kernels for

Good afternoon!
We have functioning corporate data store on the virtual machine with the selected 64 kernels and a copy of server MS of a SQL Server 2016 Enterprise. To productivity at present questions are not present. But there was a question on purchase of licenses "Under a kernel". To buy on all already selected on  kernels allows nobody. There is a desire to understand what optimal number of kernels for support of normal productivity of ours . For these purposes we want to adjust counters and to look at them  in peak loadings with various number of the kernels selected . Actually a question: whether such approach is real, what counters is better to use? Or it is possible to approach to the decision of the given task still somehow?

2

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

ditok;
To select any even number from 2 to 64 (the license on 2 kernels) to count cost and to show this total .
If it becomes absolutely bad to it - to reduce number if bewilderment "that you to me with such trifle is visible climb" - to increase number.
To repeat iterations while  it will not be sad, but it is tolerant.
Then in the virtual machine to reduce an amount of kernels to the necessary number and to look that will be.
If for couple of weeks anybody notes nothing - to buy and not to be soared.
If users with the claim for brakes start to resort - to send to  - let prove that is very necessary.
And generally, it is interesting to esteem a substantiation, what for on data store Enterprise? You checked, what you have not enough 24 kernels or 128 Gb ? If checked - that simply repeat the same tests. If did not check... It Means to you and it is not necessary, and the algorithm of actions wrote above smile))

3

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

s_ustinov wrote:

to select any even number from 2 to 64 (the license on 2 kernels) to count cost and to show this total .
If it becomes absolutely bad to it - to reduce number if bewilderment "that you to me with such trifle is visible climb" - to increase number.

to It it is sad for each rouble :-)
And when money is already selected, the request to increase causes scandal and  goals.

ditok wrote:

For these purposes we want to adjust counters and to look at them  in peak loadings with various number of the kernels selected . Actually a question: whether such approach is real, what counters is better to use? Or it is possible to approach to the decision of the given task still somehow?

Well, simply to look at loading CPU.
And then to limit kernels in  (or in sequel adjustments) and to look, as works.

4

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

ditok;
There is a possibility gradually to reduce an amount of the selected kernels?
If yes, try
+ To plan moving with  on a separate piece of iron

5

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

!
Deliver a trial, and rape him. Whether 180 days on tests suffices that?
And generally, criterion: Smaller from 2 - 1. A maximum of the total which is ready to pay a finance department and 2. The magnification of an amount of kernels twice reduces to routine reports time of generation less, than on 10 % (for storages) [means we take previous  kernels], or average loading of processors within operational day fluctuates around 60 % (for oltp) [as reached - everything that it is more, in general, waste of money].
All , certainly.

6

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

s_ustinov wrote:

ditok;
To select any even number from 2 to 64 (the license on 2 kernels) to count cost and to show this total .
If it becomes absolutely bad to it - to reduce number if bewilderment "that you to me with such trifle is visible climb" - to increase number.
To repeat iterations while  it will not be sad, but it is tolerant.
Then in the virtual machine to reduce an amount of kernels to the necessary number and to look that will be.
If for couple of weeks anybody notes nothing - to buy and not to be soared.
If users with the claim for brakes start to resort - to send to  - let prove that is very necessary.

As the principal of Economical service (in the past) I would recommend to the Chief engineer of the enterprise to dismiss you for such "expert" approach.
ditok;
I can assume that search, all of them still it is necessary to manage to give 64 kernels a full-time job on full and that the disk subsystem did not shut up from tasks of all these kernels. It is better, better to increase operative storage.

7

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

Alex_496 wrote:

I can assume that search, all of them still it is necessary to manage to give 64 kernels a full-time job on full and that the disk subsystem did not shut up from tasks of all these kernels. It is better, better to increase operative storage.

Perhaps at them a terrabyte  and 500 disks?
Here it is nonprofessional, to give a sentence to kernels, is absolute knowing nothing about system.
And to restrict kernels and to look is just the normal approach because any counters and calculations behavior of real system and comfort of users you will not calculate.

8

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

Alex_496 wrote:

As the principal of Economical service (in the past)... I can assume that search, all of them still it is necessary to manage to give 64 kernels a full-time job on full and that the disk subsystem did not shut up from tasks of all these kernels.

Perhaps it is necessary to return back to a manual? 64 kernels during an epoch of cheap SSD/flesh-arrays and storage - very much and are not enough.

9

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

ditok wrote:

Good afternoon!
We have functioning corporate data store on the virtual machine with the selected 64 kernels and a copy of server MS of a SQL Server 2016 Enterprise... Or it is possible to approach to the decision of the given task still somehow?

And the storage will produce reports to users service SSRS on the same virtual machine (whether a little the ambassador of loading of the data and formations of the aggregated tables it will be necessary  to deliver by mail PDF/Excel with fresh digits, here puzzles a manual of fastening KPI on sales or  if such it is possible to count in this storage)? Or only itself SQL and SSIS? And if you want also Tabular SSAS to collect in the same place?
I would be repelled that considered in advance that will be from tasks on  - if want all and at once then to beat out licenses one packet. Therefore how once to drag in the budget the big money this one, and half and then once again half - can kill or transfer on the budget next year.

10

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

Alex_496 wrote:

As the principal of Economical service (in the past) I would recommend to the Chief engineer of the enterprise to dismiss you for such "expert" approach.

As it is good that at the majority mine colleagues with humour everything is all right. smile))

11

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

Sergey Alekseevich wrote:

Perhaps it is necessary to return back to a manual? 64 kernels during an epoch of cheap SSD/flesh-arrays and storage - very much and are not enough.

And then SUDDENLY it appears that basis on one hundred gigabyte smile))
And here - an Enterprise Edition, 64 kernels...

12

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

ditok wrote:

what counters is better to use?

Technically counters to look on loading CPU - in my opinion since October 2007 in essence exchanged nothing, when on technical not that laid out detailed article .
Explicitly, visually, but to Zak's judgement I to trust on 100 % did not become,  it not MVP was, and idle time DBA in Levi Strauss at that point in time.

13

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

s_ustinov wrote:

what for on data store Enterprise?

Well at least compression of tables and online creation of indexes...

14

Re: sql server 2016 enterpise. Determination of optimal number of kernels for

KRS544;
Compression and SE supports, since 2016 SP1
https://docs.microsoft.com/ru-ru/sql/sq … erver-2016