Thursday, March 29, 2012

exceeded the threshold of 10 seconds?

As you should know by now in case of mysterious CRM bugs always check the event viewer. It is the source of all kinds of events like: information, warning and our fav "errors".

Guess, the common one is the printer, yes we do not install those on servers. Nevertheless the event viewer logs are often overlooked and especially the warnings might give you some nice clues.

You might have seen something like "Query execution time of 10.6 seconds exceeded the threshold of 10 seconds." and also mentions your CRM database. In my case and think also others still might wondering, where the *&^% we can change those 10 seconds.

My first guess would be "treshold" settings in MS SQL server, but this is something completely different. It is some cursor stuff (stalk your dba). Hmmm what other settings we might have the most important are described in the following post:

http://blogs.msdn.com/b/crminthefield/archive/2011/06/13/microsoft-dynamics-crm-timeout-settings.aspx

Nevertheless, nothing mentioning those 10 secs....

My latest information about this famous 10 secs is, that it is internal warning of CRM. The warning is automatically send after, yes indeed an query exceeded the 10 seconds. It is just warning that some of your processes are taking too long, duh :)

Your best guess for the cause is in the use of fetch xml and accompied by database locks. To prevent this add the 'no lock' feature.

Happy threshold hunting

3 comments:

Peter H├Ârnell said...

I'm having this exact issue. Can't find where it says how to raise the 10 second threshold.

How do I add the "No lock feature"?

FriendlyCRMonster said...

Hi Peter,

I usually start with optimizing all CRM time outs. An useful link:

CRM 2011 time-outs

If you use custom sql query, you can add the option ' WITH (NOLOCK)'

Unfortunately in some cases the reason was unknown. Officially this is also informative message.

Regards,

Sandor

duco said...

I think with this query you can view the current setting:
select IntColumn from MSCRM_CONFIG..ServerSettingsProperties where ColumnName='LongQueryThresholdInSeconds'

With this query you can update the setting:
update MSCRM_CONFIG..ServerSettingsProperties set IntColumn = '15' where ColumnName='LongQueryThresholdInSeconds'

Good luck

Duco