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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Performance Issues
Microsoft Dynamics SL (Archived)

Performance Issues

(0) ShareShare
ReportReport
Posted on by Julie Theiringer 80

Greetings,

 
We are currently having some major "slowness" issues within Solomon (ver 6.5, running on SQL '05).
 
From the SQL Profiler traces I have done it appears the MAJOR offender is:
 
DMG_UpdateInventory_ComputerName
 
This stored procedure of course calls several other SP's and I am guessing it is fairly well known in the Dynamics-SL world, so am hoping some of you can give me some better information about it as I have been unable so far to find ANY solid documentation on it online.
 
It SEEMS to me to be related to Order Process Management but I can't figure out why it is consistently taking longer than 2000 milliseconds and doing large (sometimes huge) reads/writes to disk.
 
In addition, I have noticed that the ProcessQueue table appears to get around 600-900 rows at the same time we are running into the slowness issues where users get Batches (anywhere from 1-25 at a time) that are taking 30 minutes to release.
 
My gut tells me it is something with the Process Managers but I again can't find any decent documentation on what they are doing while they are doing the Planning and what Tables/SP/Views I should be looking at.  For example, today even a single Sales Order was taking forever to print and the Process Manager would get all hung up.  The only thing we could do was take the PM offline and DELETE all rows from the ProcessQueue table then restart it...
 
We were able to optimize a few of our custom Views by adding "WITH (NOLOCK)" statements (and an Index or two) but this really only made the custom pages load as fast as they should.  The rest of our current issues are all around stock stuff.  Worse, this slowness started occuring about 3 weeks ago and we had made no modifications to our SAN, Network, Servers or Solomon configs  Confused
 
Thank you in advance for reading this and any and all help you can provide!

*This post is locked for comments

I have the same question (0)
  • Paul Phillips Profile Picture
    Paul Phillips 590 on at

    Hi Julie,

    As I'm sure you know there can be many causes of performance issues. It sounds like you have narrowed it down to the database. The following are things I have seen in the past related to process manager slowdowns that are not general environment or IT issues.

    1. You might check for custom triggers that are interfering with process manager, especially on SOPlan, ItemSite, or related inventory and SO tables. I use something like this to get a list of triggers to investigate:

    SELECT 
      TableName     = tables.name, 
      TriggerName     = triggers.name
    FROM 
      sysobjects triggers
    INNER JOIN
      sysobjects tables ON
      triggers.parent_obj = tables.id
    WHERE
      triggers    .type = 'TR'

    I've seen this iisue before where a rather sloppy trigger was put on ItemSite without considering the impact on Process Manager. In that case it was causing deadlocks in Process Manager between DMG_UpdateInventory_ComputerName and other procedures.

    2. If there are no offending custom triggers I'd suggest looking into other possible sources for locking issues. Adding NOLOCK hints to the views will help them run without blocking but probably won't have much impact on process manager unless they are very badly written and are themselves causing the performance slowdown.

    The question to ask when watching the SQL profile trace is: is this a general slowness, or does it appear to stall on one statement for awhile and then continue? If it is stalling then I would be inclined to think it is a lock issue. If it is a general slowdown then it will potentially require a lot of research and profiling to isolate.

    Process manager is a very database-intensive application and will slow down considerably if there are any issues accessing the tables used in inventory supply/demand planning.

     

    Hope this helps.

    Paul S Phillips

  • Julie Theiringer Profile Picture
    Julie Theiringer 80 on at

    Paul,

     Yes thank you, that was helpful.  That query is a nice tool for sure, and as I am sure you guessed was not our issue.

     We are not having the "stall" experience you described and instead was a more "general" slowness.  We've been hunting and researching this for almost two weeks now and I think we nailed it.  If nothing else, your comments confirmed a lot of our own thinking.

     I am pretty convinced at this point that the culprit is an internally-developed Web App that is accessing these tables and that is what is choking Process Manager.  So sadly appears it is a more political ;) issue than technical.

     Thanks again for your time.

  • Community Member Profile Picture
    Community Member on at

    Julie

    I experienced the same problem when we where on 6.5.  The problem is that they are forcing incorrect indexes to be used.  I nolonger have my 6.5 DBs around to look at but the code below will locate where they are forceing the use of index SOShipLot1 which is not the correct index.  The index that I have some documentation on is SCM_Plan_QtyAlloc I change in 4 locations the code " WITH (INDEX(SOShipLot1,NOLOCK)" to "(NOLOCK)".  This allows SQL server to decide the best index to use in these cases the SOShipLot0 is the best if the other index was not forced it would have used it.

     I had worked with microsoft on a large list of performance improvements that they had put together a "Performance Pack" for 6.5.  If I am able to locate the information I will provide it.

     run this in your application DB to locate where they are forcing indexes.

    select O.[name],o.xtype,c.[text]

    from sysobjects O

    inner join syscomments C on c.[id] = o.[id]

    where o.xtype in ('P','V','TR','FN','TF') -- V View, P Proc, TR Trigger, U Tables,FN Scaler Functions, TF Table Functions

    and c.[text] like '%(INDEX(SOShipLot1%'

    order by o.name

  • Community Member Profile Picture
    Community Member on at

    As it turns out I did a presentation a couple years ago for the Dynamics SL User Group an this was my exact example.  Here is the link

    http://www.toledofilehost.com/DynamicsSLUserGroup/ 

    The presentation is the one on SQL Using Progfiler to improve SL Performance.ppt.

  • Julie Theiringer Profile Picture
    Julie Theiringer 80 on at

    Thank you, yes, I did go through and make a few of those changes. I have a consultant coming in next week who worked on one of our custom pages so hopefully he'll be able to speed that one up somewhat as well.

    I do seem to see some performance gains from these optimizations but then inexplicably it seems like the slowness creeps back in. So weird is it to me that I have gone to great lengths to rule out hardware twice now.

    Currently in Profiler the great offender from the Stock Solomon stuff is DMG_CheckMissingHistoryRecords

    It seems to come up a LOT and run for a very long time. It creates a kind of temp table called #RangeFiscYears but I can't figure out where that table gets removed (which I assume SOMETHING does remove it since otherwise the Create statement wouldn't work in the original SP)

    Anywho, I really appreciate your scripts they are VERY helpful in tracking stuff down, so if you have another free minute or idea on this particular Stored Proc (the DMG_CheckMiss... one) I welcome your insight. However I know its a busy battle out there lately so no worries.

     -Julie

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

Quick Links

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans