RiskFabric Processing hangs during cube processing

Products

• Information Centric Analytics
• Data Loss Prevention

Issue/Introduction

Introduction

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.

Summary

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.

Impact

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.

Environment

ICA 6.x

SQL Server 2016 SP2, SQL Server 2017, SQL Server 2019

Cause

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.

Resolution

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:

1. On the server hosting SSAS, open Notepad as an administrator by right-clicking the application executable (i.e., Notepad.exe) and selecting Run as administrator
2. From the File menu, select Open...
3. Navigate to the location of the msmdsrv.ini file. The default location is here:

   %SystemDrive%\Program Files\Microsoft SQL Server\MSASn.MSSQLSERVER\OLAP\Config\

   (where n refers to the version of SQL Server)
   
   
4. Set the explorer window to show All Files and double-click the file msmdsrv
5. Search for the following string:

   CoordinatorSafeJobUnblocking

6. By default, this key is set as follows:

   <CoordinatorSafeJobUnblocking>1</CoordinatorSafeJobUnblocking>

7. Edit this key by changing the bit to 0:

   <CoordinatorSafeJobUnblocking>0</CoordinatorSafeJobUnblocking>

8. From the File menu, select Save
9. Open Command Prompt as an administrator by right-clicking the application executable (i.e., cmd.exe) and selecting Run as administrator
10. If the RiskFabric cube is installed in the default Analysis Services instance, copy and paste or enter the following command:

    NET STOP "SQL Server Analysis Services" && NET START "SQL Server Analysis Services"

11. If the RiskFabric cube is installed in a named Analysis Services instance, copy and paste or enter the following command replacing the <instance name> variable with your instance name (e.g., (ICA)):

    NET STOP "SQL Server Analysis Services (<instance name>)" && NET START "SQL Server Analysis Services (<instance name>)"

12. Press Enter to execute the command and restart the Analysis Services service for the setting change to take effect

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.

Additional Information

Microsoft KB4589170 - FIX: Indefinite hang during cube processing after applying SSAS 2016 SP2 CU7

https://support.microsoft.com/en-us/topic/kb4589170-fix-indefinite-hang-during-cube-processing-after-applying-ssas-2016-sp2-cu7-a6d637ea-6bc3-f7f1-98d0-813dddd2a686