Default SSAS behavior can sometimes result in small queries being slowed down by concurrently running big queries.
Recently in working with a customer, we observed the most common queries when run by themselves took about 1 second and some ad-hoc queries against their 110 GB cube took about 3 minutes. When running both together, small query times slowed down to several minutes. This slowdown prevented the customer’s project from being successfully deployed.
There is a configuration parameter that can alter the scheduling of the tasks to allow separate queries to run more independently. The SSAS version that includes the configuration parameter is described in KB article 922852. We worked with the customer to develop a table of configuration parameter values and the resulting query times. Some configuration values resulted in the small query running concurrently with big queries in about the same time as when run independently.
The scenario we are focused on is a big query that takes substantially more time than a small query, and when run concurrently the small query is observed to substantially slow down. The root cause is the big query spends time in one or more storage engine requests, reading from many partitions having tens or hundreds of gigabytes. Many tasks are queued to a thread pool to handle partitions and segments inside the partitions.
This scenario and its solution may apply to other customers. However the effectiveness of a proposed solution is based on the root cause of the slow down. if the root cause is different there might not be a benefit from the same solution.
The steps below can be used to determine similarity to the scenario being discussed. When working with CSS you may be guided to repeat the steps with a different configuration parameter.
Steps to reproduce scenario:
1. Start small query, wait for completion, record time.
2. Start big query, wait for completion, record time.
3. Restart server.
4. Start big query (but do not wait for completion).
5. Wait 5 seconds then start small query. This wait time might need to be adjusted to get consistent times, for example to 10 or 15 seconds. The effect depends on the nature of the big query.
6. Wait for queries to complete, record times.
Customers having this circumstance can work with Microsoft Customer Support Services (CSS) and mention CoordinatorQueryBalancingFactor. Please keep in mind server properties that are not public can only be changed after consulting with Microsoft Support.