Query execution time of __ seconds exceeded the threshold of 10 seconds.

(0) Share

Report

Posted on by Rahul-Waghmare

990

Hello All,

I am getting this warning continuously in event viewer, even though I have applied proper indexing on CRM base table. If I run the same query on SQL studio I can see the results in 02 secs.

What is the difference between having the index on extension table and on base table?

Could anyone please suggest the resolution for this error.

Note: Increasing the value of IntColumn in "ServerSettingsProperties" table of MSCRM_Config wont resolve the warning it will increase the threshold value and it wont be captured in event viewer.

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

Shantnu Sharma 655 on at

Like (0)

Report

Hi Rahul - The base table stores the data for the OOTB fields and the extension table stores for custom attributes. So these are different tables and would require indexing individually. CRM fetches data from filtered views which is built upon both these tables (and related entity tables as well). Could you please elaborate on the error? Any specific table/view it is pointing to?

Was this reply helpful? Yes No
ScottDurow 21 on at

Like (1)

Report

The events you are seeing are purely for monitoring purposes - AFIK you cannot turn them off.

Was this reply helpful? Yes No
tpeschat 4,930 on at

Like (0)

Report

Hi,

Did you already run the statements directly on the SQL server and then review the execution plan. Usually there would be proposals in the execution plan, to add additional indexes, if the index wouldn't be sufficient.

Hth Thomas

Was this reply helpful? Yes No
tpeschat 4,930 on at

Like (0)

Report

Does it happen All the time, or only randomly? Because e.g. locks on the table due to ongoing Transaktion (e.g. running imports ...) Could Cause such warnings as well

Was this reply helpful? Yes No
Suggested answer

Kumar Viju 1,026 on at

Like (0)

Report

Hi,

We had similar warnings in our Client Production Environment wherein Query execution timeout was anywhere between 12 seconds to 28 seconds.. There may be multiple areas which you may need to work on to check possible solution and monitor the performance of your server periodically.

Issues in this specific areas.

a. Emails

b. Custom Entity used for Logging

c. Notes ( Attachments )

1. Check the Hardware Configuration of your CRM Application Server and SQL Instance. ( Make sure the Application has recommended RAM and Processor / Do verify same on the SQL RAM and Processor allocated).

-Depending upon the load on the servers , check the possibility of increasing the hardware resource.

2. Check the Table Size and the records count for the entity where you are getting query execution.

-Check if your organization has any Data Purging Policy and try to purge the old records.

3. Check the Plugins / Custom Workflows (Syn Workflows as well as Async). Also check if you have Web Service's or Windows Job.

-Make sure there are no reduntant codes or multiple updates or reterive.

-Check the possibilty to club your Wokflows or Plugin Logic in Single Message where ever possible.

-Optimize your code. Check the Select querys and the columns reterived in your custom code. This is one the areas where i have seen developers setting reterive all columns in query expressions which is unnecessary.

-Check CRM System Jobs for any waiting/cancelled/failed workflows.

4. Perform Load Testing. Create Console Application and see how the system is reacting for the entity's which are throwing timeouts.

5. Check Errors and Failed Workflows in event viewer and see if you can realted to this time out.

In short check overall performance and make a check list on the areas where you can optimize your system.

Was this reply helpful? Yes No
Rahul-Waghmare 990 on at

Like (0)

Report

Hi Shantnu,

Thanks for your reply. In event viewer I can see most of the when CRM fire select distinct top 51 on Incident table it shows warning.

Regards,

Rahul

Was this reply helpful? Yes No
Rahul-Waghmare 990 on at

Like (0)

Report

Hi Vijay,

Thanks for brief checklist. We are getting this warning on Incident & Activity Pointer entity whenever Select Distinct 51 query fired from CRM.

Regards,

Rahul

Was this reply helpful? Yes No
Rahul-Waghmare 990 on at

Like (0)

Report

Hi Scott,

Yeah, I agree its just the warning messages but what if we are able to handle this then performance may get improve.

Regards,

Rahul

Was this reply helpful? Yes No
Rahul-Waghmare 990 on at

Like (0)

Report

Hi Thomas,

It happens all the time specifically for Case entity.

Regards,

Rahul

Was this reply helpful? Yes No
Kumar Viju 1,026 on at

Like (0)

Report

Did you check the ActivityPointerBase and Incident record count or table size. Possibly the records may be in millions and therefore it is throwing timeout.

Was this reply helpful? Yes No