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");
        }
    });


});

}

}