Microsoft KB Archive/813487

= FIX: RealTime Cube Performance May Decrease When Update Activity Occurs on the Non-Source SQL Server Database =

Article ID: 813487

Article Last Modified on 2/21/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services
 * Microsoft SQL Server 2000 Analysis Services
 * Microsoft SQL Server 2000 Service Pack 1
 * Microsoft SQL Server 2000 Analysis Services
 * Microsoft SQL Server 2000 Analysis Services

-



BUG #: 13900 (Plato7x)



SYMPTOMS
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.



CAUSE
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 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.

SQL Server 2000 Analysis Services Service Pack 2
Apply 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 3
Apply 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  8.0.0.761  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.dll 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 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



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

Keywords: kbbug kbfix kbqfe kbhotfixserver KB813487

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.