Lesson 2: Managing Resources

The server computer, network, and client computer resources that you use for your data warehouse play an important role in determining the ultimate success or failure of the data warehouse. In this lesson, you will learn about some important resources that you should focus on when you implement your data warehouse and some configurable server resource settings that may improve or degrade your OLAP Server performance.

After this lesson, you will be able to:

• Describe optimization strategies for configuring a server

Estimated lesson time: 30 minutes

Optimizing Your Configuration

If you want Microsoft SQL Server and OLAP Server to operate at maximum efficiency, there are some steps that you must take to configure your systems.

Fast Network Connections

Because large amounts of data are typically retrieved from the data warehouse when building cubes, you should ensure that there is adequate network bandwidth between the database server hosting your data warehouse and the computer running SQL Server OLAP Services. A projection of necessary bandwidth can be made using the Network Monitor in test labs. Once this estimate is known, you can determine if the network segment on which you intend to place the servers can support this load. Once your data warehouse is in production, it is important to monitor the load on this segment and adjust bandwidth if this segment becomes overtaxed.

Separate Computers for OLAP Servers and SQL Server

In general, for production applications, if you can provide adequate network bandwidth between systems, you should run SQL Server and OLAP Services on separate computers. This allows the systems to process in parallel, independent of one another. For example, users querying a cube would not be affected by a data-loading process running on the data warehouse.

You can run SQL Server and OLAP Services on the same computer if this computer has adequate resources to meet the demand. To provide adequate performance in production, these machines often require multiple processors and large amounts of RAM. A smaller server can be used if you have small databases and light query loads. A single-computer configuration may also be appropriate in situations in which there is insufficient network bandwidth available. In these situations, the bottleneck created by the network may hurt performance more than dividing processing across machines can help.

NOTE

---

You ll need one license for each server for both SQL Server and OLAP Services if you run them on separate computers.

Three primary subsystems impact server performance: RAM, processor, and disk. If your server is experiencing a large volume of disk activity, you have to begin by determining the reason. If the disk activity is coming from virtual memory, it is important to add additional RAM to resolve this issue. If the disk activity is the result of standard data utilization, the best gain can come from implementing an intelligent hardware RAID controller with its own RAM. If disk activity is not a problem, but you are experiencing slow performance, the poor performance may be because of excessive processor utilization. The Windows NT Performance Monitor will provide the facts necessary to help you make the proper hardware upgrade.

Optimizing Your Server Configuration

You can configure several properties to optimize performance of the server running OLAP Services.

Server Configuration

Although it is important to have adequate hardware to support your application, it is equally important to have the server software configured correctly. You can configure many of these options in the Server Properties dialog box in OLAP Manager. To open the Server Properties dialog box in OLAP Manager, right-click your server, and then click Properties on the pop-up menu. Figure 14.5 shows the Environment tab of the Server Properties dialog box.

Figure 14.5 The Environment tab of the Server Properties dialog box in OLAP Manager

Maximum Number of Threads

This option specifies the number of threads that can simultaneously utilize the CPUs in the server computer. The default is twice the number of CPUs in the server computer, with a valid range of 1 to 1,000. These settings may be appropriate for servers that are performing additional functions, but they are typically too low for dedicated OLAP Servers. It is particularly important to increase this number if you have more partitions than processors.

Large Level Defined as

This option specifies the minimum number of members that a dimension level must contain to be processed as a large level. OLAP Services classifies dimension levels into large and small categories based on the number of level members. Small levels are sent to the client application in their entirety, whereas large levels are sent to the client application incrementally. Handling large levels in this manner conserves cache memory on the client. The default value is 1,000 members, and the valid range is from 1 to 10,000 members. You may want to increase this value if most clients have a large amount of RAM and good network bandwidth.

NOTE

---

Client applications can override this setting by specifying a different value in the Large Level Threshold connection string parameter when opening a connection to the OLAP Server.

Minimum Allocated Memory

This option specifies the amount of memory allocated exclusively to OLAP Services. Actual memory usage may exceed this value. The default is to use one-half of the memory of the server computer as the minimum allocated to OLAP Services.

Memory Conservation Threshold

This option specifies the memory threshold near which memory usage decreases. When the amount of memory used by OLAP Services approaches this threshold, usage decreases as memory management becomes more efficient. Actual memory usage may exceed this value unless the default value is used. The default is all of the memory of the server computer. For servers that are providing more than one function, the default number will need to be reduced to prevent excess swapping.

Read-Ahead Buffer Size

This option determines the maximum amount of data placed into memory during each read of the database. Setting this option as high as possible minimizes the number of disk accesses. The default is 4MB. Figure 14.6 shows the Processing tab of the Server Properties dialog box.

Figure 14.6 The Processing tab of the Server Properties dialog box in OLAP Manager

Process Buffer Size

This option determines how much data is processed in memory before an input/output (I/O) is performed. The larger the value, the fewer I/Os are required. The default is 4MB.

Lesson Summary

Due to the large volume of data typically associated with a data warehouse, you should provide adequate network bandwidth between the servers in your data warehouse. This can be done through faster network connections or through a dedicated segment. Depending on the number of users who query your OLAP Server, you may need to add a separate computer to run OLAP Services.

You can configure a number of server parameters for OLAP Services using the OLAP Manager. You should start by using the default values for these parameters and carefully monitor the effect of changing parameters.