FIX: Prevent Analysis Services Processing Requests from Blocking Queries (827899)

SYMPTOMS

During incremental processing of a dimension or partition in Analysis Services, the server creates a copy of the object that is being processed and works on the copy until a commit request is received. This permits the Analysis Services server to continue answering queries against existing objects, thereby maximizing the availability of the data for users.

When a commit request is received, the Analysis Services server obtains a write lock on the objects that are processed in the current transaction and then replaces those objects with the updated versions. If queries are currently running against any of the objects that are to be replaced, the write lock request waits for the existing read locks that are held by the queries that are running to complete. Any subsequent read lock requests that the Analysis Services server receives for the objects in the commit request are queued behind the waiting write lock request for the commit operation.

When the object that is being processed is a dimension, a write lock must also be obtained on all cubes, partitions, virtual dimensions, and virtual cubes that contain that dimension. For example, if an incremental update is performed on the Product dimension in the Foodmart 2000 sample database, the following objects are also locked during the commit operation:

• Cubes
  • Sales
  • Warehouse
• Virtual Cubes
  • Trained Cube
  • Warehouse and Sales

Under typical circumstances, the elapsed wait time for the write lock requests that are associated with a commit operation is small. However, if a particularly complex query is being run by the server, the wait time for the write lock request may be significant because the Commit process requests a write lock on the affected objects. The write lock cannot be granted until the read lock for the currently executing query is released. The pending write lock request blocks all subsequent read lock requests that other queries make on established connections. These queries may appear to have stopped responding (that is, they appear to be "hung") because they cannot obtain the required read locks to run. New connection requests to the Analysis Services server may receive timeout errors if the requested connection is for the same database where the commit operation is pending. The connection timeouts occur because the server tries to read metadata about the database but cannot obtain read locks for the objects that are being processed.

Several options are available when a blocking query is detected. The option that you implement depends on the type of application that is in use and the business tolerance for "stale" data versus application availability. In some situations, it is more important for the long running query to complete and for other reports to be available than it is for the processing to be run immediately. The processing can be tried again after a short interval or at a time when the system is not as busy. For other applications, it may be more important for the data to be current than it is to deliver the report data for long running queries. One possible problem that may occur with this type of system is that frequent processing requests may make it impossible to run complex reports that require a longer "quiet period" on the server than is available because of processing requests. This hotfix addresses situations where the availability of data and the completion of currently executing queries is the primary concern.

Currently, no setting is available to control the time that a commit request waits before it fails and permits waiting query requests to run. This hotfix introduces a new registry setting that can be used to limit the commit request wait time. By using this registry value, an Analysis Services administrator can determine how many seconds a commit request will wait for the write lock to be obtained before it rolls back the processing operation. The registry entry is named CommitLockTimeout, and it must be placed under the following key: HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/OLAP Server/CurrentVersionThe entry is a REG_DWORD type, and the value is in seconds.

After you apply this hotfix, Analysis Manager returns the following error message if the number of seconds in the CommitLockTimeout setting expires before the write lock is granted on the requested objects:

   Date         Time   Version            Size    File name
   ------------------------------------------------------------
   13-May-2003  06:14  8.0.806.0         217,668  Msmdcb80.dll     
   23-Jul-2003  04:23  8.0.0.843       4,055,616  Msmddo80.dll     
   22-Aug-2003  06:18  8.0.853.0       1,032,768  Msmdgd80.dll     
   05-Aug-2003  01:36  8.0.823.0         172,608  Msmdpump.dll     
   13-May-2003  06:14  8.0.0.761       9,638,464  Msmdsgn80.dll    
   22-Aug-2003  01:07  8.0.853.0       1,831,492  Msmdsrv.exe      
   22-Aug-2003  06:18  8.0.853.0       2,077,264  Msolap80.dll     
   10-May-2003  08:05  8.0.820.0         213,069  Msolap80.rll