August 17, 2016 at 3:05 pm
We are doing some load testing, using 100 XMLA scripts containing MDX queries. We have discovered that not all 100 queries are running concurrently. Is there a setting in SSAS that can be configured to let all 100 run in parallel?
August 18, 2016 at 1:34 am
Interesting scenario, I am also keenly interested to learn about this.
August 18, 2016 at 2:20 am
imani_technology (8/17/2016)
We are doing some load testing, using 100 XMLA scripts containing MDX queries. We have discovered that not all 100 queries are running concurrently. Is there a setting in SSAS that can be configured to let all 100 run in parallel?
Think about what you are asking. In short, the binary answer to your question is no, there is not one setting that you can change that will make this happen. There are so many factors that can affect what happens when you throw 100 queries at an SSAS instance; server architecture, network architecture, cube design, data size, caching amongst many others - way down the list would be properties settings. I'd suggest that you read the following articles (they are a bit old but still valid and apply to multidimensional as well as tabular):
https://technet.microsoft.com/en-us/library/cc966527.aspx
https://msdn.microsoft.com/library/hh226085.aspx
https://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx
August 18, 2016 at 11:05 am
Don't think you want to do that as already stated. It's mostly a reflection of the thread pool settings and the settings have so many dependencies it's not recommended to make changes to most without MS support.
You can find information on the thread pool settings and determining the settings in the following:
Thread Pool Properties - https://msdn.microsoft.com/en-us/library/ms175657.aspx
Sue
August 18, 2016 at 2:53 pm
We modified the TreadPool\Query\MaxThreads property to 32 (we have 16 cores on our server). That did allow for more queries to run simultaneously, but not all 100 of them. We also tried our experiments with 50, 150, and 200 queries.
Here is our scenario: we might eventually have 200 concurrent users on an SSAS Tabular database. We need to figure out if Tabular can handle that type of workload and what type of equipment we need to make it happen. The experiment was a first step.
August 18, 2016 at 3:44 pm
I hope the additional steps include some bench marking and baselines for the system resources when doing this as several other things take a hit when you do this. CPU, context switches, memory usage (not just what's available for SQL), I/O processing etc.
Sue
August 18, 2016 at 3:51 pm
Good point, I'll pass that information along to the team.
Has anyone actually use SSAS Tabular with a lot of users? It seems like most of the people I've come across are either primarily Multidimensional or they have only used Tabular for POCs.
September 12, 2016 at 11:06 am
Sue_H (8/18/2016)
I hope the additional steps include some bench marking and baselines for the system resources when doing this as several other things take a hit when you do this. CPU, context switches, memory usage (not just what's available for SQL), I/O processing etc.Sue
Quick update: we have taken your advice and monitored CPU, server memory usage, etc. We have also started testing out multidimensional with 50 and 100 simultaneous queries to simulate multiple users. However, we have discovered that SSAS multidimensional isn't actually running all of the queries simultaneously. Instead, it is only running 5 at a time. I will assume this is a thread count issue, and I'm starting to research this. Do you have any advice on how we can get more than 5 queries to run at once on SSAS multidimensional?
October 27, 2016 at 10:06 am
Hi,
did you get any further with why only 5 quries run at once on ssas MD? I'm also having the same issue, so would be interested if you have any more info.
thanks
October 28, 2016 at 10:29 am
Sorry, no updates yet.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply