Mixed Workloads Part 2
I am presenting at the Ft. Worth SQL Server Users Group in April. Details forthcoming but it is basically going to be on running mixed workloads(OLTP and DSS) on the same server. This is part two of what I will be pulling into the presentation. Click here for part one.
In this part we will look at the typical hardware configurations used in part one(logshipping, mirroring, replication etc.). and what a better configuration would be especially if you leverage some new features in SQL Server 2008 to run mixed workloads.
Unless you invest the time to create a real reporting solution with an ETL, you end up with the following solutions based off of the methods described in part one.I see it all the time. On top of that, I see reporting queries still running on the production server because there is a need for real time data.
So what do we get with this solution? Most reporting queries are offloaded from production. However, there are a lot of con’s. The schema usually is not optimized for reporting. There is overhead in getting the data to the reporting server. The data is stored twice on disk and more importantly, memory. Finally, resource utilization is usually lopsided. For example, first thing in the morning the reporting server may be hammered while production traffic is just ramping up. During peak production traffic, the reporting server can be underutilized.
I submit to you that combing reporting and production is a better configuration if you do not invest in an ETL solution that creates a real reporting database. In the next post, we will talk about features to optimize this configuration but lets talk about what we gain just by using this architecture.
- The hardware is doubled and the amount of data is cut in half.
- In the configurations that this would replace, the memory gain is huge. Unless the reporting database is optimized for reporting, the data pages in memory are going to be the same. This allows the same page in memory to satisfy both OLTP and reporting traffic.
- This configuration gives more cores for parallel plans that are common in reporting.
- Most of all, it make available hardware that could otherwise be idle.
Note: I use direct attached storage(DAS) in these examples because that is where the biggest gains are to be had. However, the same benefits apply if you are on an enterprise level SAN with some caveats.
Note 2: This series is generalized and your mileage may vary based on your particular environment, business requirements and workload.
In part three, we will talk about features that will help optimize a mixed workload on a single instance with feature in SQL Server 2008.
This content is published under the Attribution-Share Alike 3.0 Unported license.
Comments
My RSS
-
My LifeStream
My Presence
