Blogs

   
Michael Petrov
Co-Founder, CEO
1/29/2013
The Microsoft Dynamics Slow Down

After purchasing a Microsoft Dynamics tool through a third party company, a client of ours encountered a severe speed problem.  Typically a Microsoft SQL server will store best practice execution plans to a cache for queries. This way the plan can be instantly reused without any recalculation.

Pulling up the execution plan wasn’t so easy this time around.  We ran the SQL profiler searching for answers and found that query was not actually defected but completely flooded! The execution plan had duplicated thousands of records of this same information. The query engine became so bloated with repeated files that it wasn’t able to pick up the correct plan, thereby causing a significant slowdown in the system’s performance.

We found that the server was not overloaded in terms of IO or CPU. When we tried to execute the query from SSMS it ran quickly.

Here is a sample of the query that was causing slowness when executed from Dynamics:

SELECT

A.JOURNALNUM,A.LINENUM,A.ACCOUNTTYPE,A.ACCOUNTNUM,A.COMPANY,A.TXT,

A.AMOUNTCURDEBIT,A.CURRENCYCODE,A.EXCHRATE,A.TAXGROUP,A.CASHDISCPERCENT,

A.QTY,A.DIMENSION,A.DIMENSION2_,A.DIMENSION3_,A.DIMENSION4_,A.DIMENSION5_,

A.DIMENSION6_,A.DIMENSION7_,A.DIMENSION8_,A.DIMENSION9_,A.DIMENSION10_,

A.BANKNEGINSTRECIPIENTNAME,A.ENTERPRISENUMBER,A.PRICE,A.INTERCODIMENSION,

A.INTERCODIMENSION2_,A.INTERCODIMENSION3_,A.INTERCODIMENSION4_,A.INTERCODIMENSION5_,

A.INTERCODIMENSION6_,A.INTERCODIMENSION7_,A.INTERCODIMENSION8_,A.INTERCODIMENSION9_,

A.INTERCODIMENSION10_,A.SETTLEVOUCHER,A.POSTINGPROFILE,A.VOUCHER,A.AMOUNTCURCREDIT,

A.PAYMENTSTATUS,A.CASHDISCAMOUNT,A.ACKNOWLEDGEMENTDATE,A.VENDTRANSID,A.CUSTTRANSID,A.REMAINAMOUNT,

A.TAXCODE,A.OFFSETACCOUNT,A.BANKDEPOSITVOUCHER,A.PAYMREFERENCE,A.BANKDEPOSITNUM,A.PAYMSPEC,

A.PAYMMODE,A.MARKEDINVOICE,A.OFFSETACCOUNTTYPE,A.BANKTRANSTYPE,A.OFFSETCOMPANY,A.OFFSETTXT,

A.TAX1099AMOUNT,A.TAX1099BOX,A.PAYMENTNOTES,A.MARKEDINVOICERECID,A.FOREIGNVOUCHER,

A.FOREIGNCOMPANY,A.TRANSFERRED,A.CANCEL,A.NOEDIT,A.INVISIBLE,A.TRANSDATE,A.TRANSACTIONTYPE,

A.DOCUMENTDATE,A.DOCUMENTNUM,A.APPROVED,A.APPROVEDBY,A.PAYMID,A.FREQVALUE,A.FREQCODE,A.DUE,

A.DATECASHDISC,A.TRANSFERREDBY,A.LOADINGDATE,A.FILECREATED,A.PAYMENTACCOUNT,A.INVOICE,

A.TRANSFERREDTO,A.TRANSFER,A.LASTTRANSFERRED,A.TRANSFERREDON,A.PURCHLEDGERPOSTING,A.BANKCHEQUENUM,A.PAYMENT,

A.CASHDISCCODE,A.EXCHRATESECOND,A.TRIANGULATION,A.TAXITEMGROUP,A.VATNUMJOURNAL,A.LISTCODE,A.FURTHERPOSTINGTYPE,

A.PURCHIDRANGE,A.POOLRECID,A.PREPAYMENT,A.IMPORTDATE,A.BANKCENTRALBANKPURPOSECODE,A.REASONREFRECID,A.BANKCENTRALBANKPURPOSETEXT,

A.CUSTVENDBANKACCOUNTID,A.PAYMENTSEQUENCENUM,A.ERRORCODEPAYMENT,A.FURTHERPOSTINGRECID,A.TAXDIRECTIONCONTROL,

A.CUSTVENDNEGINSTPROTESTREASON,A.BANKREMITTANCETYPE,A.BANKREMITTANCEFILEID,A.BANKACCOUNTID,

A.BANKPROMISSORYNOTENUM,A.PAYMINSTRUCTIONID1,A.PAYMINSTRUCTIONID2,A.PAYMINSTRUCTIONID3,

A.PAYMINSTRUCTIONID4,A.BANKBILLOFEXCHANGENUM,A.NEGINSTID,A.TAXWITHHOLDGROUP,A.TAX1099STATEAMOUNT,

A.TAX1099STATE,A.REMITTEECURRENCY,A.SALESTYPE_SA,A.SALESID_SA,A.SUBCONTRACTORNUM_SA,

A.CONTRACTNUM_SA,A.ASSETGROUP_SA,A.PROJID_SA,A.REVERSEENTRY,A.REVERSEDATE,A.REVRECID,A.INVOICEDECLARATIONID_IS,

A.BANKRECONCILEACCOUNTATPOST,A.BANKCHEQUEDEPOSITTRANSREFRECID,A.MARKEDINVOICECOMPANY,A.RELEASEDATE,

A.RELEASEDATETZID,A.CUSTEINVOICEPAYMDELIVERYNUM,A.CUSTEINVOICEPAYMSECTIONNUM,A.CUSTEINVOICEPAYMTRANSNUM,

A.GSTHSTTAXTYPE_CA,A.FININTERESTAMOUNTCUR,A.BORDEROSEQUENCENUM_BR,A.BORDEROPAYMREFERENCE_BR,A.ONEVOUCHER_BR,

A.FINEAMOUNTCUR,A.PAYMENTTYPE,A.PAYMENTWAY,A.COMPANYDOCUMENT,A.TAXWITHHOLDCALCULATE_TH,A.TAXWITHHOLDALTERNATEVENDOR8027,

A.TAXWITHHOLDITEMGROUP_TH,A.REFRECID,A.CREDITFLAG,A.TAXWITHHOLDEDITSTATUS,A.ATTACHMENTS_CN,A.VOUCHERTYPEID_CN,

A.LEDGERACCOUNT_CN,A.LEDGERACCOUNTOFFSET_CN,A.TRACE_CN,A.FINALUSER,A.CFOPID,A.SERVICEINVOICE,A.TAXWITHHOLDCALCULATE,

A.INVOICEFINECODE,A.INVOICEFININTERESTCODE,A.INVOICETYPE,A.CUSTSUFRAMA,A.CUSTSUFRAMAPISCOFINS,A.CUSTSUFRAMANUMBER,

A.VOUCHER_CN,A.TAXWITHHOLDFBTCATEGORYID_IN,A.TAXWITHHOLDFBTTRANSTYPE_IN,A.EXCISETARIFFCODE_IN,A.CUSTOMSTARIFFDIRECTION_IN,

A.CUSTOMSTARIFFCODE_IN,A.SALESTAXFORMTYPE_IN,A.ASSESSABLEVALUE_IN,A.MAXIMUMRETAILPRICE_IN,A.NAME_IN,A.ADDRESS_IN,

A.ECCNUMBER_IN,A.EXCISERECORDTYPE_IN,A.IECNUMBER_IN,A.STCNUMBER_IN,A.TIN_IN,A.SALESTAXREGISTRATIONNUMBER_IN,

A.SERVICECODE_IN,A.GTASERVICECATEGORY_IN,A.NONRECOVERABLEPERCENT_IN,A.VATGOODSTYPE_IN,A.STATE_IN,A.CONSIGNMENTNOTENUM_IN,

A.CUSTOMSDUTY_IN,A.TDSGROUP_IN,A.TCSGROUP_IN,A.NATUREOFASSESSEE_IN,A.TAN_IN,A.INTERCOMPANYCOMPANYID_IN,A.CITATAXBASE,

A.KUMREMITTANCEPRINTED,A.KUMEXTENDEDINVOICEID,A.MODIFIEDDATETIME,A.RECVERSION,A.RECID,B.REFRECID,B.ASSETID,B.BOOKID,B.TRANSTYPE,

B.COMPANY,B.BUDGETMODEL,B.BUDGETPOSTINGSTATUS,B.CONSUMPTIONQUANTITY,B.DEPRECIATIONSTARTDATE,B.DEPRECIATIONTIME,B.REFASSETID,

B.RESERVETRANSID,B.REVALUATIONAMOUNT,B.REVALUATIONTRANS,B.LOWVALUEPOOLTYPE_AU,B.LVPTRANSFERID_AU,B.ASSETGROUP_IN,B.RECVERSION,

B.RECID,C.NEGATIVENETBOOKVALUE,C.EXCEEDINGNETBOOKVALUE,C.RECID FROM LEDGERJOURNALTRANS A,LEDGERJOURNALTRANS_ASSET B,ASSETBOOK C

WHERE

((A.DATAAREAID=N'yus4') AND (A.JOURNALNUM=N'10013293'))

AND ((B.DATAAREAID=N'yus4') AND (A.RECID=B.REFRECID))

AND ((C.DATAAREAID=N'yus4') AND (((((C.ASSETID=A.ACCOUNTNUM) AND (C.BOOKID=B.BOOKID)) AND (A.ACCOUNTTYPE=5)) AND ((B.TRANSTYPE<>8) AND (B.TRANSTYPE<>9))) AND ((C.NEGATIVENETBOOKVALUE=0) OR (C.EXCEEDINGNETBOOKVALUE=0))))

ORDER BY A.DATAAREAID,A.ACCOUNTNUM,B.DATAAREAID,B.BOOKID

 
We have since cleared the execution plan cache.

DBCC FREEPROCCACHE

And we have also saw that token cache size was somewhat high, so cleared that also.

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

Performance seemed to go back to normal after this. Although our research and troubleshooting led us to a quick fix solution, this solution may only be temporary. We are looking to unveil a permanent solution this problem.

   

Replies

Leave a reply

Name (required)
Email (will not be published) (required)

Number from the image above
  
Latest blog posts
VNX Versions
11/10/2014
Subscribe to the blog by e-mail

Sign up to receive
Digital Edge blog by e-mail


Subscribe    Unsubscribe