1

Topic: How to restrict appetites of request to storage?

We have requests, on which output some tens millions lines. Requests not difficult, but demand that sorting for MERGE, storage for creation HASH of tables. Parameter max degree of parallelism it is modest 4. At 1 these requests work twice longer. At more 4 - advantage is hardly noticeable.
Problem in that these requests persistently demand on 25-45 gigabyte  and it is obedient wait, while the server cannot select with it so much. And, at performance, the record result of real consumption of storage was less than 10 gigabyte. Statistics updated, even with FULLSCAN. Does not help. In respect of request the record count in times exceeds the real. While for storage the separate server has not been selected, max degree of parallelism was 1 and storage quite sufficed. When became 4 - requests became will be fulfilled faster, but on one. That is, over half of time while SSAS reads out results of request, SQL the server has simply a rest.
Whether it is possible as that manually to restrict the size of storage requested by request at the server at start?

2

Re: How to restrict appetites of request to storage?

ptr128;

wrote:

25-45 gigabyte

It is not a lot of for

wrote:

some tens millions lines

Search for the sponsor, the body volume should correspond to a load...

wrote:

When became 4 - requests became will be fulfilled faster, but on one

Mahlo of kernels under tasks, sm above...
Seven caps can be sewed from one skin, but small.

3

Re: How to restrict appetites of request to storage?

Vladislav Kolosov;
Totally ludicrous.
ptr128
Look in plans and correct request, at you a wrong estimation.
Well and the delimiter is with 2012, but it not a variant
https://support.microsoft.com/en-us/hel … nt-and-max

4

Re: How to restrict appetites of request to storage?

Whether

ptr128 wrote:

It is possible as that manually to restrict the size of storage requested by request at the server at start?

the Master  and transactions probably does not read the documentation? Or did not begin to trouble simply itself with perusal of the sections concerning to resource governor?

5

Re: How to restrict appetites of request to storage?

TaPaK wrote:

well and the delimiter is with 2012, but it not a variant

At the client generally 2008R2 so it is exact not a variant.

6

Re: How to restrict appetites of request to storage?

ptr128 wrote:

it is passed...
At the client generally 2008R2 so it is exact not a variant.

Then governor in hands

7

Re: How to restrict appetites of request to storage?

invm wrote:

the Master  and transactions probably does not read posts? Only is able to write?

How the regulator of resources can modify incorrectly calculated by the plan of request the demanded memory size?

8

Re: How to restrict appetites of request to storage?

ptr128 wrote:

it is passed...
How the regulator of resources can modify incorrectly calculated by the plan of request the demanded memory size?

Whether at you "It is possible as that manually to restrict a question the size of storage requested by request" instead of to modify

9

Re: How to restrict appetites of request to storage?

Whether

TaPaK wrote:

at you "It is possible as that manually to restrict a question the size of storage requested by request" instead of to modify

I apologize. Probably incorrectly expressed. Meant to restrict in the course of request scheduling to (modify) and I diligently described a situation. After the scheduler already calculated these 45, the server can only or select to it so much, or deliver in queue.
On the server 64  RAM. Therefore requests are executed sequentially though any of them does not consume in runtime more than 10 . As after to the first the server selects interrogated 45 , on the following request already remains a little.

10

Re: How to restrict appetites of request to storage?

ptr128;
Judging by your description you want to push the button - "to make well". Curve requests are corrected was specific everyone

11

Re: How to restrict appetites of request to storage?

ptr128 wrote:

How the regulator of resources can modify incorrectly calculated by the plan of request the demanded memory size?

For you to "restrict and"modify"- synonyms?

12

Re: How to restrict appetites of request to storage?

ptr128 wrote:

we Have requests, on which output some tens millions lines. Requests not difficult, but demand that sorting for MERGE, storage for creation HASH of tables. Parameter max degree of parallelism it is modest 4. At 1 these requests work twice longer. At more 4 - advantage is hardly noticeable.
Problem in that these requests persistently demand on 25-45 gigabyte  and it is obedient wait, while the server cannot select with it so much. And, at performance, the record result of real consumption of storage was less than 10 gigabyte. Statistics updated, even with FULLSCAN. Does not help. In respect of request the record count in times exceeds the real. While for storage the separate server has not been selected, max degree of parallelism was 1 and storage quite sufficed. When became 4 - requests became will be fulfilled faster, but on one. That is, over half of time while SSAS reads out results of request, SQL the server has simply a rest.
Whether it is possible as that manually to restrict the size of storage requested by request at the server at start?

I summarize. You have a measurement of checks (or discount cards or still that). You instead of view over the plane table made view as  of the main table and connected through left join with different auxiliary (the list id and titles of types of the check or the list id and titles of types of discount cards and so on).
And further SSAS at attempt of update of measurement ProcessUpdate throws aside SQL Select queries distinct [__key___], [__name___], [__key__1_], [__key__2_] from view, then for each attribute of measurement select distinct [__key__1_], [__name__1_] from view - and at some instant SQL builds absolutely incorrect plan. When he considers that key_ it is not enough in the big table - it does preliminary merge and uses some kernels when you to it clamp eggs in a vice level MAXDOP in unit - it  storage for HASH tables.
And absolutely all the same it is sad and  when at you inside view it is used openquery on other server and  something from local SQL bases. And still the rights are not present on statistics review on a remote server.
Therefore - at first preempt in the plane table of measurement. And then use for measurement view over this plane table. And then ProcessUpdate will be fast and painless, storage to be used exactly so much, how many it is necessary and how many is requested, and your hair will be soft and silky (and will prematurely not fall out because of stress in struggle against the tutor of resources).

13

Re: How to restrict appetites of request to storage?

TaPaK wrote:

ptr128;
Judging by your description you want to push the button - "to make well". Curve requests are corrected was specific everyone

The button "to make well" in its various variations explicitly is described in articles of a blog of Alexander Juzhakova which is widely known in narrow circles as dear user of this forum under anybody Alex_496.

14

Re: How to restrict appetites of request to storage?

invm wrote:

it is passed...
For you to "restrict and"modify"- synonyms?

No, different methods to achieve that during calculation of necessary storage in the scheduler it turned out closer to real . It is possible to restrict the scheduler, forcing it to produce result not above restricting, and it is possible  that it already counted before it gets in the form of request about storage to the server.
Time speech came about a regulator of resources it is reached by business only upon termination of operation of the scheduler. Hence there is only an adjustment.

15

Re: How to restrict appetites of request to storage?

ptr128 wrote:

That is, over half of time while SSAS reads out results of request, SQL the server has simply a rest.

You look, how many the auxiliary files of type map it processes in cubes which are connected to that measurement for which you launched ProcessUpdate, on the disk monitor - and you  are surprised.
Lines with the fresh data from SQL is not all. Inside SSAS the black magic.

16

Re: How to restrict appetites of request to storage?

ptr128;
[spoiler]
Generally Juzhakov explained to you explicitly on SSAS in 2012 when you started to saw cubes in Power sale.
Really you did not study till now its blog up and down?
In SSAS he understands more all of us, together taken smile
[/spoiler]

17

Re: How to restrict appetites of request to storage?

Andy_OLAP wrote:

Therefore - at first preempt in the plane table of measurement.

The class recommendation, but not the possible. Does not climb.
There are operations and there are their lines. Up until that time all is class. Less 20 million lines. However each operation is compared with several others line by line, and partially and different dates. As a result on an output, after JOIN with the comparison table, there are 300 million lines. Also this result on a disk in case of plane table 12-16 (!) of hours is written. Thus that SSAS  it is all from VIEW even sequentially for 4-6 hours. If  at least on 2 large requests, there will be 3-4 hours. A question however...
As to DISTINCT they almost also are not present. As almost all analysts are carried out from the fact table above. As a matter of fact, in the comparison fact table, and payments/invoices and lines - separate measurements in plane tables. Requests of loading of groups of measures on sections clash on storage. And to force the client not to climb hands during the closed periods and not to correct there I cannot (
To prove that on one disk system in 5th RAID to hang up all DB from two SQL servers (including tempdb!) too yet it was not possible to me. Thanks though convinced tempdb to carry out separately, as allowed to refuse the plane table.

18

Re: How to restrict appetites of request to storage?

ptr128 wrote:

it is passed...
The class recommendation, but not the possible. Does not climb.
There are operations and there are their lines. Up until that time all is class. Less 20 million lines. However each operation is compared with several others line by line, and partially and different dates. As a result on an output, after JOIN with the comparison table, there are 300 million lines. Also this result on a disk in case of plane table 12-16 (!) of hours is written. Thus that SSAS  it is all from VIEW even sequentially for 4-6 hours. If  at least on 2 large requests, there will be 3-4 hours. A question however...

So you have the virtual fact table in 300 million lines over which you built some groups of measures (including with aggregations distinct count).
But incremental  you for some reason do not want the plane table. Therefore as at you be implemented the full reoutswapping for 12-16 hours can only (?!!!).
In that case, it is necessary to understand, why outswapping from view with all keys in the plane occupies 12 hours, and outswapping of a part of columns which are necessary for group of measures - 4-6 hours or 2-3 hours.
And  in the portions on 10-20 million lines in a cycle in the plane table. And further over it group of measures.
And differently as you generally check the facts cubed and in initial registration system, without creation of show-windows?
"These requests persistently demand on 25-45 gigabyte  and is obedient wait, while the server cannot select so much with it" - is not present, you after all throw requests to operations and their lines directly on registration system, users actively drive in new documents into this moment, all in locks, all brakes and waits and smothers each other.
It is not necessary so. These are a rake.

19

Re: How to restrict appetites of request to storage?

.
Do correct  cubed with restriction on a range of sampling and at you such  a forehead all of you will be equally solve. on an output 300  even if make the correct plans of requests
Architecture it is necessary to look upon

20

Re: How to restrict appetites of request to storage?

Andy_OLAP wrote:

But incremental  you for some reason do not want the plane table.

It is, but not always works. Regularly there is an editing of the old periods, up to shaggy years. And in comparisons. And because of literally several comparisons it is necessary  entirely section for a year. By the way DISTINCT COUNT group of measures only one, small with one measure (specially carried out), and with it problems at me just are not present.

Andy_OLAP wrote:

In that case, it is necessary to understand, why outswapping from view with all keys in the plane occupies 12 hours, and outswapping of a part of columns which are necessary for group of measures - 4-6 hours or 2-3 hours.

I know why. Because disk system SQL of servers is overloaded and costs not on RAID 1+0, and on 5 which noticeably brakes at record.

Andy_OLAP wrote:

And  in the portions on 10-20 million lines in a cycle in the plane table.

And became, but on 40-50 million, what not an essence as it is important.

Andy_OLAP wrote:

you after all throw requests to operations and their lines directly on registration system, users actively drive in new documents into this moment, all in locks, all brakes and waits and smothers each other.
It is not necessary so. These are a rake.

From what you it took? At first for 30-40 minutes all modifications from a productive DB  in a storage DB, and further all transformations and cube processing happens already from a storage DB to which, except SSAS during processing, generally nobody addresses.
P.S. Almost four years they worked generally to me without addressing. All was good, yet did not find out that cube update has not time to end from the termination of the working day prior to the beginning of the following. After a failure from the plane table on productive SQL the server, update began to be inscribed at 6 o'clock (2 replication and transformation, 2-4 - processing). And here at transfer of a DB of storage on separate SQL the server, processing became more slowly twice. And because of storage with what I also began a topic. Here such Santa Barbara...

21

Re: How to restrict appetites of request to storage?

Analysis Services Operations Guide wrote:

These two settings determine how much memory is allocated for the creation of aggregations and indexes in each partition. When Analysis Services starts partition processing, parallelism is throttled based on the AggregationMemoryMin/Max setting. The setting is per partition. For example, if you start five concurrent partition processing jobs with AggregationMemoryMin = 10, an estimated 50 percent (5 x 10 %) of reserved memory is allocated for processing. If memory runs out, new partition processing jobs are blocked while they wait for memory to become available. On a large memory system, allocating 10 percent of available memory per partition may be too much. In addition, Analysis Services may sometimes misestimate the maximum memory required for the creation of aggregates and indexes. If you process many partitions in parallel on a large memory system, lowering the value of AggregationMemoryLimitMin and AggregationMemoryMax may increase processing speed. This works because you can drive a higher degree of parallelism during the process index phase.

22

Re: How to restrict appetites of request to storage?

Maxx wrote:

Architecture it is necessary to look upon

It long and explicitly behind frames of the given forum. Branchy structure nevertheless from almost hundred measurements and tens sections.
It is clear that the architecture should be optimized. But on it already the will of the client - pays, I will be engaged. Does not pay - and remains.

23

Re: How to restrict appetites of request to storage?

ptr128;
And how many kernels of all at you for SQL servers? Whether it is used NUMA?

24

Re: How to restrict appetites of request to storage?

At 64 you in any way do not rest against storage at selection 25-45 for request.

25

Re: How to restrict appetites of request to storage?

Kolosov wrote:

At 64 you in any way do not rest against storage at selection 25-45 for request.

For one - I do not rest. For two it is parallel - already I rest. Here also there are they sequentially.
Kernels at SQL 8