Symptoms
Assume that you use an instance of Microsoft SQL Server 2016 Analysis Services. While more and more global scope objects are being cached, those caches are never cleaned up. This may result in a continuous growth of memory and eventually the server may crash.
The global scopes are cached based on the security requirements for each user. Therefore, you will usually only encounter this issue when you experience one of the following scenarios:
-
You have dynamic expressions (for example, UserName(), CustomData()) in the model. This is not mandatory to see the problem, but it is a common cause. When dynamic expressions are present, each unique user needs to build their own unique global scope. This causes a significant increase in the number of global scopes that need to be created and cached.
-
You have a complex model that has many metadata and security. This influences the cost of memory per scope object. As the number of cached global scopes increases, the memory footprint grows based on the cost of memory per scope object.
-
You have lots of unique users connecting to the server. For example, PowerBI.com may refresh tiles in its dashboard for each user on a frequent schedule. When this happens, Analysis Services receives lots of connections and queries for each unique user and the number of global scopes spikes up and uses up lots of memory.
Note: After you apply this hotfix, the SQL Server version will be 13.0.2170.0.
Resolution
Cumulative update information
The fix for this issue is included in the following cumulative update for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Workaround
To work around this issue, manually run a <ClearCache> XMLA command to the database on an appropriate schedule. This would allow all the global scope caches to be cleared and the memory would reduce again. This option will cause some performance cost when you connect the next time because you will not be able to reuse the cached global scope.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
Learn about the terminology that Microsoft uses to describe software updates.
