Best practice dictates that we use a separate server for each of these SQL Server Services. And this seems logical because otherwise these services will compete over server resources. In IT we call this competition: contention.
However there are some great reasons to put these services on one box:
- Licensing: SQL Server licensing can be expensive. And you need licenses for every server on which a services runs.
- Better resource utilization: Less servers, less power usage, less maintenance and -monitoring cost.
- Sometimes the network is the problem as a lot of data moves from the SQL Engine to SSIS or SSAS resulting in network congesting. If services run on the same machine, SQL Server uses the Shared Memory Protocol which is faster and leads to less network congestion.
In a scenario with all SQL/BI Services on one server we need some strategies to diminish the described contention. This is of course a big subject and I can only touch the basics in this post and give you some tips.
TIP 1: Limit the software and services on the server
This seems logical but I have been to several sites where developers remote desktop into the production server to do maintenance- and other jobs. This is certainly not a best practice. Better is to not install any client tools on the server and use a separate developer/test server to do maintenance.
TIP 2: Get an overview of your typical daily workload
In this image I have mapped a typical daily workload to the services needed to perform the workload:
- From 07:00 AM to 23:00 is the extended working day window. Some users start early, others end late: all are extensively opening SSRS reports and querying SSAS cubes.
- From 23:00 to 01:00 AM backups are running for all (source) systems. This is the period we do our own SSAS and SQL Server backups.
- From 01:00 AM tot 07:00 AM is our process window
In this period:
- we use SSIS to land data from our source systems into the staging database
- we use SSIS to load data from our staging database into the data warehouse
- we’ll process the dimensions and cubes of our SSAS databases.
- we warm the cache of our SSAS database and start distributing reports using SSRS subscriptions and SSIS for large Excel reports.
With this knowledge we have a clear understanding of potential contention issues. Furthermore we can schedule to stop and start services on a need to use basis. We can use the operating system commands NET START and NET STOP for this purpose.
TIP 3 Set Maximum Memory Setting for SQL Server
With the max server memory setting you can place a limit to the amount of buffer pool memory used by SQL Server. Which leads to the question to what amount should we limit the buffer pool memory?
During our extended working day window we will probably not benefit from a large buffer pool memory: Most queries will probably be answered by Analysis Services. However in the process windows we will benefit from a larger buffer pool memory. We can change this property during the day and schedule these changes by using the sp_configure system stored procedure. Based on 24GB of RAM, reserve 4 GB for the operating system and:
- During working day window set max server memory to 4 GB, reserve 8 GB for SSAS and 8 GB for SSRS.
- During the process window set max server memory to 10 GB, reserving 10GB for SSIS and 8GB for SSAS.
TIP 4 Set SSAS Memory Settings
The SSAS memory settings are available in the msmdsrv.ini file and in properties window of the server. If the value is between 0 and 100 than this means a percentage of total available physical memory. Above 100 means bytes. Change these settings to:
- Memory\HardMemoryLimit: from the default of 0 to 32
- Memory\TotalMemoryLimit: from the default of 80 to 28
- Memory\LowMemoryLimit:from the default of 65 to 24
This means that Analysis Services will start freeing up memory once its has reached the LowMemoryLimit threshold of 24% of physical memory. This process will get more aggressive if it reaches the other thresholds.
While you’re at it change some other memory properties:
- OLAP\Process\BufferMemoryLimit from 60 to 20
- OLAP\Process\AggregationMemoryLimitMax from 80 to 14
- OLAP\Process\AggregationMemoryLimitMin from 10 to 4
TIP 5 Set SSRS Memory Settings
In the RSReportServer.config file add the WorkingSetMaximum property and set it to 8GB (for our 24GB example):
Restart the reporting services service, so other memory related properties that are based on WorkingSetMaximum get set.
TIP 6 Monitor and adapt the memory setting
Use the settings in tip 3, 4 en 5 as a starting point but monitor memory usage and change these properties to map them to your workload.
TIP 7 Use Windows System Resource Manager to restrict CPU utilization
With Windows System Resource Manager (WSRM) you can create custom resource allocation policies to restrict resources (CPU, RAM) to an application. You can map your workload windows to policies and define these policies e.g.:
- policy ProcessData which runs when Analysis Services is processing data, set the CPU percentage for SQL to 45% and 45% for SSAS
- policy ProcessIndex which runs when Analysis Services is processing aggregations, set the CPU percentage for SQL to 10% and 80% for SSAS
Next you can setup calendar rules to apply the different policies to the correct time frames.
However this has the drawback that you have two calendar schemes: one in SQL agent and one in WSRM that you have to keep in sync. Furthermore it’s possible that there aren’t any CPU contention issues. My advice would be to monitor CPU usage and plan resource allocation policies if necessary based on the monitoring results.
TIP 8 Don’t write to the same disk you’re reading from
In the daily process window we will typically load large amount of data in our data warehouse:
- We load data from the source system in a staging database.
- From the staging database we load the data in the data warehouse.
- From the data warehouse we load the Analysis Services cubes.
In this scenario put the data warehouse database files on another drive as the staging database and the Analysis Services cubes.
In this post I focused on diminishing contention issues when using one server for your BI Solution running SQL Server, Analysis Services, Integration Services and Reporting Services. Key aspect is understanding your workload and the role the different applications play as well as the properties you can tweak to diminish contention.
- Microsoft SQL Server 2008 Analysis Services Consolidation Best Practices, by the SQLCat team
- SQL Server 2008 R2 Analysis Services Operations Guide, by the SQLCat team.
- Configure Available Memory for Report Server Application, on MSDN
- Best Practices for Data Warehousing with SQL Server 2008, on MSDN
- The Data Loading Performance Guide, by the SQLCat team.