SQL Server OLTP vs. data warehouse performance tuning

This article discusses two broad categories of SQL Server processing -- online transaction processing (OLTP) and data warehouses -- and outlines performance-tuning tips for each.

SQL Server delivers a number of functions for small to large organizations with needs ranging from internally developed applications to third-party off-the-shelf software. With all systems, data collection, entry and analysis at some level is required to meet organizational needs.

As user demands and the volume of data increase, it is imperative that all systems are properly configured and tuned based on the processing needs to meet the organizational requirements. This article will discuss two broad categories of SQL Server processing -- online transaction processing (OLTP) and data warehouses -- and will outline performance-tuning tips for each platform.

The first category is OLTP systems. These are operational systems that support daily organizational needs such as customer relationship management (CRM), enterprise resource planning (ERP) and order entry (OE). In general, OLTP systems have a large number of users with short transactions with users responsible for front line business operations. The second broad platform is data warehouses. For the sake of this article, a data warehouse environment is generally categorized as Reporting and OLAP (online analytical processing). Data warehousing systems traditionally have fewer users that typically focus on tactical and strategic analysis, although a recent trend has been an increased user base for decision making at all levels of the organization.

Based on this comparison, the OLTP system and the data warehouse require different configurations in order to achieve high performance and ultimately a return on investment (ROI) for the organization.

As such, the following recommendations make these goals a reality:

Split the OLTP and data warehouse environments

Observation: Since the database design and processing needs are completely different, ensure that both systems are operating on a dedicated server and storage tuned for the system. I have observed a number of systems where reporting is conducted on the same database or server as the OLTP transactions. Since they each have nearly opposite needs, the design does not properly support reports, and as soon as a report is issued, the entire operations staff suffers poor performance.

Recommendations: First and foremost, set up a project to gather the user requirements for reporting and analysis. Once the needs are understood, consider one of the following options as a second phase to the original project:

  1. Separate the servers, and design and implement a data warehouse environment. Approach the project in stages to move the most critical and time-consuming reports to lessen the impact on the OLTP environment.
  2. A similar option to number one is to leverage replication, DTS, Log Shipping to have a second server for reporting purposes. In addition, depending on how the data is replicated, the second server could be used as a backup server to the production environment if a failure on the production server occurs.
  3. If introducing a second server exceeds the budget, and if the OLTP environment does not support a 24x7 operations team, schedule the reports during off hours and have them delivered to the users on a daily basis. Another similar option is to aggregate the data during off hours and only grant the users rights to the pre-aggregated data for querying as opposed to the raw data. This will save the server resources during the day for exploratory queries that cannot be pre-aggregated or sent via a standardized report.

Set the max degree of parallelism configuration

Observation: Typically, max degree of parallelism is neither properly configured nor completely understood. During a typical installation of SQL Server, by default, max degree of parallelism is enabled for all of the logical processors on the machine. For data warehouse environments with low concurrency, this is a beneficial setting because a parallel execution plan is selected by SQL Server. Unfortunately, for OLTP environments with high concurrency, this setting yields lower then expected performance since the parallel query plan does not benefit short transactions.

Check the query plans

Observation: With the speed that applications need to be developed, query plans are typically not tuned because of the time involved. As long as the query functions properly, the code is promoted to the production environment and the needs for the next project are addressed.

What are some of the performance tuning differences you have found in SQL Server between OLTP and data warehouse environments? Let me know, then stay tuned for the next installment of SQL Server performance-tuning tips when we will offer more observations from the field and simple recommendations that will improve overall system performance.

About the author:
Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning Expert. Ask him a question here.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning