The following is a root cause analysis describing cube processing issues experienced after installing cumulative updates 7 through 13 for Microsoft SQL Server 2016 SP2. These issues were caused by a patch made available by Microsoft in the early July timeframe which has led to adverse effects on cube processing in SQL Server Analysis Services (SSAS). Note that this issue also affects builds of SQL Server 2019 prior to CU9.
After applying the CU7 patch in the environment, during ICA nightly processing, SQL Server Analysis Services is adversely affected and causes cube processing to hang indefinitely. After checking numerous settings in Analysis Services and rebooting the server without a change in behavior, manually processing the cube proves successful, however only in a sequential fashion. By nature, serial processing proved to be much more time consuming, increasing the overall cube processing time from 30 minutes under the former parallel setting, to an hour and a half. This behavior was later replicated in Bay Dynamics’ internal environment by applying the same SQL patch. After removing that patch, cube processing was allowed to continue successfully, further underscoring the point that this issue was directly related to the security update provided by Microsoft.
Duration of the nightly job is negatively impacted by tripling the time it takes to process the cube, resulting in delayed updates to the dataset within Analyzer.
ICA 6.x
SQL Server 2016 SP2, SQL Server 2017, SQL Server 2019
Processing times are affected after installing the security update for SQL Server 2016 SP2 CU7, published 7/7/2019 to address CVE-2019-1068, as per the Microsoft Support site.
https://support.microsoft.com/en-us/help/4505222/security-update-for-sql-server-2016-sp2-cu7
This affects all cumulative updates for SQL Server 2016 SP2 through CU13 and all cumulative updates for SQL Server 2019 through CU8.
This issue is resolved by upgrading SQL Server to the latest build (SQL Server 2019 should be upgraded to CU9) and disable the CoordinatorSafeJobUnblocking setting on the Analysis Services server by following this procedure:
%SystemDrive%\Program Files\Microsoft SQL Server\MSASn.MSSQLSERVER\OLAP\Config\(where n refers to the version of SQL Server)
CoordinatorSafeJobUnblocking
<CoordinatorSafeJobUnblocking>1</CoordinatorSafeJobUnblocking>
<CoordinatorSafeJobUnblocking>0</CoordinatorSafeJobUnblocking>
NET STOP "SQL Server Analysis Services" && NET START "SQL Server Analysis Services"
NET STOP "SQL Server Analysis Services (<instance name>)" && NET START "SQL Server Analysis Services (<instance name>)"
Alternatively, the process in the attached document should be followed as a workaround to the issue described above, which will enable the cube to process successfully. Note that as previously mentioned, this may increase the duration of cube processing; however, it will allow data in Risk Fabric to update.
Microsoft KB4589170 - FIX: Indefinite hang during cube processing after applying SSAS 2016 SP2 CU7