Knowledge Base

Views may take a long time to display data or you may receive time-out errors when you select a "My" view such as "My Active Contacts" in Microsoft CRM

Article ID: 894460

Article Last Modified on 7/25/2006

APPLIES TO

Microsoft CRM 1.2

SYMPTOMS

When you access one of the "My" view options in Microsoft CRM, such as My Active Contacts, My Active Leads, My Active Accounts, or My Active Opportunities, you may experience one of the following symptoms:

The view that you select takes more than five seconds to display records.
You may experience Microsoft SQL Server blocking issues.
If you try to update a record or save a record, and if many thousands of records exist in the tables that correspond to the view that you selected, you may experience time-out errors.

CAUSE

This problem occurs because of the way that Microsoft CRM returns records for a particular view. When you access one of the "My" view options, Microsoft CRM generates an SQL statement that has the following properties:

The statement returns all the records that are shared or owned by the currently logged on Microsoft CRM user.
The statement includes a statement to select records that are based on the following criteria:
- The currently logged on Microsoft CRM user
- Microsoft CRM team ownership

Note Team ownership of records is not supported in Microsoft CRM. However, you can configure team ownership of records by using a Microsoft CRM SDK-based application.

In a large Microsoft CRM dataset that contains hundreds of thousands of records, this SQL statement may cause SQL Server query plans not to use certain indexes. Specifically, the query plans might not use any one of the indexes that might exist for the OwningUser fields in the Microsoft CRM tables. The Microsoft CRM tables are in the Organization_Name_MSCRM database.

You typically experience this problem more frequently when many hundreds of Microsoft CRM users access Microsoft CRM at the same time.

RESOLUTION

A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next version of Microsoft Business Solutions CRM that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the hotfix. For a complete list of Microsoft Product Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

Installation information

Important If any records are assigned to teams by using a Microsoft CRM SDK-based application, you must assign those records to Microsoft CRM users before you apply this hotfix. This hotfix removes the ability to view any records that are owned by teams when you use one of the "My" view options. This hotfix removes references to the Teambase tables from Microsoft CRM views. Additionally, this hotfix removes coalesce statements for the OwnerID fields in the Microsoft CRM database tables.
Apply this update on computers that are running the Microsoft CRM 1.2 server. You must apply this hotfix by using the credentials of a valid licensed Microsoft CRM user.
Important Before you apply this hotfix, back up the Microsoft CRM databases and the Microsoft CRM Active Directory organizational unit. For more information, see "Chapter 10: Backing Up and Restoring your Microsoft CRM System" in the Microsoft CRM Implementation Guide.
This hotfix examines Microsoft CRM for the presence of records that are owned by Microsoft CRM teams. If any records are owned by Microsoft CRM teams, the hotfix Setup program does not change the Microsoft CRM views. You must first assign the team-owned records to Microsoft CRM users by using the Microsoft CRM SDK-based application.
This hotfix adds indexes to the Organization_Name_MSCRM database for the OwningUser field in all the following tables:
- AccountBase
- ActivityBase
- AnnotationBase
- ContactBase
- ContractBase
- IncidentBase
- InvoiceBase
- LeadBase
- OpportunityBase
- QuoteBase
- SalesOrderBase
These indexes use the following naming convention where tablename is one of the tables from this list:

ndx_tablename_894460_OwningUser
After you apply this hotfix, you can update the Microsoft CRM views by running the MSCRMCoalesce.exe program.

MSCRMCoalesce.exe is located in the following folder, where drive is the drive on which Microsoft CRM 1.2 is installed:

drive:\Program Files\Microsoft CRM\Server\Bin

To run MSCRMCoalesce.exe, click Start, click Run, type C:\Program Files\Microsoft CRM\Server\Bin\MSCRMCoalesce.exe in the Open box, and then click OK. A window displays the progress of various tasks that the program is performing. When the window closes, the Microsoft CRM database is updated, and the database indexes are applied to the tables that are listed in this section.

You do not have to rerun MSCRMCoalesce.exe after you perform schema customizations to the Microsoft CRM forms. This includes the addition of new fields, or the addition or removal of custom fields.
Applying this hotfix reduces the load on SQL Server every time that a "My" view option is accessed by a Microsoft CRM user. If you have a Microsoft CRM database that has only several thousand records, you might not notice the effects of this hotfix. This hotfix has a larger effect on a database that contains hundreds of thousands or millions of records, and that is accessed by hundreds of Microsoft CRM users.

Prerequisites

To install this update, you must have installed Microsoft CRM version 1.2.

Removal information

You cannot remove this hotfix.

Restart requirement

You do not have to restart the computer after you apply this hotfix. However, the following services may have to be restarted after you apply this hotfix:

Internet Information Services (IIS)
Microsoft CRM Security Service

File 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
   ------------------------------------------------------------
   17-Jan-2005  05:59  1.2.3297.123   16,088  Crmmsg.dll       
   14-Sep-2005  20:29  1.2.3297.101   10,976  Kb894460.dll     
   14-Sep-2005  20:32                170,728  Mscrmcoalesce.exe  
   25-Jan-2005  21:21  1.2.3297.204  121,568  Mscrmcustom.dll  
   14-Sep-2005  21:05                  2,465  Crmhotfix.cdf
   25-Jan-2005  21:21  1.2.3297.204  121,568  Mscrmcustom.dll

MORE INFORMATION

This hotfix may not resolve all Microsoft SQL blocking issues that you might experience by using Microsoft CRM. If you experience other Microsoft SQL blocking issues, you may have to contact Microsoft Product Support Services (PSS).

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems

271509 How to monitor SQL Server 2000 blocking

For more information about the terminology that is used to describe Microsoft product updates, 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

887283 Microsoft Business Solutions CRM software hotfix and update package naming standards

Keywords: kbmbsmigrate kbpubtypekc kbqfe kbhotfixserver KB894460

Microsoft KB Archive/894460

Contents