I am working on a server that houses both an OLTP workload and a data warehousing/reporting workload. The OLTP system requires sub-second (in the milliseconds) response time while the reporting workload has a threshold of minutes. The issue is some users will run multiple reports during peak transactional activity in our OLTP environment, thereby slowing the OLTP queries down. On their own, the OLTP queries tend to run 20-30ms, but when many reporting/data warehouse queries are fired up simultaneously the OLTP queries begin to run in the 4-5 second range. Our bottleneck appears to be CPU, as this is where all of our waits are.
I would of course like to separate the workloads on two different servers, but I’m wondering if we could get a quick improvement via use of Resource Governor.
I’m considering two approaches: (1) set a minimum allotment of CPU to the OLTP system so that it is guaranteed 25% resources, or (2) max and cap out the reportingdata warehouse queries so they cannot take more than 50% of the CPU.
Does anyone here have any practical experience with Resource Governor that would guide me in one of these two directions? Or is there another usage pattern that you’d suggest instead?
Supporting a hybrid workload requires more than just resource governance.
The docs call this scenario Real-Time Operational Analytics, and it’s a bunch of features all working together.
First you need row versioning to ensure that the report users don’t create blocking in the database. So set the database to READ COMMITTED SNAPSHOT or force the reports to run with SNAPSHOT isolation.
With locking solved, you then need resource governance and isolation to prevent the report users from using excessive server resources. For many workloads the OLTP part doesn’t need data file physical IO, but does depend on log file IO. So physical separation of data and log can help prevent IO from reports from interfering with OLTP. For resource governance I would start by capping the reporting workload’s CPU, IOPS, Memory, and perhaps maxdop.
The next thing you need is to make the reporting workload less expensive. For this use some combination of updatable nonclustered Columnstore indexes, filtered indexes, indexed views, and Columnstore compression delay.
And the final thing is that you need to use Query Store to monitor the workloads and provide visibility into the plans for the report developers.