The Location of Query Processing
Control of the location of processing, as we shall see, can be managed from the Large Level Threshold property setting. While the large level value is a critical influencer of which location is selected for query processing, we can also control the location using other means, the chief of which is the Execution Location parameter. We will examine these settings in more depth as we proceed, but before we undertake to control the location of processing, we should understand the benefits and costs associated with the client / server processing options from which we can select. Some of the more important of these are summarized in Table 1.
|
Client vs. Server Processing: Costs and Benefits |
||
| Processing Location | Disadvantages |
Advantages |
|
Server |
Consumes more CPU and memory resources |
Results can be cached on the server, so that requests for the same result sets by other client applications are quickly available |
|
Client |
Takes longer to process |
Placing processing burden on the client relieves the server of virtually all resource requirements |
|
Potentially much greater network traffic, as large amounts of data are being dispatched from the server. |
||
|
Client-based processing means client-based caching. This results in a loss of rapid fulfillment of recurring requests, by multiple clients, for the same information. |
Table 1: Advantages and Disadvantages of Processing Location Options Let’s take a look at the first of these two prominent settings, Large Level Threshold property, with which we will begin a hands-on examination of managing processing location. The Large Level Threshold Property The Large Level Threshold property fixes the point at which a dimension level is too large to be sent to the client application as a single body. The property, by default, is set at the server at 1,000. We can use the property to help us to manage both CPU and memory utilization from the client perspective. Because the property stipulates the point at which level is “too large for the client,” it also determines the level at which the number of members from a given level force server-based execution of the PivotTable Service. The property, therefore, is highly useful to us in determining query execution location, which, as we are learning, is a significant tuning interaction point in the optimization of our MDX queries. If the number of members in a level is below the threshold we set in this property, it is sent to the client application in a single piece. The level is broken into smaller groups, which are sent to the client application as needed, if the number of members in the level under consideration is equal to or more than the threshold setting. Should the level contain a number of members smaller than this threshold value, the entire level will be sent to the client application. If the level under consideration contains a number of members that is greater than or equal to the value of the Large Level Threshold property, the level is incrementally sent from the server to the client application. While the default value for the Large Level Threshold property is 1,000, the minimum value for this property is 10. (If the value is set to less than 10, the value will reset itself at 10, automatically preventing any errors in processing). The Large Level Threshold property can be modified at the server, or can be specified by the client upon establishment of a connection to the server. Let’s explore the process for setting the parameter in the former case, with the following steps.
Figure 1: Select the Properties Option from the Context Menu The Properties dialog appears.
We have now set the number of members a dimension level must contain to be processed as a large level. The range here can be from 1 – 10,000 (default is 1,000, as we mentioned earlier), and it is meaningful in the context (the number of level members) with which Analysis Services classifies dimension levels into large and small categories only. Keep in mind, as we have already discovered, that large levels are not sent to the client unless they are specifically requested, but small levels are sent to the client even if the entire level is not requested.
We can, as we have mentioned, set the Large Level Threshold property via an MDX query we generate from a client application. When we define the large-level threshold at the client application, we do so by specifying the setting as a connection property. Because the property value is set upon opening the connection, this approach for setting the large-level limit does not provide a means of controlling the location of query processing on a query-by-query basis, any more than the same setting within Analysis Services provides for ad hoc location management. The Large Level Threshold property therefore allows for setting the property from the client, much as it is set from within the Analysis Services properties that we explored above. A connection string example is as follows: Large Level = 750; Thus we see that the control of the location of processing can be managed from the Large Level Threshold property setting. While the Large Level value is a critical influencer of which location is selected for query processing, we can also control the location using other means, the chief of which is the Execution Location parameter, which we will take up in our next section.
Continues…
| |
