web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Advanced find database...
Microsoft Dynamics CRM (Archived)

Advanced find database blocking

(0) ShareShare
ReportReport
Posted on by Nic Jackson 25

Hi,

I have been looking into various performance issues with our CRM database and one which we have noticed very strange is regular database blocking which seems to originate from advanced find.

Several times a day the entire CRM system becomes non responsive for all users and upon investigation of the database there are blocking processes which appear to be attempting to create functions on the CRM database.  The two main offenders appear to be dbo.fn_GetCutoverTime and dbo.fn_FindUserGuid.

For some reason this will block all other processes for several minutes, can anyone help with a resolution or point me in the right direction.

Our set up is 2x webservers, 2x async servers, 1x reporting services, 1x SQL Server 2008 R2, with approximately 400 users.

I believe the version we are running has roll up 16 applied however I am still confirming this.

Kind regards,

Nic

 

  

*This post is locked for comments

I have the same question (0)
  • Nic Jackson Profile Picture
    Nic Jackson 25 on at
    Re: Advanced find database blocking

    Thanks for you help,

    The async table is something that we are keeping an eye on, we have a nightly job to clear down any old or failed jobs at present the size is 122,000 rows which I believe should not cause us any issues.

    Since my original post we have found out a little more information, when the user executes an advanced find to return 10,000 rows in excel, 50,000 page locks are taken out against the exported table.  I would have assumed that these should have been escalated to a table level lock but sometimes (mostly)  this does not happen.  I suspect the database is running out of lock resources and thus can not process any further requests.  

    I would have thought this would have been escalated to a table lock once 5000 page locks had been acquired?

    I have checked the sys,tables and it appears that the lock_escalation is set to TABLE and has not been changed.

    Kind regards,

    Nic

  • Jarrod Williams Profile Picture
    Jarrod Williams 1,747 on at
    Re: Advanced find database blocking

    Nic,

    Gus is on with looking at what's going on with async.  If you are looking at what is going on in a targeted period of the day I would also suggest using a tool called SQL Nexus that can be used to caputre data and then suggest things like Missing indexes to put into place.  That is available from the link below.

    http://sqlnexus.codeplex.com/

    Also, the white paper for for performance has good information.

    www.microsoft.com/.../details.aspx

    Jarrod Williams

    CRM Support Engineer

  • Suggested answer
    Gus Gonzalez Profile Picture
    Gus Gonzalez 27,113 on at
    Re: Advanced find database blocking

    Nic,

    Can you find out what is the size of your AsyncOperationBase table in SQL?

    I believe this is located on your Org_MSCRM database.

    If this database is big, try the steps included in this KB Article:

    support.microsoft.com/.../968520

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Our Community Code of Conduct has been updated!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
Aric Levin - MVP Profile Picture

Aric Levin - MVP 2 Moderator

#2
MA-04060624-0 Profile Picture

MA-04060624-0 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans