x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 11 replies
  • Subscribers 75 subscribers
  • Views 2095 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
arnolda_1

Apache Tomcat SQL Statements

Posted By
Running some monitoring on our system it appears that the same two SQL queries are constantly being run against our CRM database. My belief is that they are being triggered by the tomcat services as
1. SQL is reporting that the program name is "Microsoft JDBC Driver for SQL Server" - as far as I am aware the core eWare dll does not not rely on java
2. If the tomcat service is stopped then these queries cease to run
The two queries in question are
1. SELECT crmid, view_EntityName, view_RecordId, index_descriptor, territory, channel, assignedTo, createdBy FROM vEmailComposer WHERE crmid ='ENTITY-00000-0000'
2. (@P0 nvarchar(4000))SELECT epd_entityid, epd_Recordid, epd_EmailAddress FROM crmemailphonedata WITH (NOLOCK) WHERE epd_EmailAddress=@P0
ENTITY can be either company or person.
Does anyone know the exact CRM functionality which generates these queries? My suspicion is that it is the Exchange synchronisation, possibly with relation to external attendees but without access to the source code I cannot be sure.
It appears that both these queries are placing locks on the database tables which are then hindering the operation of CRM.
  • 0

    Thank you for that Jeff.

    Within the article the following are stated

    • It is entirely maintained by triggers on the PhoneLink and EmailLink table, it is never updated directly.
    • It is really an internal table and we would not advise anyone trying to update it or read it directly. Definitely do not update it!

    Does this mean that CRM should never read the CRMPhoneEmail directly? In which case is the statement being generated by the trigger?

    in addition the first query mentions the view vEmailComposer. My belief is that this is being called by a function within Tomcat but it would be great to know where exactly this is being called from.

  • 0

    Alison

    I don't have any more information about this I am afraid. I'll try and speak with a colleague in Development.

  • 0

    Hi Alison,

    Try stopping Quick Find this might be coming from Quick Find.

    Regards,

    Ravi

  • 0

    I wonder whether this has anything to do with an issue which has been resolved in 2018 R3

    0-170662-QA

    Performance/Scalability

    Sage CRM calendar caused a performance degradation on client computers.

    This issue is fixed.

    There is obviously an issue here, otherwise there would not be a fix. The 'responsive' calendar is powered by the Tomcat service and I guess may access the CRMPhoneEmail table (although unlike the legacy calendar does not display phone numbers on tasks)

  • 0

    The issue 0-170662-QA, from what I believe, was due to the Kendo UI.

  • 0

    Yes the CRMPhoneEmai table is updated by triggers. Then the Person and Phone Views tabs use it to see the records. If you are concerned about locks on the database watch this video www.brentozar.com/.../how-to-prove-your-sql-server-needs-more-memory-video

  • 0

    I don't think that this is a server resource issue. We have noticed a number of inconsistencies between data held within the Phone / Email tables and the CRMEmailPhoneData tables. When this inconsistency occurred we are not sure, all of the standard triggers are enabled. In order to combat this we have been going into affected records, removing phone numbers and email addresses, saving the record and re-populating. In order to find affected records I written have the following script

    select

    pers_personid,

    comp_companyid,

    rtrim(comp_name) as Company,

    rtrim(pers_firstname) + ' ' + rtrim(Pers_LastName) as Person,

    rtrim(epd_EmailAddress) as Header_EmailAddress,

    rtrim(Emai_EmailAddress) as Linked_EmailAddress,

    rtrim(epd_PhoneCountryCode) as Header_PhoneCountry,

    rtrim(epd_PhoneAreaCode) as Header_PhoneArea,

    rtrim(epd_PhoneNumber) as Header_PhoneNumber,

    rtrim(epd_PhoneFullNumber) as Header_PhoneFull ,

    rtrim(Phon_CountryCode) as Linked_PhoneCountry,

    rtrim(Phon_AreaCode) as Linked_PhoneArea,

    rtrim(Phon_Number) as Linked_PhoneNumber

    from Person

    left join company on comp_companyid = Pers_CompanyId

    left join CRMEmailPhoneData on epd_Deleted is null and epd_EntityID = 13 and epd_RecordID = Pers_PersonId

    left join PhoneLink on PLink_EntityID = 13 and PLink_Deleted is null and PLink_RecordID = Pers_PersonId and PLink_Type = 'Business'

    left join Phone on PLink_PhoneId = Phon_PhoneId

    left join EmailLink on ELink_EntityID = 13 and ELink_Deleted is null and ELink_RecordID = Pers_PersonId and ELink_Type = 'Business'

    left join Email on Emai_EmailId = ELink_EmailId

    where pers_deleted is null

    and (

    not (isnull(epd_EmailAddress,'') = isnull(Emai_EmailAddress,'')) or

    not (isnull(epd_PhoneCountryCode,'') = isnull(Phon_CountryCode,'')) or

    not (isnull(epd_PhoneAreaCode,'') = isnull(Phon_AreaCode,'')) or

    not (isnull(epd_PhoneNumber,'') = isnull(Phon_Number,''))

    )

    order by comp_name,Pers_FirstName

    Interestingly this morning attempting to run this script resulted in the following error

    Msg 1205, Level 13, State 52, Line 3

    Transaction (Process ID 137) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    I did not have SQL profiler running at the time so did not get a deadlock graph. Running the script again it sat executing for over 5 minutes before it was terminated. Stopping the tomcat service and restarting it the script ran in around 3 seconds.

    There is an issue where the tomcat service is locking tables, especially with databases which have been upgraded from older versions of the software.

  • 0

    arnolda_1 did you ever find resolution for this issue?  Having the same problem here...

  • 0 in reply to ptronic

    There are two issues here selecting data from CRMEmailPhoneData table and selecting data from vEmailComposer.    Regarding vEmailComposer it is related to Quick Find like Ravi said.  And Dublin is working on ticket (711-171938) on Quick Find taking up excessive up SQL.EXE CPU on the SQL server with a 30 GB database.  

*Community Hub is the new name for Sage City