FIX: Prevent Analysis Services Processing Requests from Blocking Queries
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 14125 (Plato7x)
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:
- Virtual Cubes
- Trained Cube
- Warehouse and Sales
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:
Lock manager error [Internal error]
Service pack informationTo resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack
Hotfix informationThe English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
Date Time Version Size File name ------------------------------------------------------------ 13-May-2003 06:14 8.0.806.0 217,668 Msmdcb80.dll 23-Jul-2003 04:23 220.127.116.113 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 18.104.22.1681 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.rllNote Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.
SQL Server 2000 Analysis Services 64-bitFor additional information about a hotfix build that is designed to be applied on a server that is running SQL Server 2000 Analysis Services 64-bit, or to find out if a fix is scheduled to be included in SQL Server 2000 Analysis Services 64-bit Service Pack 4, click the following article number to view the article in the Microsoft Knowledge Base:
822985 Hotfix Information for Build 8.0.861.0 of SQL Server 2000 Analysis Services 64 bit
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates
Article ID: 827899 - Last Review: 02/27/2014 21:19:57 - Revision: 3.3
Microsoft SQL Server 2000 Analysis Services
- kbnosurvey kbarchive kbhotfixserver kbqfe kbqfe kbsqlserv2000presp4fix kbfix kbbug KB827899