Article ID: 827899
Article Last Modified on 11/21/2006
APPLIES TO
- Microsoft SQL Server 2000 Analysis Services
- Microsoft SQL Server 2000 Analysis Services
BUG #: 14125 (Plato7x)
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:
RESOLUTION
Service pack information
To 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 information
The 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 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
Note 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-bit
For 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
STATUS
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.
MORE INFORMATION
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
Keywords: kbbug kbfix kbqfe kbsqlserv2000presp4fix kbhotfixserver KB827899
