FIX: RealTime Cube Performance May Decrease When Update Activity Occurs on the Non-Source SQL Server Database
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 13900 (Plato7x)
Performance of queries against RealTime cubes in SQL Server 2000 Analysis Services may decrease during periods of update activity on the computer that is running SQL Server and that holds the source database for the Analysis Services RealTime cube.
It is expected that update activity on the dimension and fact tables for the RealTime cube may have a negative affect on the performance of queries to the RealTime cube. This problem occurs because the Analysis Services server restarts any in-process queries for the RealTime cube when an update occurs on the fact or dimension tables in SQL Server. Although the restart of the query may result in longer response times for the query, it insures the accuracy of the results sent to the client for the query.
However, with this problem, you notice the decrease in the Analysis Services query performance if the update activity occurs on tables in a database other than the one that serves as the source for the RealTime cube.
The Analysis Services server uses the SQL Server trace mechanism to monitor for updates to tables involved in a RealTime cube. The Analysis Services server filters the RealTime trace on SQL Server using the ObjectID properties of the tables involved in the RealTime cube. The ObjectID property for SQL Server objects is guaranteed to be unique in a single database, however you can have objects with the same ObjectID property in different databases on the same instance of SQL Server.
Analysis Services (before you apply this fix) does not filter the SQL Server trace on the DatabaseID property. As a result a commit of an update to a table in any database, including TempDB, can cause the Analysis Services server to incorrectly flush the data cache and restart in-process queries for the RealTime cube if the update occurs on a table with the same ObjectID property as one of the dimension or fact tables involved in the RealTime cube.
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.
SQL Server 2000 Analysis Services Service Pack 2Apply this hotfix build on a server that is running SQL Server 2000 Analysis Services Service Pack 2:
Date Time Version Size File name -------------------------------------------------------------- 18-Jan-2003 02:04 8.0.727.0 1,782,340 bytes Msmdsrv.exe 18-Apr-2001 06:23 2000.80.382.0 815,676 bytes Sqldata.dll 18-Jan-2003 02:18 2000.80.534.0 590,396 bytes Sqlsort.dll
SQL Server 2000 Analysis Services Service Pack 3Apply this hotfix build on a server that is running SQL Server 2000 Analysis Services Service Pack 3:
Date Time Version Size File name -------------------------------------------------------------- 12-Feb-2003 02:49 8.0.767.0 1,024,576 bytes Msmdgd80.dll 27-Jan-2003 07:47 18.104.22.1681 9,638,464 bytes Msmdsgn80.dll 14-Feb-2003 22:32 8.0.768.0 1,827,396 bytes Msmdsrv.exe 12-Feb-2003 02:49 8.0.767.0 2,069,072 bytes Msolap80.dllNote 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 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:
822017 FIX: Hotfix Information for Build 8.0.810.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.
Article ID: 813487 - Last Review: 02/27/2014 21:16:39 - Revision: 3.4
Microsoft SQL Server 2000 Analysis Services, Microsoft SQL Server 2000 Service Pack 1
- kbnosurvey kbarchive kbhotfixserver kbqfe kbqfe kbfix kbbug KB813487