Thursday, 4 September 2008

SQL Timeouts in CRM - Generic SQL Error

I often find myself answering forum posts about SQL errors (the most common error is 'Generic SQL Error'). By far the most likely cause of this error is a timeout when accessing SQL server. If this is a case your options are to increase the timeout, or to try and ensure the query does not take so long.

Increase the Timeout
The SQL timeout is controlled by a registry value on each CRM server:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\OLEDBTimeout

This value does not exist by default, and if it does not exist then the default value will be 30 seconds. To change it, add the registry value (of type DWORD), and put in the value you want (measured in seconds). Then you have to recycle the CrmAppPool application pool for the change to take effect (this is a step most Microsoft documentation omits to mention); do this we IISReset, or less drastically via iisapp.vbs

Reduce the time taken by the query
This may not be so simple, as you may have little control over the query. If the query is run as a result of your code (e.g. through a RetrieveMultiple request), then you may be able to make useful changes. 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), and I've been able to make significant improvements by using a FetchXml query instead, which gives closer control over the joins used.

Otherwise, the other query optimisation option is to add indexes in SQL Server. This is a massive topic in its own right (I used to deliver 5 day training courses just on this topic), so I'm not going to go into detail here. The general steps are:
  1. Identify the SQL query that takes the time - I use CRM tracing for this - http://support.microsoft.com/kb/907490
  2. Use the SQL Management Studio and SQL Profiler to identify the query execution plan and to get recommendations about possible indexes

There are 2 important things to take into account:

  1. 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
  2. Some out-dated CRM documentation suggested that it is unsupported to add indexes to the MSCRM database. However, adding indexes is supported, providing the index does not implement any constraints (i.e. it's not a UNIQUE index)

8 comments:

Jeffry @ CWR Mobility said...

Hi David,

Indeed I read in the unsupported url that adding indexes is not supported, but I also remember the CRM 3.0 performance whitepaper that talks about adding indexes to improve performance.

I guess that whitepaper is not valid for CRM 4.0 anymore then...

David Jennaway said...

Jeffry

In the CRM 3.0 SDK it also said that adding indexes was unsupported, but then refered stated that changes made based on the performance whitepaper (which suggests adding indexes) were OK. In the CRM 4.0 SDK there is no reference to any performance whitepaper.

So, a strict reading of the CRM 4.0 SDK says you cannot add SDK indexes, but I'm not sure if this is correct. I'm chasing this up with Microsoft at the moment, and will post any updates on this here

David

firedave said...

Hi David,

I great raft of information on your blog - really awesome effort.

I have had a strange issue lately with timeouts but not on the running of SQL - this is fine. But if the window in CRM has been open a while (15 mins) then if you rerun the report without closing the window then it errors. eg Execution 'nmlcqg551jpyeh55keagoynw' cannot be found (rsExecutionNotFound)

Many references say increase the Reporting services timeout (which makes no difference)

Have you come across this issue of have any idea here?

David Jennaway said...

I don't think that increasing the reporting services timeout will help with a rsExecutionNotFound error in this scenario.

What I expect is happening is that RS will typically retain an execution snapshot of the returned data when you run a reports. If you rerun the report with the same parameters it will reuse the snapshot data - this is done for performance reasons.

These snapshots are retained for a configurable amount of time per report - I expect what's happening is the snapshot has expired, though I would normally expect RS to just rerun the queries in that instance. Your problem maybe because you haven't closed the window.

You can modify the snapshot settings in Report Manager. Go to the properties of the report, then Execution, and select either 'Do not cache temporary copies...' or modify the number of minutes to cache the temporary copy

Dave Berry said...

Dave,

From the "Unsupported Customizations" article to which you linked:

"The following is a list of the types of actions that are not supported:
...
Modifications to the physical schema of the database, other than adding or updating indexes."

Reads to me like indexes are indeed supported.

David Jennaway said...

Dave

Thanks for spotting this. I think the documentation used to say that adding indexes was unsupported, but thankfully that has now changed. I've updated the post to reflect this

David

Varun Kumar said...

Is there a possibility to solve this issue online

i have a plugin which works fine when registered synchronously but throws a generic sql error when registered async

Ravi said...

Hi I am also facing the same problem, I am using MS CRM 4.0 and SSRS 2008 R2, after running the report it is working fine but after putting the report as idle for 10 minutes, again while using the same report without closing the window it is showing Time Out Error.

I have tried to change in OLEDBTimeOut, Web-config and as suggested with this article but didn't got any success