Tuning the Performance of Your Microsoft Business Solutions CRM 1.2 Installation (March 08, 2005)
Published: March 9, 2005
Please note: Portions of this transcript have been edited for clarity
Introduction
Experts: Greg Gawryluk and Chad Rexin, Microsoft Business Solutions CRM escalation engineers with the Microsoft CRM Support team
Related Web sites: Microsoft CRM Online community and Microsoft CRM newsgroup
James Z (Moderator):
Welcome to today’s chat.
Before we begin, I’ll have our experts introduce themselves:
Chad Rexin [MSFT] (Expert):
I’m Chad Rexin, a Microsoft CRM Escalation Engineer with the Microsoft CRM Support team. I’ve worked with Great Plains since 1995 and with Microsoft since the Great Plains merger with Microsoft in 2001. I focus on escalated cases with the Microsoft CRM product, working closely both with customers and the Microsoft CRM product team in resolving issues.
Greg [MSFT] (Expert):
My name is Greg Gawryluk. Here is a quick bio.
* Software Engineer for 11 years
* With MSFT for 5 1/2 years as 1) developer, 2) CRM Developer Support and now, 3) CRM Escalation Engineer
* As, CRM Escalation Engineers, in general terms, we are responsible for triaging bugs within the CRM product and identifying root cause code and submitting Hotfix Requests. If you have installed a CRM hotfix, we likely reviewed, developed, or tested the code.
Chad Rexin [MSFT] (Expert):
Since this is a chat related to Microsoft CRM Performance, I'd like to point out a couple of sites that show the various Microsoft CRM 1.2 hotfixes. Many of these hotfixes do address performance both in the Microsoft CRM Server and the Microsoft CRM Sales for Outlook products.
Chad Rexin [MSFT] (Expert):
Microsoft CRM 1.2 hotfixes that have been released:
http://support.microsoft.com/default.aspx?scid=kb;en-us;838986
Update Rollup for Microsoft CRM 1.2
KB Link: http://support.microsoft.com/kb/892949
Download Location for Update Rollup for Microsoft CRM 1.2: http://www.microsoft.com/downloads/details.aspx?amp;amp;displaylang=en&familyid=127a274d-bc20-4e2f-be0d-1247455748a2&displaylang=en
Greg [MSFT] (Expert):
Q: Thanks for taking the question - If this is off-topic just let me know...I heard that there is a fix/patch/utility for the CRM security service - that improves the performance by forcing the system to re-create the Security Descriptors - is that accurate?
A: Scott...if you review http://support.microsoft.com/kb/835292, you will find the Hotfix that you are referring to. You will need to contact Microsoft Support to have the Hotfix distributed as it is not a self service hotfix.
Greg [MSFT] (Expert):
Q: Thanks for taking the question - If this is off-topic just let me know...I heard that there is a fix/patch/utility for the CRM security service - that improves the performance by forcing the system to re-create the Security Descriptors - is that accurate?
A: You can also review the following KB relative to the "CRM Admin Tool" that will help in security-related performance issues:
http://support.microsoft.com/kb/893102
You will want to look specifically at the Repair Object Security section as I believe this is the section that you are likely referring to in re-calibrating the security descriptors.
Chad Rexin [MSFT] (Expert):
Q: When our CRM Administrator makes changes to the business unit settings, it impacts performance. What can be done to minimize the impact on system performance when this is performed?
A: Microsoft CRM works using a security model that is based off of the same security model that Active Directory uses. This means that each record in Microsoft CRM has a column called a SecurityDescriptor. This SecurityDescriptor field holds the security information that tells the Microsoft CRM application (through the use of the Microsoft CRM Security Service) which users should have access to that record and what level of access that they should have. This is validated against each Microsoft CRM user's security group membership in Active Directory. That is a small background on what is happening behind the scenes. That being said, if an administrator makes changes to existing Microsoft CRM Security roles, creates new Microsoft CRM Security roles, or copies an existing Microsoft CRM security role, it will trigger an update to every record in the Microsoft CRM database that contains a Security Descriptor. Most of the time you will not even notice this type of operation, but with a larger data set > 20,000.
To minimize the impact, changes to Microsoft CRM Security roles should be done in the evening after regular business hours so that the changes can take effect before Microsoft CRM users are in the system. I would also recommend contacting Microsoft CRM Support for the hotfix (http://support.microsoft.com/kb/835292/EN-US/), which will help increase the performance of the update operation that updates the SecurityDescriptors. In addition to this, performance tuning should be done on the Microsoft SQL Server on the MSCRM databases. This includes operations such as reindexing the data and if needed, defragmenting the data. There is a great whitepaper, Optimizing the Performance of Microsoft CRM 1.2, available at http://www.microsoft.com/downloads/details.aspx?FamilyID=E1A00070-2E5A-410A-881F-D859F8474933&displaylang=en, which includes a sample script that can be scheduled as a regular SQL job to reindex the tables in the MSCRM database.
Greg [MSFT] (Expert):
Q: Is there a hotfix that addresses the current functionality when re-assignment of an account also reassigns all opportunities from that account to the new owner?
A: Unfortunately, the behavior you describe is by design. I actually triaged that request a couple of months back and it was per spec that this behavior is occurring (i.e., by design....albeit, arguably poor design). I am not aware of any changes planned for CRM 2005 either.
Greg [MSFT] (Expert):
Q: Does publishing a change, using Deployment Manager, impact system performance?
A: Publishing can have an impact on performance as it is making multiple metabase table updates. However, we have not seen publishing take an inappropriate amount of system resources. Are you experiencing performance issues when publishing?
Chad Rexin [MSFT] (Expert):
There are some general tools that we typically use in addressing performance-related problems with Microsoft CRM. Most of the Microsoft CRM performance problems are related to SQL performance issues. As such, I am including here some of the more common sites that use some of the same techniques that we do in addressing and finding out performance bottlenecks on the Microsoft SQL Server. Many of you may already be familiar with some of these:
http://www.microsoft.com/sql/default.mspx
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
PSSDIAG data collection utility
http://support.microsoft.com/default.aspx?scid=kb;en-us;830232
Greg [MSFT] (Expert):
Q: Forgive me if this is off the topic....Can the SFO client be configured to work with integrated authentication with SSL (require a secure connection) enabled?
A: Renee,
Let me inquire of one of our SFO experts regarding your question. I am IM'ing him now.
Greg [MSFT] (Expert):
Q: But these applications make a connection to the Web server. We tried to configure our site using only basic authentication with SSL and received an error when attempting to use these applications. When we changed to integrated authentication, it worked.
A: From the CRM Support SFO expert....let me clarify this: if you have any other IIS applications which require basic authentication, then you need to put CRM on a separate Web site, as CRM requires integrated Windows authentication.
Greg [MSFT] (Expert):
CRM support: Second, if you have SSL enabled and the Secure Channel option checked on the CRM Web server side, then you need to uncheck the Require Secure Channel option so that XSL files can be downloaded when you open SFO client.
Greg [MSFT] (Expert):
Q: We have not experienced any performance issues from Deployment Manager. Is it required that we restart the IIS services after publishing some changes?
A: Doing an IIS reset is a requirement after publishing customizations through Deployment Manager. The reason is that we actually make a logical cache of the metabase structure in memory on the CRM server and while publishing will update the physical metabase tables, it does not flush the logical cache.
Chad Rexin [MSFT] (Expert):
Q: What suggestions do you have pertaining specifically to optimize a very large enterprise grade (3 million records +) system?
A: Optimizing a very large enterprise grade system can be challenging and is going to be unique to most customers. A customer with 3 million plus records may be exceeding the capabilities of the Microsoft CRM application. This is something that should definitely be discussed with the Microsoft CRM Support team, who can line you up with the Microsoft Elite Support team. They have engineers who specialize in analyzing hardware and making recommendations related to usage of the product.
Things to take into consideration are the following:
1. Hardware -- SQL Server, CRM Servers, number of users
2. How the software is used
3. If Microsoft CRM Sales for Outlook is used and if users go offline
4. The type of data entered -- i.e., if large attachments are added to data
5. Peak usage patterns
6. How much existing data is imported into a Microsoft CRM Server
Greg [MSFT] (Expert):
Q: Is there a caching feature for the SFO Client?
A: What type of caching are you referring to?
Greg [MSFT] (Expert):
Q: Is there a fix or method for managing the replication system tables that tracks changes to the published tables (MSMerge_contents)? By default, the retention setting on each publication is set to never expire.
A: The experiation duration is configurable in SQL Enterprise Manager. I am looking for a KB describing this. One thing to note though is that if you do expire the contents of these tables, you may be forced into taking a full initial load again the next time you attempt to go offline, depending on the timing.
Greg [MSFT] (Expert):
Q: So do you recommend not to expire the contents?
A: We do recommend expiring the contents but realize that it could have the consequence of users having to take their initial sync again.
Greg [MSFT] (Expert):
Q: Each time we click the CRM Outlook Folder it takes time to load, so we were wondering if this can get cached in some way?
A: Unfortunately not. Because we are leveraging a lightweight Web server technology (Cassini) to service the resource requests, we are not able to cache the resources.
Chad Rexin [MSFT] (Expert):
Q: Also, will Deployment Manager and Workflow Manager only work with integrated authentication?
A: Could I get a little bit more context surrounding this question? Microsoft CRM Deployment Manager and Microsoft CRM Workflow Manager are applications that are not run from a Web client like the rest of the Microsoft CRM application is. Microsoft CRM Deployment Manager and Microsoft CRM Workflow Manager require that a user have a valid Microsoft CRM License, be a local administrator on the Microsoft CRM Server, have administrative permissions on the Microsoft SQL Server, and be assigned to the Microsoft CRM System Administrator role.
Chad Rexin [MSFT] (Expert):
Q: Is there an e-mail address that my support lead can have to contact you?
A: For this, I'd recommend contacting our support team directly. The link for this is at http://support.microsoft.com/oas/default.aspx?ln=en-us&x=14&y=11&gprid=1484& Indicate that you were discussing this issue on the Web chat today, regarding Tuning the Performance of Microsoft CRM 1.2, and indicate that you spoke with myself, Chad Rexin. That way you will be assured to get in touch with me. We can get you some more directed tuning recommendations based on the specifics of the implementation(s) that you are looking at.
Greg [MSFT] (Expert):
Q: I was told by Microsoft Support that basic authentication with SSL should work, but we are constantly experiencing problems with our Web site being configured as such.
A: Basic authentication is not supported for the CRM Web site. For other Web sites on the same Web server, Basic Authentication may be appropriate.
Greg [MSFT] (Expert):
Q: So this means that data is transferred in clear text?
A: You are correct for Basic Authentication, your security principal credentials are passed in clear text, but again, Basic Authentication is not supported on the CRM Web site.
Greg [MSFT] (Expert):
Q: Our Web site is now configured to use both Integrated and Basic authentication. We had to deselect the option to require a secure connection because it breaks the SFO client.
A: If you have SSL and Secure Channel, the SFO client will not download the XSL files and you will not be able to render any of the CRM folders/Web forms.
Chad Rexin [MSFT] (Expert):
Here are some additional links that help with performance tuning Microsoft CRM:
Optimizing the Performance of Microsoft CRM 1.2 white paper
http://www.microsoft.com/downloads/details.aspx?FamilyID=E1A00070-2E5A-410A-881F-D859F8474933&displaylang=en
Reindexing Databases to Optimize Performance in Microsoft CRM 1.2
http://www.microsoft.com/dynamics/crm/using/12/reindexingdbs.mspx
Chapter 16: Performance Monitoring and Optimization pgs 273–289 in the Microsoft CRM 1.2 Implementation Guide (found on the Microsoft CRM Documentation CD)
Greg [MSFT] (Expert):
Q: We installed SSL to secure the data that is transferred while working within the Web application, which is why we would like to require a secure connection, but this breaks the SFO client. Will we just have to rely on the end-user connecting using https?
A: Renee, as this chat room is designated for Performance Tuning, I would recommend that you contact CRM Support to discuss these IIS security issues.
Greg [MSFT] (Expert):
Q: OK...thank you...I apologize!!
A: Not a problem. Your questions are very valid ones and CRM Support is in the best position to provide you with the answers to your questions.
Chad Rexin [MSFT] (Expert):
In addition to those links, while not directly related to Microsoft CRM, the Microsoft CRM application often benefits from increased performance by installing the latest Microsoft SQL Server 2000 hotfixes. Given identical hardware configurations, Microsoft CRM installations will run with the best performance if using Microsoft Windows Server 2003 instead of with Microsoft Windows Server 2000.
Greg [MSFT] (Expert):
Q: Any suggestions on managing the InvoiceDetailBase for large implementations? Seems to be one of the tables that grows excessively.
A: We have one large customer that I am aware of that has a regularly scheduled purge mechanism that they wrote using the CRM SDK. For this customer, anything older than 18 months they purge the InvoiceDetailBase and InvoiceBase. If you do see poor performance on this table or any table, I would recommend you contact support and we can do a table index assessment to determine what indexes you might find value in creating/including.
Chad Rexin [MSFT] (Expert):
Q: I'm part of an integration team that sells MSCRM only to enterprise grade clients... Do you have something more specific than the general functional/analytical suggestions that you have given?
A: Cases like this require working closely with the customer in knowing how their business model works and in knowing how much hardware they may already have or are willing to spend in getting good performance. Very large implementations (I would say greater than 500,000 records in any entity of Microsoft CRM), would require using test servers that are identical to the production server and setting up performance tests using programs such as Microsoft Application Center Test, Empirix, or other test software to simulate large loads against the Microsoft CRM application. This is something that does take resources in both time, personnel, and hardware to do accurately. We are working on some tools that will make that process easier, but those are not currently in a state where they can be released in the near future for general release.
James Z (Moderator): This chat is now over.