You experience slow performance when you move to views that include custom attributes in Microsoft Dynamics CRM 4.0

Article translations Article translations
Article ID: 2306918 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

Assume that you try to view an entity that contains more than 100,000 records in Microsoft Dynamics CRM 4.0. When you move to views that include custom attributes, you experience slow performance. The Microsoft Dynamics CRM queries take lots of time to return results.

CAUSE

This problem occurs because Microsoft Dynamics CRM generates joins for the Microsoft SQL queries that disallow indexes on custom attributes. Therefore, the queries cannot be used effectively.

RESOLUTION

This problem is fixed in the latest cumulative update rollup for Microsoft Dynamics CRM 4.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
949256 Microsoft Dynamics CRM 4.0 updates and hotfixes


Note This problem was originally fixed in Update Rollup 13 for Microsoft Dynamics CRM 4.0.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2267499 Update Rollup 13 for Microsoft Dynamics CRM 4.0 is available


Note Before you install this hotfix, you must have Update Rollup 12 for Microsoft Dynamics CRM 4.0 installed.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2028381 Update Rollup 12 for Microsoft Dynamics CRM 4.0 is available


After you install this hotfix, you must regenerate the views for each organization by using a tool that is included in this hotfix. To do this, follow these steps:
  1. Determine the organization names by running the following statement in Microsoft SQL Server Management Studio against the MSCRM_Config database:
    SELECT UniqueName FROM Organization
  2. Run the tool for each organization that you determined in step 1. To do this, run the following command that resembles the following at a Command Prompt:
    c:\program files\Microsoft Dynamics CRM\Server\Bin\Tools\RegenerateViewsTool\Microsoft.Crm.Se.RegenerateViewsTool.exe <OrganizationName>

    Note In the command, the <OrganizationName> placeholder represents the actual name of the organization for which you want to regenerate the views.

    After you run the tool, the fix can be verified by looking at the entity view definitions for any entities that are customized. To do this, in Microsoft SQL Server Management Studio, run a statement against an entity view. For example, for the Account entity, run the following statement:
    sp_helptext Account
    In the From statement in the returned text, the join type of the AccountExtensionBase table is listed as the inner-join type instead of the left-join type.
  3. To fully use this hotfix, you can capture Microsoft SQL traces by using Microsoft SQL Profiler or other tools, such as the SQL Nexus tool. To obtain the tool, visit the following website:
    SQL Nexus tool
    By using the tools, you can capture the queries that are generated when you access Microsoft Dynamics CRM views that have custom attributes included. By analyzing these queries in Microsoft SQL Server Database Tuning Advisor, you can obtain a recommended list of indexes and statistics to implement.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

For more information about software update terminology, 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
For more information about Microsoft Business Solutions CRM software hotfix and update package terminology, click the following article number to view the article in the Microsoft Knowledge Base:
887283 Microsoft Business Solutions CRM software hotfix and update package naming standards

Properties

Article ID: 2306918 - Last Review: September 23, 2010 - Revision: 1.0
APPLIES TO
  • Microsoft Dynamics CRM 4.0
Keywords: 
kbqfe kbfix kbexpertiseinter kbmbsmigrate kbsurveynew KB2306918

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com