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?