- About the Series
- Introduction to Performance Tuning
- Understanding How Queries Are Processed
- Types of Intervention
- Large Level Threshold Property
- Location of Query Processing
- Optimization of Set Operations
- Importance of Optimal Arrangement
- Expression Arrangement
- Caching and Optimization
- Other Performance Enhancement Options
- A Parting Word
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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. By default, the property 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 the 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, a significant point of intervention 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 that 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 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 avoiding any errors in processing. The Large Level Threshold property can be modified at the server or it can be specified by the client upon establishing a connection to the server. Let's explore the process for setting the parameter in the former case:
Start Analysis Manager (Start, Programs, Microsoft SQL Server, Analysis Services, Analysis Manager).
Expand the Analysis Servers folder by clicking the + sign on its left.
Right-click the Server (MOTHER in this case, as shown in Figure 1), typically named after the machine on which it resides.
Select Properties from the context menu that appears, as shown in Figure 1.
Figure 1 Select the Properties option from the Context menu.The Properties dialog box appears.
Select the Environment tab.
Type 750 in the Large level defined as box, as shown in Figure 2.
Figure 2 Modification: large level threshold defined as 750
members.
)
Figure 1)
Figure 2We have now set the number of members that a dimension level must contain to be processed as a large level. The range here can be from 1–10,000 (the 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 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.
Click OK.
A dialog box appears that announces we must restart the Analysis Server for changes to take effect, as shown in Figure 3.
Figure 3 A restart of analysis services is required for changes to take
effect.Click OK.
Click the Console Item in the Console top menu and then select Exit to close Analysis Services.
The new threshold level will be enacted upon our next start of Analysis Services.
)
Figure 3We can 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 setpoint 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 setpoint 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 previously. A connection string example is as follows:
Large Level = 750;