Author: Carl Rabeler
Contributor: Josh Caplan
Reviewers: Prem Mehra, Denny Lee, John Desch, Wayne Robertson, Steve Pontello, Thomas Kejser
This technical note describes the consolidation options, considerations, and best practices for Microsoft® SQL Server® 2008 Analysis Services that the SQL CAT team has derived from working with Analysis Services customers. In this technical note, we also discuss an Analysis Services load balancer solution that one of our internal customers uses to load balance queries from Microsoft Excel® and SQL Server Reporting Services clients across multiple query servers to increase query performance and provide availability.
Our customers are consolidating Microsoft SQL Server 2008 Analysis Services for the following reasons:
In working with our customers, we have implemented either one or both of the following Analysis Services consolidation solutions:
Frequently, an Analysis Services database is located on a dedicated Analysis server and Analysis Services processing queries are sent across the wire over TCP/IP or named pipes to a SQL Server relational data warehouse on its own, dedicated server. In this scenario, network congestion can be the processing performance bottleneck, particularly when the volume of data being loaded into the Analysis Services database for each processing operation is large. If this is the case, the ASYNC_NETWORKIO wait will appear high in the SQL Server wait stats list.
Network congestion can be addressed by improving the network connection performance in one or more of the following manners:
Co-locating the SQL Server relational data warehouse with the Analysis Services instance on a single physical server is particularly appealing as servers become more powerful (multiple cores and more memory) and less expensive. On these more powerful servers, dedicating one powerful server for the relational data warehouse and another powerful server for the Analysis Services instance may not fully utilize the processor and memory resources on either server. Co-locating the relational data warehouse with the Analysis Services instance may enable better utilization of available hardware resources. Co-location requires that you address and resolve resource contention issues. These issues are discussed later in this paper.
To more fully utilize these more powerful and less expensive servers, it is tempting for IT to consolidate multiple small and medium-sized Analysis Services databases onto a single physical server. In many environments, there are numerous small to medium-sized cubes spread out across the enterprise that do not fully utilize the hardware resources of the servers on which they are running. Furthermore, managing and monitoring this sprawl requires significant human effort and power resources to keep running on a 24 x 7 basis. This phenomenon is exacerbated as older servers are retired and newer servers are purchased.
Consolidating Analysis Services databases onto a single physical server reduces the number of servers, which results in better utilization of hardware and a reduction in power usage. Managed appropriately, such consolidation also results in a reduction in management and monitoring costs by the IT department.
Each of these consolidation solutions addresses specific needs. These consolidation solutions can be used separately or can used in combination. There are some common considerations for these consolidation solutions and some unique considerations for each solution.
Consolidation will result in competition for server resources such as processor, memory, and I/O resources. In some circumstances, it may also result in locking issues or inefficient use of existing resources. When you are consolidating separate systems onto a single server platform, proper control of the utilization and sharing of resources is vital to the success of the consolidation effort. The following resources are affected by consolidation:
Co-locating the SQL Server relational data warehouse with the Analysis Services instance on a single physical server can result in significant contention for hardware resources during processing as both the SQL Server relational instance and the Analysis Services instance each require processor, memory, and I/O resources to complete the processing operation. The SQL Server relational engine and the Analysis Services multidimensional engine will each, by default, attempt to utilize all available server resources. This may result in contention for these resources during processing. For example, the SQL Server relational engine may utilize a substantial percentage of memory on the server and be slow to give up these allocated memory resources to Analysis Services when memory resources are needed during the indexing and aggregation phase of the Analysis Services processing operation.
Additionally, this resource contention may negatively affect querying, if querying is occurring concurrently with processing. For example, contention for memory resources may result in the clearing of Analysis Services caches that would otherwise be used to improve query performance. Additionally, locking during processing may result in short queries taking longer than expected and new connections failing when a long-running user query blocks the commit phase of a processing operation. When processing is affecting query performance, utilizing separate query and processing servers can resolve this resource contention. The use of separate query and processing servers is discussed later in this article.
Co-locating multiple Analysis Services databases onto a single server can result in several types of resource contention issues:
Co-locating multiple Analysis Services databases onto a single server can result in several types of maintenance issues. When you consolidate multiple databases into a single physical server, down time related to operating system or hardware issues will negatively affect more databases and consequently more users. For example, applying an operating system service pack or upgrading hardware could take all of the databases on the consolidated physical server offline for a period of time. Furthermore, if you consolidate using a single instance of Analysis Services for all databases, instance management (such as SQL Server service packs and cumulative updates) can also impact availability of the databases in a consolidated environment.
The best practices discussed in the remainder of this paper describe how we have resolved these resource contention issues.
The following best practices describe the consolidation solutions that we have successfully implemented in customer environments to realize the benefits and mitigate the risks inherent in each of these consolidation solutions. Utilizing these best practices, we have increased processing performance and hardware utilization, decreased power consumption, simplified manageability through standardization, and increased availability.
The challenge when the relational data source is collocated with the Analysis Services instance is to realize the benefits of improved processing performance and increased hardware utilization without impacting user queries. We implemented this consolidation option in two different ways to avoid resource contention with user queries.
A processing window is a period of time during which the Analysis Services instances on a single server are resolving few or no user queries and one that is long enough for all required processing operations to complete. A typical scenario is an environment where querying is not occurring overnight (although the definition of overnight is getting shorter). The benefit of this scenario is that the co-location of the SQL Server relational warehouse with the Analysis Services instance on a single physical server enables you to reduce the size of this processing window by increasing the speed of processing. If you have such a processing window, you will be able to complete processing during a period in which there is little or no resource contention on the server related to querying. However, as you place more Analysis Services databases on a single server, you need either more server resources or more time.
When you have such a processing window, the resource contention is only between the SQL Server relational engine and the SQL Server analysis engine for processing operations, and it does not affect a significant number of user queries. Solving contention between these two processes during the Analysis Services processing operation requires the application of a number of techniques.
You can restrict the amount of memory and processors resources used by the SQL Server relational engine to ensure sufficient resources are available to Analysis Services. You can also use disk layout techniques to limit I/O contention between the SQL Server relational engine and Analysis Services. For more information about limiting I/O contention, see Resolving Disk Contention and Maximizing I/O Resources.
The SQL Server relational engine uses memory in its buffer pool and memory outside of its buffer pool.
To manage the size of the SQL Server buffer pool, you can use:
You can use these options together. For example, if you have users querying the relational data warehouse for reporting purposes and Analysis Services querying the relational data warehouse for processing purposes, you can use the max server memory setting to set an upper limit on usage of buffer pool memory and then use Resource Governor to guarantee the Analysis Services processing queries (or the reporting queries) a specified amount of memory resources.
Important: You should use the capabilities built into the SQL Server relational engine to restrict its use of memory; you should not use Windows® System Resource Manager (WSRM) for restricting memory use by the SQL Server relational engine. For more information about WSRM and SQL Server, see Scaling Up Your Data Warehouse with SQL Server 2008 and Consolidation Using SQL Server 2008.
You can restrict the use of processor resources by the SQL Server relational engine either by limiting the use of all processor resources by the SQL Server relational engine or by allocating specific processors to the SQL Server relational engine:
Important: On large multi-processor servers, you may need to set MAXDOP to 4 (or less) to prevent excessive parallelization of Analysis Services processing queries by the SQL Server relational engine.
Note: If you are running SQL Server 2008, you can also use Resource Governor to throttle CPU consumption by processing queries.
Tip: You can use WMI to dynamically configure these resource utilization settings during processing and then reconfigure them when processing completes. For more information, see WMI Provider for Configuration Management Concepts.
You cannot directly restrict the SQL Server analysis engine to specific memory and processor resources.
Analysis Services does not support processor affinity, and the max memory settings for Analysis Services are soft settings, not hard settings. By soft, we mean that when specified maximum memory settings are exceeded, objects in cache will be evicted to attempt to avoid exceeding the memory settings, but you can still blow through these memory settings under a variety of circumstances. For more information, see the SQL Server Analysis Services 2008 Performance Guide. There is a HardMemoryLimit setting in the msmdsrv.ini file in SQL Server 2008 Analysis Services that enables you to specify a threshold beyond which Analysis Services will start cancelling operations if the memory exceeds a specified threshold. However, the use of this setting will not prevent Analysis Services from exceeding the specified threshold under all circumstances.
The buffered file mode that Analysis Services uses can cause the Windows system file cache to grow quite large and starve the relational engine of all memory resources during Analysis Services processing, resulting in an apparent “hang” during processing. If you encounter this scenario, you can limit the amount of system file cache used by Analysis Services by modifying the LimitSystemFileCacheSizeMB setting in the msmdsrv.ini file. Limiting the amount of system file cache used by Analysis Services will ensure that memory remains available for the relational engine.
WSRM is our preferred approach to place hard restrictions on the usage of memory and processor resources used by the Analysis Services process.
Important: When you utilize WSRM to place a hard restriction on the usage of memory resources by Analysis Services, the Analysis Services process will still see the total amount of memory on the server. Therefore, you need to adjust the values for the TotalMemoryLimit and LowMemoryLimit properties to reflect the appropriate percentage of the total physical memory on the server that WSRM is allowing Analysis Services to utilize in order for these settings to function as designed.
Note: If you utilize the Preallocate setting in the msmdsrv.ini file, do not grant the Lock Pages in Memory local user policy to the Analysis Services service account if you utilize WSRM. If you use Local System as the service account, be aware that Local System has this privilege by default.
You can resolve disk contention between the relational engine and Analysis Services by utilizing separate LUNs, utilizing one set of LUNs for Analysis Services and another set for the relational engine. For more information, see Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services.
For many consolidated environments, you may want to separate the Process Data and Process Index phase of the Analysis Services processing operation to improve the performance of the entire processing operation. Separating these processing operations enables you to reconfigure the SQL Server relational engine after the Process Data phase of the processing operation completes to enable all server resources to be made available to Analysis Services during the Process Index phase of the processing operation. The Process Index phase is much more processor and memory intensive than the Process Data phase from a single thread perspective. By running the Process Index phase separately, you can parallelize the indexing operation to make use of all available resources, because the Process Index phase does not require access to the SQL Server relational engine. For more information, see the Analysis Services Performance Guide.
In the absence of a processing window, we implemented this consolidation solution by performing all processing on physical servers not utilized for querying (called processing servers) and directing all user queries to one or more physical servers not utilized for processing (called query servers). Utilizing separate servers for querying and processing eliminates the resource contention during processing that reduces query performance while enabling you to maximize processing performance. For more information about the benefits of utilizing multiple query servers, see Utilizing Multiple Query Servers.
Utilizing a dedicated processing server and one or more query servers may, at first, seem inconsistent with consolidation. Indeed, it is if you are only looking at consolidating a very small number of Analysis Services databases. However, if you are looking to consolidate dozens of older, department-sized servers onto a small number of newer, server-class servers, consolidating to a small number of servers works very well – each server with its own standardized role.
Note: When utilizing a dedicated processing server, we have frequently consolidated the relational data warehouse on the processing server.
This solution requires you to use one of several options to synchronize the newly processed objects from the processing server to the query server(s):
For more information about these options, see Analysis Services Synchronization Best Practices.
In order to realize the benefits of increased hardware utilization when co-locating multiple Analysis Services databases onto a single server, we have implemented this consolidation option either with a single Analysis Services instance or with multiple Analysis Services instances. When consolidating multiple databases, we have generally utilized multiple query servers and one or more processing servers, and we have co-located the relational database source with its corresponding processing server. When we utilize multiple query servers, we use a number of techniques to load-balance user queries.
Important: We have only implemented this type of consolidation with small to medium sized Analysis Services databases; consolidating multiple very large or very complex databases onto a single instance simply has not made sense from a resource contention point of view. While the definition of a small or medium sized Analysis Services database is at best imprecise, our definition includes databases against which individual queries do not consume a significant portion of the server resources to resolve and which are primarily constrained by concurrency rather than query complexity. For more information about resource usage by queries, see the SQL Server Analysis Services 2008 Performance Guide.
We have rarely seen the need to spread Analysis Services databases across multiple instances of Analysis Services on a single physical server to improve query performance. We have used multiple instances of Analysis Services on a single instance when users are querying a database in one instance while the same database in a second instance is being synchronized. This configuration is used to enable user queries to be directed to the newly synchronized database on the second instance without downtime.
Note: Multiple instances of the SQL Server relational engine are sometimes used to manage the priority of queries. With Analysis Services, you can use the CoordinatorQueryBalancingFactor and CoordinatorQueryBoostPriorityLevel properties to prioritize long-running queries versus short queries. For more information, see the SQL Server Analysis Services 2008 Performance Guide.
When choosing between querying all databases in a single instance versus spreading the databases across multiple instances on a single server, consider the following:
With multiple Analysis databases on a single physical server, the processing of any single database can affect the query performance of any query against any database on the server. A long-running query against a database can prevent the processing of that database from completing, unless the long-running query is forcibly terminated. Furthermore, within a single instance, a long-running query can prevent a processing operation from completing its commit phase and block subsequent queries as well as all new connections until the commit operation completes successfully or times out.
When consolidating databases onto a query server, we have generally utilized one or more processing servers and multiple query servers, and we have co-located the relational database source with its corresponding processing server. These servers provide the following benefits:
Synchronizing a processed database from its processing server to multiple query servers adds minimal additional complexity or synchronization time compared to synchronization with a single query server. You can synchronize all query servers either simultaneously or in two (or more) groups. For example, you could synchronize a processed database to your first group of query servers while directing all new queries against that database to the second group of query servers. If you use Server Sync, this approach allows existing queries against that database to complete while synchronization is occurring and new queries to execute against the servers that are not being synchronized – to maximize query performance. After synchronization is complete for the first group, all queries against that database can then be directed to the first group of query servers so that the newest data is being queried while the second group of query servers is synchronized. This approach is illustrated in figures 1 and 2.
Figure 1: Synchronizing First Query Server
Figure 2: Synchronizing Second Query Server
When you utilize multiple query servers, you need to load balance the user queries across the query servers for performance and availability. We are taken several approaches to load balance user queries across multiple query servers.
One approach is to load balance at the presentation layer. This approach utilizes multiple Web servers, with some Web servers directing MDX queries to one query server and other Web servers directing MDX queries to a second query server. This approach is discussed in Scale-Out Querying with Analysis Services and is illustrated in figure 3.
Figure 3: Query Scale-Out Topology with Multiple Web Servers
This approach works well as long as the workload directed to each Web server is evenly balanced, but it does not provide load balancing where that is not the case. For example, suppose some users are connecting from Excel to one query server and through Reporting Services to a second query server. In this scenario, the load from each set of users may well not be balanced.
Another approach is to use network load balancing software, such as Microsoft’s Network Load Balancing (NLB) software solution, or network load balancing hardware, such F5.
NLB is an optional component in the Windows Server 2008 and Windows Server 2008 R2 operating systems and is built into Windows Server 2003. NLB load balances network traffic among multiple servers in an IP cluster. For more information, see Windows Server 2008 NLB and Windows Server 2003 NLB.
NLB is a software solution to create a cluster of machines, each with two IP addresses – a private unique one and a virtual one that is shared by all machines in the cluster. Each machine in the cluster runs an algorithm that determines whose turn is next in responding to requests. These machines also exchange heartbeats with each other, so they all know if one server goes down and won’t allocate any more requests to such a machine until it is healthy again.
NLB supports a number of affinity options depending on your version of Windows Server. When no affinity is specified, all network requests are load-balanced across the cluster without respect to their source. In general, when affinity is enabled, all client requests from the same IP address are responded to by the same cluster host.
However, NLB has several limitations when working with multiple Analysis Services query servers, particularly when each query server has a subset of the Analysis Services databases to which queries are being directed (for example, all databases are located on at least two servers for availability, but all databases are not located on all servers):
Due to these limitations, NLB is not our recommended solution for load balancing user requests across multiple Analysis Services query servers.
Network load balancing hardware solutions have more sophisticated affinity options, such as cookie persistence, and they include dynamic load balancing algorithms that track server performance. However, these solutions do not solve the problem of removing a single database on an Analysis Services query server from the cluster while continuing to direct user queries to the other databases on that query server. In addition, these hardware solutions do not, out of the box, monitor the availability of the Analysis Services process itself.
Due to these limitations, using network load balancing hardware solutions is not our recommended solution for load balancing user requests across multiple Analysis Services query servers.
When using multiple query servers with multiple databases on each server, we have found the need to load balance queries at the database level as well as the need to monitor service availability at the Analysis Services level. To accomplish this, we wrote a custom Analysis Services load balancing solution (ASLB) that consists of a load balancing Web service backed by a SQL Server load balancing metadata database to load balance MDX queries across multiple query servers. The metadata database contains information about each Analysis Services query server to which queries can be redirected by the Web service.
Note: This load balancing Web service can be clustered using NLB for availability, and the load balancing metadata database can also be mirrored for availability.
The ALSB solution solves the problems previously discussed as follows:
Figure 4: Individual Query Using the Analysis Services Load Balancer
Figure 4: Individual Query Using the Analysis Services Load Balancer
Figure 5: Querying and Processing Using the Analysis Services Load Balancer
Important: This solution uncovered several bugs that have been fixed in the most recent cumulative updates to SQL Server 2008 Service Pack 1 (SP1). To receive these bug fixes, you should patch your SQL Server using Service Pack 1 Cumulative update 4 or newer. For more information about the newest cumulative updates, see Knowledge Base article #970365.
Note: The code for the ASLB solution as well as an installation and configuration document is available for download on the Microsoft download center at http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c7737e80-8dfd-4cb6-a27e-69bbe03b2f9e.
The ASLB code is using the database as its source for the string comparison - so, the string can be either just the database name - or, can be any other string - such as a concatenation of the database name and the cube name (you may have to use an underbar or a dash rather than a slash though - do not remember exactly how it is currently coded).
Pingback from SSAS White Paper list - reading for "SSAS Maestro" program | Vidas Matelis Analysis Services Blog
Vidas Matelis összeállított egy egész jó listát azokról a dokumentumokról, amelyek szükségesek lehetnek
Pingback from SSAS Maestro program – my experience so far | Vidas Matelis Analysis Services Blog
We've been seeing a lot of interest in optimizing SQL Server for virtualization private cloud deployment lately. Below is an attempt to put useful links to recent SQL Server virtualizaiton, consolidation and private cloud resources in one place, divided
We've been seeing a lot of interest in optimizing SQL Server for virtualization private cloud deployment
Bulut çözümleri pek çok kurumun uygulamaya başladığı ya da uylamak üzere
This is an excellent document but an excitment is missing if some one wants to upgrade from SQL Server 2000 Analysis Services to SQL Server 2008 or R2. Can i connect SQL Server 2000 Analysis Services from SQL 2008 R2 SSMS.
In short i am looking for document to upgrade\connect\migrate from SQL Server 2000 Analysis Services to SQL Server 2008 R2.
Any help will be great.
How is this going to work with SSAS 2012? Our BI team is planning on upgrading to 2012 fairly soon after release so I need to know if ASLB will work with 2012 or if we need to go another route.
Jay, this link might be a good starting point for you if you haven't found something already.