Microsoft Dynamics CRM 4.0 slows to unacceptable levels when you process e-mail messages by using the Microsoft Dynamics CRM E-mail Router

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

SYMPTOMS

When you process e-mail messages by using the Microsoft Dynamics CRM E-mail Router, Microsoft Dynamics CRM 4.0 slows to unacceptable levels. Therefore, all activity views cannot be viewed. This problem occurs if the following conditions are true:
  • You process many e-mail messages.
  • The instance of Microsoft SQL Server contains many records. For example, the server contains more than one million ActivityParty records and ActivityPointer records.

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

Installation information

Install this cumulative update rollup on computers that are running the Microsoft Dynamics CRM 4.0 Router components.

To maximize the performance of this cumulative update rollup, you must add additional SQL Server indexes to the <OrganizationName>_MSCRM database. <OrganizationName> is a placeholder for the name of the Microsoft Dynamics CRM organization. You can do this before or after you apply this hotfix.

Before you add the additional SQL Server indexes, verify that the additional SQL Server indexes do not already exist in the MSCRM database. To do this, run the following commands against the MSCRM database:
sp_helpindex ActivityPointerBase
sp_helpindex ActivityPartyBase
sp_helpindex EmailBase
If the additional SQL Server indexes exist, these commands will result in output that resembles the following:
index_name index_description index_keys
cndx_ActivityPointer clustered, unique located on PRIMARY ActivityId, StateCode, ActivityTypeCode
ndx_ActivityPointerCover1 nonclustered located on PRIMARY ScheduledEnd, RegardingObjectTypeCode
Verify that none of the additional SQL Server indexes have columns that are the same as the ones in the indexes below. If a duplicate index is created, it will make the Microsoft Dynamics CRM SQL performance slower rather than faster.

To create the additional SQL Server indexes, run the following SQL statement in SQL Server Management Studio. Change the USE <OrganizationName>_MSCRM statement to the actual organization and database name.

USE <OrganizationName>_MSCRM
GO

CREATE NONCLUSTERED INDEX [CRM_ActivityPartyBase_EmailRouter1] ON [dbo].[ActivityPartyBase] 
(
[ActivityId] ASC,
[ParticipationTypeMask] ASC,
[PartyId] ASC
)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [CRM_ActivityPartyBase_EmailRouter2] ON [dbo].[ActivityPartyBase] 
(
[ParticipationTypeMask] ASC,
[PartyId] ASC
)
INCLUDE ( [ActivityId],
[ActivityPartyId]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [CRM_ActivityPartyBase_EmailRouter1] ON [dbo].[ActivityPointerBase] 
(
[ActivityTypeCode] ASC,
[statecode] ASC,
[actualend] asc,
[statuscode] asc
)INCLUDE ([activityid], [modifiedon], [deletionstatecode])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [CRM_ActivityPointerBase_EmailRouter2] ON [dbo].[ActivityPointerBase] 
(
[statecode] ASC,
[ActivityTypeCode] ASC,
[deletionstatecode] asc
)INCLUDE ([ActualEnd], [StatusCode], [ActivityID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [CRM_EmailBase_EmailRouter1] ON [dbo].[EmailBase] 
(
[activityid] asc,
[deliveryattempts] asc,
[DirectionCode] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
GO

Prerequisites

You must have Microsoft Dynamics CRM 4.0 installed to apply this cumulative update rollup.

Restart requirement

If you are prompted to restart the computer, you must restart the computer after you apply this cumulative update rollup.

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 Dynamics 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: 959248 - Last Review: January 20, 2012 - Revision: 4.2
APPLIES TO
  • Microsoft Dynamics CRM 4.0
Keywords: 
kbmbsemail kbfix kbautohotfix kbexpertiseinter kbsurveynew kbmbsmigrate kbqfe kbhotfixserver KB959248

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