Happy Holidays!

I wish everyone a merry Christmas and a great new year! Thanks for reading my blog, I'll see you in 2013 with more interesting articles. It was a very busy year and I hope you enjoyed to stop by and found something useful and productive.

Thanks and see you next year.

Dynamics CRM Database Optimization with DMVs

On this article I will be covering mainly optimization around indexes and how DMV's can help to monitor and optimize indexes.

Data management views (DMV's)
Dynamic management views store SQL transactional information which can be used to monitor health, performance and diagnose problems. DMV's are extremely helpful in particular around performance-tuning. The information is stored until you restart SQL server, after a restart all is reset to 0.

There are a number of DMV's to retrieve a different number of metrics for different purposes; we will be looking in particular at the Index related and Execution DMV's:

• Index
• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_missing_index_columns
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups
• Execution
• sys.dm_exec_query_stats
• sys.dm_exec_requests

1. http://msdn.microsoft.com/en-us/library/ms187974(v=sql.105).aspx
2. http://msdn.microsoft.com/en-us/library/ms188068(v=sql.105).asp

Indexes Overview
Imagine walking to a library with 1 million books stored across 5 floors, and ask for a book. If the library does not keep a record where each book is stored, which floor, shelf, categories etc... how long to you think staff would take to find your book? a very long time. The same concept applies to databases, indexes are needed to improve response time when users request specific data.

We will be mainly looking at optimization with indexes so before we do that I want to explain very briefly what they are and how they work. There are two types of indexes, clustered index and non-clustered index.

• Non-Clustered IndexesThis type of index, indexes a particular column and hold a reference to where on the table this data can be found. The example below, shows if you want to find an order with an amount of 210 you would need to scan the entire table to find all orders with an amount of 210. If we create an index for the column 'Amount', the index it's ordered descendent and the query would stop at the last 210 and there would be no need to continue the execution.

• Clustered IndexesClustered indexes have the same concept but, they sort the column 'Amount' descendent on the table itself, when orders with the amount 210 are found SQL does not need to lookup the reference to find the details of that order, the data is accessible immediately. 

Both type of indexes have positive and negative areas, the clustered index has a higher cost when you need to re-index the column 'Amount' because all the rows need to shift into the correct place, however the data it's immediately available when data is found. The non-clustered indexes are quicker to re-index but require an extra lookup to gather the data on the table. Also note that the clustered indexes only one index can exist per table. The non-clustered indexes can be created for multiple columns on the same table.

Missing Indexes
DMV's store missing indexes when queries are optimized by the SQL query optimizer; if you have a customized CRM environment you should monitor these type of DMV's on a regular basis. Below a nice script you could use with references to missing indexes DMV's.


SELECT   migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,   'CREATE INDEX [myIndex_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)   + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'   + ' ON ' + mid.statement   + ' (' + ISNULL (mid.equality_columns,'')     + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END     + ISNULL (mid.inequality_columns, '')   + ')'   + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,   migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
Index Usage
It's important you check for Index usage because indexes consume memory and disk I/O resources when they need to be rebuild to keep the most up to date data.

To check for index usage run the following query:

 select OBJECT_NAME(a.object_id),b.name,a.user_seeks,a.user_scans,a.* from sys.dm_db_index_usage_stats a join ORG_MSCRM.sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id where a.database_id = DB_ID('ORG_MSCRM') and b.name like 'myIndex%' order by b.name asc The output of this query would be something like:

From the above screenshot is clear that _DMV_09 has not been used since it was created and therefore should be deleted.

Fragmentation
CRM ships with built-in maintenance jobs which are configured during installation and are designed to optimize the database and reduce fragmentation. It's good practice to re-configure these jobs to run out-of-hours.

To configure the built-in sql maintenance jobs download the following tool:
http://crmjobeditor.codeplex.com/

Place the tool under C:\Program Files\Microsoft Dynamics CRM\Tools\

The tool provides a flexible and easy way to configure the maintenance jobs; note that the job will run once every day( every 1440 minutes), you can configure it to run more times during the day.

Normally the CRM built-in maintenance jobs are designed to keep the system healthy and do a very good job, to understand if you need these jobs to run more often you can monitor fragmentation with the following queries:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level, avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count FROM sys.dm_db_index_physical_stats (DB_ID(N'ORG_MSCRM'), NULL, NULL, NULL , 'SAMPLED') ORDER BY avg_fragmentation_in_percent DESC
Or you can be more specific at the table level:

SELECT avg_fragmentation_in_percent,* FROM sys.dm_db_index_physical_stats ( db_id('ORG_MSCRM'), OBJECT_ID('PrincipalObjectAccess'), NULL, NULL, 'DETAILED')

You can check if the maintenance tasks have run successful by looking at the last time the indexes have been updated:
SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('PrincipalObjectAccess') Check for expensive queries
With a better understanding around indexes and DMV's we could start looking at top 20 most expensive queries in your CRM environment using sys.dm_exec_query_stats to give you a better picture what is going on under the hood and analyse whether tuning is needed for those queries.

SELECT DISTINCT TOP 20 t.TEXT QueryName,
s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / NULLIF(s.execution_count,0), 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()),0), 0) AS FrequencyPerSec FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC

Database Maintenance
However CRM maintenance jobs do a really nice job in keeping the system healthy, I would still recommend  creating SQL jobs to do regular out-of-hours database maintenance:

• Shrink the database
• DBCC SHRINKFILE (1, TRUNCATEONLY);
• DBCC SHRINKFILE (2, TRUNCATEONLY);
• Rebuild indexes
• EXEC sp_msforeachtable 'DBCC DBREINDEX("?"," ",80)'
• Update statistics
• EXEC sp_updatestats;

Summary
There are a number of scripts and documentation on the internet around DMV's, is good practice monitor these metrics and optimize the system accordingly. Note that with each SQL version DMV's may change and new features added etc...

We have covered basic SQL optimization around indexes and an overview on how indexes work and how they can help. I hope you enjoyed the article, please leave your feedback.

References

1. http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
2. http://msdn.microsoft.com/en-us/library/ms188068(v=sql.105).aspx

Dynamics CRM 2011 upgrading from trial license

I often see on the CRM forum questions how to upgrade CRM from a trial installation into a full licensed environment without the need to re-install CRM, so decided to create a quick post on how to do this and where to find the newly purchased CRM license key.

Registry key
If you purchased the full license, Microsoft will provide you with a Media download link, so you can download the installation DVD. The key is not provided by the sales assistant's because the registry key is now built-in in the media. However you can retrieve this key form the DVD on:

D:\Server\amd64\License.txt

Upgrade Trial License
Open deployment manager:

PowerShell

You can also set your new license key with PowerShell, below are the commands to do this:

To activate CRM PowerShell commands type the below command:
Add-PSSnapin Microsoft.Crm.PowerShell

To set the new CRM key run:
Set-CrmProductKey -ProductKey [string]

To get your current version:
Get-CrmLicenseProperty

Dynamics CRM troubleshooting Kerberos Authentication

This is Part 2 of my article on improving CRM performance with Kerberos http://quantusdynamics.blogspot.com/2011/12/extreme-performance-with-dynamics-crm.html

Kerberos is not the easiest protocol to troubleshoot due to it's complex architecture, I would like to share with you a few tips on how to tackle kerberos issues.

To troubleshoot kerberos I'm going to use the following tools:

• Wireshark (we could use Microsoft network monitor but for Kerberos I think Wireshark is simpler to use and illustrate)
• Fiddler (capture HTTP packets)
• ADSI Edit or Command prompt setspn
• Microsoft Kerbtray to list/purge tickets or command prompt klist.

If you read the first article on how to improve performance with kerberos, you understand that when you use  custom service accounts you will need unique SPN's configured to allow authentication to succeed. However you could use built-in accounts, the best practice and recommendations from Microsoft is to always use custom service accounts, and it's here where things get a bit more complicated. I'm assuming you have attempted to configure Kerberos based on my first article and you run Fiddler for the first time and let's assume you see this on the first attempt:

This ticket is an NTLM ticket, Kerberos failed somewhere and we will try to find out using wireshark.

Open Wireshark start capturing network traffic and on the search field type: Kerberos

Wireshark shows a packet with error: KRB5KDC_ERR_S_PRINCIPAL_UNKNOWN highlight this packet and on the below screen expand the Kerberos packet details and you find that the missing DNS is HTTP/crm.ha Kerberos is looking for this and can't find it. The next step is to register an SPN under the service account CRM website is running on.

Configuring SPN

a quick way to create visualise SPN's  is using the Microsoft command tool Setspn, open the command prompt and type:

setspn -L DOMAIN\SERVICE_ACCOUNT 

Replace DOMAIN and SERVICE_ACCOUNT with your own domain and service account. This will list any SPN's configured under that account.

If you can't find the SPN listed under that account create the SPN:

setspn -a HTTP/crm.ha SERVICE_ACCOUNT

Run again the first command and check if the SPN has been created.

Another way to create SPN's is using the ADSI Edit GUID, it's easier to manage multiple SPN's for multiple accounts etc..

To install ADSI Edit follow the following instructions:

On the Server manager under Features add new features and navigate to the following options:

When installed  go to: Start menu > Administrative Tools > ADSI Edit

Open ADSI Edit and right click and choose connect to...
On the below screen type the name of the object you want to use, and leave default naming context and type the SERVER_NAME click ok. replace SERVER_NAME with the name of Domain controller in your network.

Below Expand the folders until you see the service account you want to configure the SPN's

 When you find the account, right-click and properties. The below screen appears. Scroll down to servicePrincipalName and click Edit.

On the below screen you can manage the SPN's and add the needed SPN, you can type HTTP/crm.ha and click OK and Apply and it's done.

Looking for Kerberos tickets

We have configured the missing SPN, the next step is to run wireshark and look for the kerberos ticket.

Highlighting the first packet show us what request's are going through in the process.

I highlight the first HTTP packet and I already see the first negotiation using Kerberos tickets look for Authorization: Negotiate YIIUM this is a Kerberos ticket.

Below I highlight the next in line HTTP packet HTTP/1.1 found and you see a reply with a kerberos ticket again.

It looks like we are now using Kerberos. if we look back at Fiddler now, we should see the following:

Also another way to check your kerberos tickets it's using Microsoft Kerbtray or the command line prompt klist.

Usage: Klist.exe [-lh ] [-li ] tickets | tgt | purge

On the command line run:

klist [ENTER] This should show you all kerberos tickets issued to your machine and user account, you may see a number of them.

To purge the tickets do:

klist purge

You can also use a GUID tool called kerbtray you can download kerbtray from here:

• http://www.microsoft.com/en-us/download/details.aspx?id=17657
• http://www.microsoft.com/en-us/download/details.aspx?id=7887

Kerbtray will appear as a green icon on your tray area, right-click this icon and you can purge tickets or list the current tickets. This is the same functionality as the klist command.

Event Logs

You could also look for kerberos authentication issues by enabling Kerberos debug logging on the server, this would generate a number of detailed event logs on your server which can be quite handy:

http://support.microsoft.com/kb/262177

Summary

I hope this was helpful, the article is designed to give an overview of the tools available for troubleshooting kerberos authentication issues and how to use this tools to look for the right information.

References
If you interested in learning more about Kerberos and it's architecture, I would recommend you read this article which gives a great overview on this. You may also find loads of high-level articles on this.

• http://cs.unc.edu/~fabian/course_papers/steiner_neuman.pdf

Dynamics CRM 2011 Reduce your javascript code footprint with Microsoft Ajax Minifier tool

Did you know about Microsoft Ajax Minifier tool? I use this tool for some time now and I think is great, it gives developers the chance to reduce their 'code footprint' by compressing javascript and css files.

You can download the tool here:
http://ajaxmin.codeplex.com/

As customization in CRM 2011 are more flexible, developers have more functionality on their side to impress customers, it's easy to end up with long and complex javascript files which end up increasing the download and processing time; so a good practice in a production environment is to remove all comments and un-necessary spaces to compress the code, with Microsoft Ajax minifier tool, can do this for us and also minify the code compressing it to the optimal size.

The same concept as jquery-minimized version, I would recommend in a production environment minimize all your code to get optimal performance.

Below is an example of a normal javascript code from a previous post on a jquery duplicate detection pop-up.

function idp() {
var isCreateForm = Xrm.Page.ui.getFormType() == 1;
if (isCreateForm) {

$.ajaxSetup({cache:false});
$(document.body).append("
A possible duplicate record was found, please review:
");

$('#name').focusout(function() {

var name = Xrm.Page.getAttribute('name').getValue();
var query = "/AccountSet?$filter=Name eq '"+name+"'";

var serverUrl = Xrm.Page.context.getServerUrl();

    if (serverUrl.match(/\/$/)) {
        serverUrl = serverUrl.substring(0, serverUrl.length - 1);
    }

    var ODataURL = serverUrl + "/XRMServices/2011/OrganizationData.svc" + query;

    $.ajax({
        type: "GET",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: ODataURL,
        beforeSend: function (XMLHttpRequest) {
            XMLHttpRequest.setRequestHeader("Accept", "application/json");
        },
        success: function (data, textStatus, XmlHttpRequest) {
           var entities = data.d.results;
    $.fx.speeds._default = 1000;
    $(document).ready(function() {
        $("#dialog").dialog({
            autoOpen: false,
            show: "blind",
            hide: "explode",
            modal: true
        });

    });
if(entities.length > 0 ) {
  for( i=0; i< entities.length; i++)
  {
     var entity = entities[i];
}    

var entityUrl = "main.aspx?etn=account&pagetype=entityrecord&id=";
$(document).ready(function() {
                             $("#link").empty();
                             $("#link").append(document.createTextNode(entity.Name));
                             $("a#link").attr("href","/"+entityUrl+"%7B"+entity.AccountId+"%7D"+"");
                             $('#dialog').dialog("open");
});          
}

        },
        error: function (XmlHttpRequest, textStatus, errorObject) {
            alert("OData Execution Error Occurred");
        }
    });
});
}
} 

Install Ajax minifier tool and from the command prompt run:
AjaxMin.exe idp_engine.js -out idp_engine_min.js

The end result will be something like this:

function idp(){var n=Xrm.Page.ui.getFormType()==1;if(n){$.ajaxSetup({cache:!1}),$(document.body).append(""),A,possible,duplicate,record,was,found,please;n:");";$("#name").focusout(function(){var r=Xrm.Page.getAttribute("name").getValue(),u="/AccountSet?$filter=Name eq '"+r+"'",n=Xrm.Page.context.getServerUrl(),t;n.match(/\/$/)&&(n=n.substring(0,n.length-1)),t=n+"/XRMServices/2011/OrganizationData.svc"+u,$.ajax({type:"GET",contentType:"application/json; charset=utf-8",datatype:"json",url:t,beforeSend:function(n){n.setRequestHeader("Accept","application/json")},success:function(n){var u=n.d.results,f,e;if($.fx.speeds._default=1e3,$(document).ready(function(){$("#dialog").dialog({autoOpen:!1,show:"blind",hide:"explode",modal:!0})}),u.length>0){for(i=0;i

We have compressed the code from:
Original Size: 1953 bytes; reduced size: 1085 bytes (44.4% minification)

The tool also provides an approximate Gzip compression:

Gzip of output approximately 887 bytes (18.2% compression)

Summary
This tool can help reduce the code footprint on customizations with large usage of javascript and css files, and is a great way to tackle latency and keep the code running at optimal performance.

Hope this was useful.

Dynamics CRM 2011 Provision Rollups per Organization

With Dynamics CRM 2011 we can now deploy update rollups without updating the Organization database, the advantages are:

1. Deploy Update Rollups per Organization and not to the entire Environment.
2. Test binaries before updating the database

By default it updates the database as you install an update rollup, to stop it from automatically update the databases, run the following powershell script:

Open PowerShell copy+paste the below commands; on the last line hit enter:

Add-PSSnapin Microsoft.Crm.PowerShell
$setting = New-Object "Microsoft.Xrm.Sdk.Deployment.ConfigurationEntity"
$setting.LogicalName = "Deployment"
$setting.Attributes = New-Object "Microsoft.Xrm.Sdk.Deployment.AttributeCollection"
$attribute = New-Object "System.Collections.Generic.KeyValuePair[String, Object]" ("AutomaticallyInstallDatabaseUpdates", 0)
$setting.Attributes.Add($attribute)
Set-CrmAdvancedSetting -Entity $setting

Note: what the powershell script does, sets to 0 the database field AutomaticallyInstallDatabaseUpdates on the DeploymentProperties table of the MSCRM_CONFIG database.

Now if you Install the latest rollup; when you open deployment manager the column 'version' should still show the previous RU version for all organizations; to update the database for a specific organization right click the organization you want to update and click Update:

 If you get the below error, stop the Asynchronous service and try again.

 

 



















After you update the organization, you now have one organization with RU6 and other with RU10:

You ready for testing :)

Hope was helpful.

Dynamics CRM 2011 leverage duplicate detection with jquery

Have you come across a situation where users take a considerable amount of time filling in a form and at the end the CRM duplicate detection pops-up?

With Jquery we can pro-actively check for duplicate records performing a number of checks on the background; on a successful trigger alert the user.

The process is straight forward, however I have used jquery CSS UI libraries to give the solution a better look-and-feel. You can download the CRM unmanaged solution on the following link:
http://gallery.technet.microsoft.com/Dynamics-CRM-2011-3e9fdb10

If you would like to change the pop-up colour and layout, look at the available jquery UI libraries:
http://jqueryui.com/

When you download the solution and import it into CRM, load the following resources on the account form properties:

Below is the javascript code that performs the duplicate check.  On this example it checks for the account name, however you can extend this and perform multiple checks and combinations of data:

function idp() {

var isCreateForm = Xrm.Page.ui.getFormType() == 1;
if (isCreateForm) {

$.ajaxSetup({cache:false});
$(document.body).append("
A possible duplicate record was found, please review:
");

$('#name').focusout(function() {

var name = Xrm.Page.getAttribute('name').getValue();
var query = "/AccountSet?$filter=Name eq '"+name+"'";

var serverUrl = Xrm.Page.context.getServerUrl();

    if (serverUrl.match(/\/$/)) {
        serverUrl = serverUrl.substring(0, serverUrl.length - 1);
    }

    var ODataURL = serverUrl + "/XRMServices/2011/OrganizationData.svc" + query;

    $.ajax({
        type: "GET",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: ODataURL,
        beforeSend: function (XMLHttpRequest) {
            XMLHttpRequest.setRequestHeader("Accept", "application/json");
        },
        success: function (data, textStatus, XmlHttpRequest) {
           var entities = data.d.results;
    $.fx.speeds._default = 1000;
    $(document).ready(function() {
        $("#dialog").dialog({
            autoOpen: false,
            show: "blind",
            hide: "explode",
            modal: true
        });

    });
if(entities.length > 0 ) {
  for( i=0; i< entities.length; i++)
  {
     var entity = entities[i];
}    

var entityUrl = "main.aspx?etn=account&pagetype=entityrecord&id=";
$(document).ready(function() {
                             $("#link").empty();
                             $("#link").append(document.createTextNode(entity.Name));
                             $("a#link").attr("href","/"+entityUrl+"%7B"+entity.AccountId+"%7D"+"");
                             $('#dialog').dialog("open");
});
          
}

        },
        error: function (XmlHttpRequest, textStatus, errorObject) {
            alert("OData Execution Error Occurred");
        }
    });


});

}

}

Dynamics CRM 2011 take other user share view ownership - workaround

Have you previously come across a user that wants to delete a number of views shared by another user that left the company?

The problem in CRM is that at admin level you can't take ownership of the view assigning this to yourself, because the level of permissions on the security role are only at the user-level.

I have filed an improvement request on Microsoft Dynamics CRM connect website to introduce more flexibility around this, Please vote:
https://connect.microsoft.com/dynamicssuggestions/feedback/details/757204/admin-full-access-to-shared-views

To resolve the problem you could easily reset the user AD password, however in large companies you would have to file a HR form requesting permissions for this, because you will be accessing information owned by someone else in the company.

The workaround for this is quite straight forward and all done on the CRM side.

1. Create an AD test account
2. In CRM open the profile of the user that left the company and change the Domain Logon Name to the new test account, press tab, make sure the first and last name loads for the new account and press save, make sure you assign a security role.
3. Over the IE icon, press SHIFT+Right-Click > Select Run as different user 
4. Type the credentials of the test account you created on step 1, IE will run under the test account context.
5. Open CRM and voila your test account took ownership of all the data of the user that left the company
6. Update the views etc...
7. Revert the changes back to the user account that left the company by again updating the Domain logon name.

Note: you obviously don't want to do this everyday, the long term solution is to wait on Microsoft to introduce new features around this. Please vote on my feature request above.

Dynamics CRM 2011 Optimization guide

View the wiki technet article here:  http://social.technet.microsoft.com/wiki/contents/articles/13661.dynamics-crm-2011-quick-optmization-guide.aspx

I've put together a high-level list of optimizations for dynamics CRM to centralise information for myself, and thought sharing would be helpful for someone, I'll keep this list updated as I find more information.

1. Authentication
2. IIS
3. CRM Registry Keys
4. Database
5. Outlook Client
6. Internet Explorer
7. Email Router
8. Monitoring
9. Resources

1. Authentication

The number of authentication request's and authentication challenges takes valuable seconds deteriorating performance.

• Application Pool Kernel Mode authentication enabled
• Enable Kerberos
• http://quantusdynamics.blogspot.co.uk/2011/12/extreme-performance-with-dynamics-crm.html 
• Enable useAppPoolCredentials
• Enable authPersistNonNTLM
• [PFE] - SPN confguration SpreadSheet
• http://blogs.msdn.com/b/crminthefield/archive/2012/12/20/crm-2011-service-principal-names-spn-generation-and-usage.aspx

Resources:

http://support.microsoft.com/kb/2593042

2. IIS

• AppPool Recycling
• Configure to recycle after hours.

• WCF Compression - enable compression for the content-type: application/soap+xml; charset=utf8 this content type is used by the Outlook client HTTP requests. Compressing this traffic reduces network traffic and improves communication.
• http://blogs.msdn.com/b/crminthefield/archive/2011/12/29/enable-wcf-compression-to-improve-crm-2011-network-performance.aspx
• Enable Dynamic Compression
• http://www.iis.net/ConfigReference/system.webServer/httpCompression
• 
  

3. CRM Registry Keys
A number of tweaks can be done on the CRM side to improve performance.

• You can control the size of the Workflow table by deleting completed Workflows with a registry key, this is a nice way to improve performance on the database side, however this can only be applied if you don't need the workflow history.
• Name: AsyncRemoveCompletedWorkflows
• Type: DWORD
• Value: 1
• Improve SQL Performance EnableRetrieveMultiple
• Type: DWORD
• Value: 2 (Decimal)
• Fixes a number of issues around the SQL tempdb database and improves performance
• http://support.microsoft.com/kb/2535245
• Note that with RU10 the suggested default option is 0 read more on the [1] resource document optimizing and maintaining performance.
• Disable the email pop-up reminder to speed up load times
• Name: DisablePendingEmailReminder
• Type: DWORD
• Value: 1
• Set a preferred Domain Controller to speed up AD checks
• Name: PreferredDC
• Type: String
• Value: DC_Name
• 
  

There are a number of registry keys targeting different types of performance issues and types of environments, Microsoft is now releasing with each update Roll Up a package named:'tools'.This package contains a tool that can implement a number of updates/optimizations which were previously reserved for registry implementations, this way makes it easier to enable optimizations centrally and avoid creating registry keys across multiple front-end servers.

http://support.microsoft.com/kb/2691237
e.g number of registry keys part of RU6

• DisableSmartMatching
• Default Value – False
• AutoCreateContactOnPromote
• Default Value - True
• BackgroundSendBatchFile
• Default Value – 10
• DisableInactiveRecordFilterForMailMerge
• Default Value - False
• LookupNameMatchesDuringImport
• Default Value - False
• EnableRetrieveMultipleOptimization
• Default Value - 1
• DoNotIgnoreInternalEmailToQueues
• Default Value - False
• SkipSuffixOnKBArticles
• Default Value - False
• SkipGettingRecordCountForPaging
• Default Value – False

4. Database

• CRM Indexes with DMVs
• http://quantusdynamics.blogspot.co.uk/2012/11/dynamics-crm-database-optimization-with.html
• CRM SQL Maintenance jobs
• https://community.dynamics.com/product/crm/crmtechnical/b/crminthefield/archive/2012/04/26/avoid-performance-issues-by-rescheduling-crm-2011-maintenance-jobs.aspx
• http://crmjobeditor.codeplex.com/
• Optimizing CRM built-in maintenance jobs with Indexes
• http://quantusdynamics.blogspot.co.uk/2013/08/dynamics-crm-2011-optimize-built-in.html
• Max parallelism set to 1
• http://technet.microsoft.com/en-us/library/dd979074.aspx 
• Enable Lock Pages in memory forcing SQL queries to run using physical memory
• http://msdn.microsoft.com/en-us/library/ms190730(v=sql.105).aspx
• Enable Read Committed Snapshot isolation to avoid deadlocks
• http://www.microsoft.com/en-us/download/details.aspx?id=24923
• http://technet.microsoft.com/en-us/library/ms345124.aspx
• Maintenance of the Workflow Table AsyncOperationBase
• http://support.microsoft.com/kb/968520
• Maintenance of the Principal Object table PrincipalObjectAccess
• http://support.microsoft.com/kb/2664150
• SQL Deadlocks article
• http://quantusdynamics.blogspot.co.uk/2012/01/dynamics-crm-40-sql-dead-locks.html

5. Outlook Client

• Outlook client optimization
• http://quantusdynamics.blogspot.co.uk/2013/07/dynamics-crm-2011-outlook-client.html
• Registry Keys
• http://support.microsoft.com/kb/2585157 
• Increase the CRM Sync options, this depends on your environment.Settings > Email
• Promote emails
• Syncronization
• Manage Outlook filters
• http://blogs.msdn.com/b/crm/archive/2011/06/02/controlling-user-synchronization-settings.aspx
• Disable cached pinned-views with a registry key on the client side
• Name: DisableMapiCaching
• Type: DWORD
• Value: 1

Note: Make sure you enable IIS WCF compression from point 2. IIS

6. Internet Explorer

• Ensure CRM url is added to the local intranet zone
• Authentication Options
• AutoLogon
• Windows Integrated Authentication
• Reset Zoom level for new windows and tabs
• Disk space for temporary internet files
• 250MB
• Increase IE connections to the server with Registry Keys
• http://support.microsoft.com/?kbid=282402 

7. Email Router

• Installing email router on multiple servers
• http://quantusdynamics.blogspot.co.uk/2012/02/dynamics-crm-installing-email-router-on.html
• Load Balance Email router
• http://quantusdynamics.blogspot.co.uk/2012/03/dynamics-crm-load-balance-email-router.html
• Scalling Email router with Business Units
• http://quantusdynamics.blogspot.co.uk/2012/04/dynamics-crm-2011-scaling-email-router.html
• [PFE] - Email router scalability and availability
• http://blogs.msdn.com/b/crminthefield/archive/2012/10/28/microsoft-dynamics-crm-2011-e-mail-router-availability-and-scalability.aspx
• Reduce the configuration update period
• Open the Microsoft.Crm.Tools.EmailAgent.xml in notepad and edit the following line to 86400000 this will set the email router to update the user list once a day, this is important if you have a large number of users in your environment.
• <configupdateperiod>86400000</configupdateperiod>
• Increase the number of emails to process on each batch
• On the same file as above you need to add an extra section to override some of the settings, this will stay included on the  <systemconfiguration> section:</systemconfiguration>
•  <provideroverrides>
•   <cachecapacity>1024  </cachecapacity>
•   <pendingstatusdelay>300000 </pendingstatusdelay>
•   <sendingstatusdelay>1800000 </sendingstatusdelay>
•   <maximumdeliveryattempts>10 </maximumdeliveryattempts>
•   <retrievemessagecount>10  </retrievemessagecount>
•   <batchsize>50 </batchsize>
•   <requestbatchsize>50 </requestbatchsize>
• </provideroverrides>

for more information on how to use <proveroverrides> </proveroverrides> go to:

http://support.microsoft.com/kb/972150

If you have more than 5000 users in your environment you will need to increase email router limits on how many users to load from CRM by setting MaxRowsPerPage on the front-end server:

• Name: MaxRowsPerPage
• Type: DWORD
• Value: Number_of_rows (Decimal)

8. Monitoring

• Use SSIS to monitor pending emails
• http://quantusdynamics.blogspot.co.uk/2013/07/dynamics-crm-2011-using-ssis-to-monitor.html

 8. Resources 

1. [CRM 2011] Optimizing and Maintaining the Performance of a Microsoft Dynamics CRM 2011 Server Infrastructure. http://www.microsoft.com/en-us/download/details.aspx?id=27139
2. [CRM 2011] Optimizing and Maintaining Client Performance for Microsoft Dynamics CRM 2011 and CRM Online. http://www.microsoft.com/en-us/download/details.aspx?id=23261
3. [CRM 4][NB] Security and Authentication in Microsoft Dynamics CRM: Connectivity and Firewall Port Requirements in On-Premise Deployments. http://www.microsoft.com/en-us/download/details.aspx?id=12774
4. [CRM4][NB] Security and Authentication in Microsoft Dynamics CRM: The Microsoft Dynamics CRM Security Model. http://www.microsoft.com/en-gb/download/details.aspx?id=12108
5. [CRM4] Outlook Synchronization in Microsoft Dynamics CRM 4.0. http://www.microsoft.com/en-us/download/details.aspx?id=17594
6. [CRM4] [NB] Offline and Online Synchronization in Microsoft Dynamics CRM. http://www.microsoft.com/en-us/download/details.aspx?id=2737
7. [CRM4] Sharing Data across Microsoft Dynamics CRM Deployments. http://www.microsoft.com/en-us/download/details.aspx?id=10151
8. [CRM4] Optimizing and Maintaining Microsoft Dynamics CRM 4.0. http://www.microsoft.com/en-us/download/details.aspx?id=3997
9. [CRM2011] [Email Router] How to configure the Microsoft Dynamics CRM on-premises and Microsoft Dynamics CRM Online E-mail Router in different deployment scenarios. http://www.microsoft.com/en-us/download/details.aspx?id=21445
10. [CRM 2011] Deploying Microsoft Dynamics CRM 2011 and CRM Online Solutions from Development through Test and Production Environments. http://www.microsoft.com/en-us/download/details.aspx?id=27824
11. 
    
12. 
    
13. 
    

Dynamics CRM Identifying and troubleshooting performance bottlenecks with Microsoft Network Monitor

Analyse your Dynamics CRM performance with two great Microsoft tools:

1. Microsoft Network Monitor is a great tool used to troubleshoot applications and network traffic; Fiddler is probably the only tool you may ever use to troubleshoot traffic and HTTP requests. However if you need something more advanced to troubleshoot and inspect in detail TCP/IP packets you will need Microsoft Network Monitor or may consider Wireshark. 
2. Microsoft Visual Round Trip Analyzer sits on top of the Network Monitor; it analyses traffic in a friendly format with a graphical representation of the client-server communications and the greatest feature generates a report with scores based on the captured traffic to easily identify network and application bottlenecks.

Download Network Monitor
http://www.microsoft.com/en-us/download/details.aspx?id=4865

Download Microsoft Visual Round Trip Analyzer
http://www.microsoft.com/en-us/download/details.aspx?id=21462

1. Capturing Data
When the software installed open Network Monitor and click New Capture > click play. This will start capturing traffic. When you done, save the captured traffic as a *.cap file.

You can also Capture data with Microsoft Visual Round Trip Analyzer, press the play button and it will start capturing traffic, however you will only be able to view the data when you stop.

2. Analysing Data
Now open the Microsoft Visual Round Trip Analyzer and Open the *.cap file.

On the first tab, you will see the Main Chart, a graphical representation of the captured activity. The vertical scale shows TCP ports from Client to Server, and horizontal scale shows the time.

On the below example I've highlighted two ports where the CRM address was used. Each coloured area represents a packet, hovering the mouse over the coloured area displays details of the specific packet.

Using the Zoom in button, Zoom to port: 59838 and the graph will start looking like this:

Technical description of the graphical representation.

On the statistics tab, you can get a breakdown of the traffic per content-type. Also the colours on this tab should represent the colours on the first tab. On the below screenshot you can see 31MB of data for Application/soap+xml; charset=utf-8 this is Outlook client traffic.

The tab all files, displays all activity per Frame. you can use the frame number as a packet identifier on the network monitor application. Also if you highlight any of the url's on this list, you get detailed information about the packet.

Identify one of the Frames you want to inspect in more detail from the first column. Open Network Monitor and open the same *.cap file you have loaded onto the Microsoft Visual Round Trip Analyzer. I'm going to pickup the Frame 1620, on the Network Monitor locate the Frame number 1620 and you then should be able to inspect this in greater detail as per the below screenshot:

3. Identifying Performance Bottlenecks
Now the interesting Analysis tab, I think this is a great feature, it scores the traffic recorded. The process is simple, the tool analysis the traffic and scores A to F, (good to bad). Displays the results on a list and highlights the scores in different colours (green, orange, red) describing what is being measured and scored. The below screenshots illustrates this feature:

Hover the mouse over one of the scores and you get detailed information about the score.

4. Summary 
Hope you find this tool and this post useful.

• Microsoft Network Monitor captures all network traffic on a specific interface
• *.cap files can be opened by Microsoft Visual Round Trip Analyzer for a more friendly analyse of the traffic
• Microsoft Visual Round Trip Analyzer built-in feature helps identify network and application performance bottlenecks
• Identify specific TCP/IP frames to inspect in greater detail within Microsoft Network Monitor

Dynamics CRM 2011 Map solution with OpenStreetMap

I have seen a few tutorials online regarding how to set up Maps with Google or Bing, however this solutions require a license depending how you use them so why not use a free and powerful opensource map solution on your Dynamics CRM 2011 with OpenStreetMap? And the most interesting part you can do that with only two simple configuration steps.

OpenStreetMap is a powerfull worldwide map created by volunteers. You can read more here: http://wiki.openstreetmap.org/wiki/Main_Page

To implement OpenStreetMap on Dynamics CRM 2011 we use the following services:

• GeoNames (to lookup post codes) -  http://www.geonames.org/
• OpenLayers (free javascript map library) - http://www.openlayers.org/
• OpenStreetMap layers
• Jquery

Step 1 - Create a HTML webresource
 Navigate to Settings > Customizations > Customize the system > click on web resources and click new.

Click Text Editor > click tab source > < paste the below code:

When you save it, you can ignore the error messages $. is undefined. Save and Publish.

Step 2 - Customize your entity

using the account entity start by creating a new section called map:

1.  Customize the main Account form, click the insert tab and select One column Section.

2. Confirm is set to one column and click OK

3. High light the section on the form and go to insert > Webresource and add the osmap_loader

 

Click ok, Save and Publish.

Result

Open an account make sure the postal code is not empty:

You can navigate around, and zoom in and out.

Summary
Hope you enjoyed this tutorial, is pretty straight forward and completely free of charges.


GeoNames
The solution above uses the Postal code lookup API from GeoNames however GeoNames have  more API's that can search for addresse's cities etc, lookup GeoNames services here:

• http://www.geonames.org/

Note: On the javascript above code I'm using username=nrodri which is my username, you can register an account for free and replace my username. with a custom username the service gives you 30k lookups a day.

Performance
Note that I'm loading the jquery library and openlayers library on the form, if you have a heavily customized form, this can add some extra load time, my advise would be to collapse the tab and allow users to expand the map as they needed, this way the map will only be loaded when you expand the tab.