Msmdsrv.ini
Hermila Farquhar
Administrators can modify many default configuration properties of an Azure Analysis Services (Azure AS) server resource, SQL Server Analysis Services (SSAS) server instance, or a Power BI workspace assigned to a Premium capacity. Modifying default configuration properties is not supported for Power BI Premium per user.
Properties pages in SQL Server Management Studio (SSMS) show a subset of those properties most likely to be modified. For Azure AS and Power BI, all applicable properties can be modified by using XMLA script in SSMS. For SQL Server Analysis Services, all applicable properties can be modified in the msmdsrv.ini file.
Power BI workspaces support modifying a limited subset of XMLA-based Analysis Services properties in the General, DAX, Filestore, Memory, and OLAP categories by using SSMS. When modified, the change applies only to that workspace.
Some XMLA-based workspace properties have equivalent capacity settings that can be set in the Admin portal, in Capacity settings/Workloads/SEMANTIC MODELS. These settings apply to all workspaces assigned to the capacity, however, workspace admins can override settings for a specific workspace by using SSMS.
Capacity admins can enable or disable the ability for workspace admins to modify XMLA-based workspace property settings. By default, this setting is enabled, meaning workspace admins can modify workspace property settings by using SSMS. Capacity admins can disable this setting in the Admin portal, in Capacity settings > Workloads > SEMANTIC MODELS > Observe XMLA-based settings.
For SSAS, server properties are in the msmdsrv.ini file. If the property you want to set isn't visible even after you show advanced properties in SSMS, you might need to edit the msmdsrv.ini file directly. For a default installation, msmdsrv.ini can be found in the \Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\Config folder.
I'll shoot something back to you! I actually just moved files to a new volume. I've updated Analysis Server Properties and moved all the files, even updated the Dump Directory in SQL Server Configuration Manager.
Everything works great...until....I try to delete the old olap folder. The msmdsrv.ini file at the old location is still being used by AS when the program starts. If I stop the service and rename this file, AS will not start. So somewhere in the config AS is still holding onto the location of this file which it depends on for start up.
I am working on an in-place upgrade from SQL Server 2012 Enterprise to SQL Server 2014 Enterprise, including an SSAS instance, and have been fighting with moving the files from the "11" folders to the "12" folders. I could not get the SSAS service to start after moving the files, and found the incorrect parameter in the Binary Path property of the service (under SQL Server Configuration Manager). I knew it must be in the registry somewhere because it was not in the msmdsrv.ini file.
As I understand from this MS Doc, most of the properties for SSAS configuration settings are in msmdsrv.ini which resides at default location drive:\Program Files\Microsoft SQL Server\MSAS12\OLAP\Config.
In my recent SSAS project, When I checked configuration file msmdsrv.ini, it has only one property as 1. The default value of this tag is 0 but here it is 1. And rest of all properties are missing.
Hi,
Can your server run correctly and process AS database functionally ?
From the description , I suspect the AS server file is corrupt and broken, in this case I would suggest you to reinstall it if it cannot work as expected.
CoordinatorShutdownMode property is to control shutdown of server execution. AND it is not a property for on-prem server . Some properties' description are current not open for public, so we could not know the full information, but normally these are not essential for us.
Regards,
Lukas
Yes, everything is working perfectly fine such as cube processing, synchronization, accessing cube via ssms/excel etc since long.
I had to change the value of property ServerSendTimeout and ServerReceiveTimeout , but there is nothing in msmdsrv.ini file.
By above comment, do you mean that there will not be any other place where all missing properties would have defined?
Or any place where i can at least get 'ServerSendTimeout' and 'ServerReceiveTimeout' properties?
When CoordinatorShutdownMode property controls server execution, being its value as 1 means , other remote connection will be able to access this server to shutdown?
Wy I am asking this because default value of this property is 0 but above image got value as 1.
Hi,
It is quite rare to have this, we are not clear why it happens.
But some property can only be set in .ini file., as the two required. You could try find .ini file in SQL developer edition and replace it to your server. Then change the property setings.
Sometimes SQL Server Analysis Services query response time is too slow and we want to improve the performance. How can we do so? What options are available to improve SQL Server Analysis Services performance? In this tip we will improve the performance by testing two parameters in the msmdsrv.ini file. Check out this tip to learn more.
For this tip, I used a demo cube which is 30 GB in size. Each partition contains 17 million rows and they are 30 partitions. In order to gather a baseline time needed for the query, I am going to run an MDX query with SQL Server Management Studio (SSMS):
However, in order to test the server time needed, it is better to use SQL Server Profiler to monitor the performance of individual queries. In this tip, we will use SQL Server Profiler to measure the query response time.
In order to start, we are going to change the value for the CoordinatorExecutionMode property in the msmdsrv.ini file. The CoordinatorExecutionMode indicates the number of parallel operations that the server will attempt. By default the value is 0, which means that the server will decide the value. Most of the time, this is the most convenient value, but sometimes it is not. In my scenario, I have a test server with 32 cores, so I want to test multiple values to see which one is best. In this case we are going to test the query at the beginning of this tip with the CoordinatorExecutionMode parameter of -8, -6, -4 and -2. In between each test we are going to clear the cache.
If you run the query you will notice that the execution time is 1 second once the query is stored in the cache. Whenever you run the same query again it will be faster than the original execution. This is great for the customer, but in my case we need to test the query improvements so we are going to create a XMLA script in SSMS by clicking on the XMLA icon as shown below:
Now we can run the query four times with the values of CoordinatorExecutionMode= -8, -6, -4 and -2 and clear the cache between each execution. The table below records the time required to run each query according to SQL Server Profiler. As you can see, the best query response time for my test environment is with a CoordinatorExecutionMode value of -6 and the average time was 26 seconds.
Now we are going to use a similar strategy with the CoordinatorQueryMaxThreads property in the msmdsrv.ini file. This property determines the number of threads per partition. Now let's try to test the response time with different values and clearing the cache between each execution. We found the following results:
In my scenario with my hardware and cube, the best query response time with the CoordinatorQueryMaxThreads = 22 and the CoordinatorExecutionMode = -6 parameters in the msmdsrv.ini file. Keep in my the best parameters depend on the database and the hardware. Also note that these parameters help mainly when you have multiple partitions, which in my case I had 30 partitions. You may need to test with different values in your own server and I am sure the results will be different. The main idea is to test the configuration results with different values and verify which options best suits your environment. Good luck!
I'm trying to switch the sql server analysis services to multidimensional since I'm getting an error in visual studio when I try to deploy my analysis project that says I can't deploy the model because the deployment server is not running in multidimensional mode. I tryed switching the deployment mode to 0 in my msmdsrv.ini file, but that did not change the outcome. I did restart the service already, but that didn't work either.This is the error I get:
I came across a strange permissions issue while attempting to edit the SQL Server Analysis Services file msmdsrv.ini locally on a SQL Server (Ref: Recommended settings from Optimal configuration settings for high concurrency in SSAS)
Users of on-premises Analysis Services will know that most of the useful server properties can be set in SQL Server Management Studio, some (such as MaxIntermediateRowsetSize) can only be set by editing the msmdsrv.ini file. How do you set these properties in Azure Analysis Services though, when there is no msmdsrv.ini file to edit?
The solution is to use an XMLA script to make the change. The easy way to do this is to open up the server properties dialog in SQL Management Studio by right clicking on your instance name in the Object Explorer and selecting Properties:
Late last week, I decided to change a few settings on my development Tabular Model Server. The first change was related to the VertipaqPagingPolicy value in an attempt to understand the setting a little better. The second was related to changing the DataDir path. Changing values of the Analysis Server properties can have adverse effects. It is best done only if you understand the implication AND if you have thoroughly tested it in a dev environment prior to making the changes in Production.