CRM 4.0 Notes tab SlowNess RRS feed

  • Question

  • I am facing problem with the Notes tab slowness. Its taking around 50 seconds to open notes tab and it's sporadic behavior. Annotation base table has around more than 30 Million rows. When there is slowness we see spike in number connections to query select MAX((POA.AccessRightsMask|POA.InheritedAccessRightsMask .

    We are using the MS CRM 4.0. Please guide me on this.

    Saturday, February 22, 2014 10:15 PM

All replies

  • Is sharing of records heavily used in your system?

    Did you try regular DB maintenance options: reindexing, partitioning etc?

    If this post is an answer or helpful, please do not forget to vote!

    Monday, February 24, 2014 9:50 PM
  • We don't use sharing heavily in our system and we do have proper db maintenance activities. While fetching notes, for each row POA.AccessRightsMask query runs which spike the cpu usage. Not sure how to get rid of this.  
    Tuesday, February 25, 2014 9:47 PM
  • The query on POA.AccessRightsMask is called to determine if the user has permission to the record through sharing. The only option I can think of that may remove this is if you granted organisation-level read permission on the annotation entity, as I believe CRM would not check the object-level permission if the user necessarily had organization-wide permissions. Whether this is acceptable from a security perspective is another matter

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Wednesday, February 26, 2014 9:30 AM
  • We already have organization-wide read permissions for Notes tabs. Really not understanding what causing the issue. It's s sporadic issue and happening for notes only. Is the archival of notes data would help?
    Thursday, February 27, 2014 7:18 PM
  • From what you describe of the SQL queries, then I would expect archiving notes would help, as it sounds like the POA query is executed for each note returned. However, it's very difficult to make an estimate of how much it would help.

    As the issue is sporadic, one underlying cause could be that the execution plan for the POA query (which should execute quickly with little sharing) is normally cached, but is occasionally flushed from the cache, and the compile time to recompile it could be the overhead. You might be able to identify this via the SQL profiler. Adding more SQL memory would reduce the likelihood of the plan being flushed, and ensuring you have enough space in TempDB might help

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, February 27, 2014 11:06 PM
  • I appreciate your help on this. Currently we are seeing more than 35 million rows in annotationbase table. We are planning to archive and delete the unwanted data. Could you please guide us on the below?

    1. Delete : If we directly delete the unwanted records from AnnotatioBase , would it be impacting anything else?

    2. Archive : Please suggest how we can do the archive of data of annotation base?

    3. Can we delete the records from Operationbase table?

    Thanks  in Advance.

    Friday, February 28, 2014 3:43 PM