SQL Timeout solutions in Dynamics CRM 2011

Mostly this kind of problems could come from Plugins, if there are any plugins executed on that action.

Increase the default timeout

Registry on CRM applicationserver(s)

1. Open CRM Web Server(where CRM is installed)
2. Open regedit (start – > write ( regedit) ) and press ok
3. Go to HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSCRM
4. Add DWORD name OLEDBTimeout with value 86400 ( 24 hours) select decimal
5. Add DWORD name ExtendedTimeout with value 1000000. The maximum value can be 2,147,483,647 and the default value is 30 seconds

These changes in register should fix the SQL timeout.

1. Web.config
   1. <httpRuntime executionTimeout=”300″ />
      1. .NET 3.0: “timespan” attribute. The default is “00:01:50” (110 seconds)
      2. .NET 3.5 and 4.0: an integer in seconds. Default is 110 seconds.
      3. Specifies the maximum number of seconds that a request is allowed to execute before being automatically shut down by ASP.NET.
   2. IIS/ASP.NET configuration
      1. IIS 6.0 -> Website Properties -> ASP.NET tab -> Edit Configuration button -> Application tab -> Request execution timeout (seconds)
         1. Related to this type of situation in CRM 4.0:http://blogs.msdn.com/b/crm/archive/2008/11/20/asp-net-2-0-50727-0-warning-event-id-1309-due-to-request-time-out.aspx
   3. When using the CRM SDK, there are also timeout settings that can be set via custom code:
      1. CRM 4.0 example:
         1. CrmService service = new CrmService();
         2. service.Timeout = 300;
         3. In milliseconds and default is 100,000
      2. CRM 2011:
         1. ServiceProxy.Timeout property (Timespan)
   4. Identify the SQL query which takes long time to execute, when user clicks on “Resolve Case” button
   5. Use the SQL Server Management Studio and SQL Profiler to identify the query execution plan and to get recommendations about possible indexes
   6. Run the SQL profiler after settings the following events?

Identify the SQL Query

RPC: Completed
SQL: Batch Completed
SQL: Batch Starting

1. Make sure you check the text data filed and the CRM database name filed and filer the profiler trace for CRM databases only so that it can only capture the CRM related query execution in the trace.
2. Go to the point where you are relay to reproduce the issue and then turn on the Tracing from the profiler. Stop the tracing immediately after the issue reproduced.
3. After we find out the query and entity which is taking too long to execute, we can create indexes on that entity

Note: There are 2 important things to take into consideration when you are adding indexes:

1. It is unsupported to add SQL indexes to the MSCRM database. My view on this is that, as long as the index does not implement any constraints (i.e. it’s not a UNIQUE index) then you will affect the stability of CRM; you may however need to drop the index prior to upgrading CRM.
2. Although adding an index may improve the performance of one query, it can adversely affect other SQL operations – most obviously data updates. There is no easy solution to this,though the SQL Profiler can help you if you capture and analyse a representative sample of SQL operations

Change the Plugin code

1. If the Plugin code (e.g. through a RetrieveMultiple request) is used to query the data
2. For example, RetrieveMultiple requests on activities are not necessarily processed very efficiently by CRM (the problem is the way that is accesses the activity parties) , sometimes DB locks can happen using this.
3. As far as I know FetchXml works more efficiently and makes significant improvements than RetrieveMultiple request. It gives closer control over the joins used. We can also specify SQL Server DB no locks in FetchXml

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false” no-lock=”true” ></fetch>

Advertisement