Skip to main content

Notifications

Microsoft Dynamics RMS forum

Will many years of batch information effect performance

Posted on by Microsoft Employee

I have a customer that has been experiencing system slow downs during the transaction tender/posting process. The problem manifests as a slow down either when the tender screen displays, while trying to enter amounts in the tender screen, or when finishing the sale (slow response of receipt print, cash drawer pop, etc.).

This problem is very sparadic. We can reboot their server and things can run fine for days or weeks, then the registers come to a grinding halt, returning a "Not Responding" status at the time of tender. IF the customer waits it out, the register eventually catches up and finishes the sale. No RMS or SQL related error is displayed when this happens.

Environment:

Dedicated Windows 2003 server

Two registers, both running Windows 7

RMS 2.0 SP4 version 2.0.0159

SQL version 2008 Express R2 SP1

The database is 4,025,125

Recent steps taken to improve performance:

Database was reindexed (no improvement)

Database was backed up, dropped, and recreated using backup file (no improvement)

Database was shrunk (seems to have improved)

Database growth was changed from the default 10% to 20meg

Server was rebooted.

So far, one day after all of this, things seem to be running fine. However, I'm looking for other ways to optimize database performance. I've found that they have batches that date back 10 years in their system. The question is, if I use the published SQL commands to delete old batches (previous to a specific batch number), would that positively effect performance? If I delete those, will I in any way damange the accounts receivable information on their customers?

Thank you very much for any advice

Gregg

 

*This post is locked for comments

  • Gerald Rothaus Profile Picture
    Gerald Rothaus 2,930 on at
    Re: Will many years of batch information effect performance

    Windows Vista Business SP2?

    You mean Windows Millenium?

    You mean Bob (you have to be pretty old to remember that one)?

    I don't know, I mean Vista?

    All I remember about Vista was installing it, playing with it, and reinstalling XP!

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Will many years of batch information effect performance

    Jerry,

    Have you seen the tender pause issue with Windows Vista Business SP2?

    Thanks!

    Gregg

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Will many years of batch information effect performance

    Hi Shawn,

    I haven't really considered running the sample database because the issue we're seeing on the full production database happens rarely. The production database can run for days without issue, then we suddenly see the problem with the tender screen display.

    Thanks!

    Gregg

  • Shawn Meade Profile Picture
    Shawn Meade on at
    Re: Will many years of batch information effect performance

    Hello Gregg and Gerald,

    We have not heard of an issue like this in Windows 7 32 nor 64 bit version.  Store Operations will work in Windows 7 Professional or Higher.  

    As to the issue you are seeing rght now, I would recommend trying to run transactions from teh sample database to see if it also experiences this performance.  

    Warm Wishes,

    Shawn with Microsoft Retail Support.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Will many years of batch information effect performance

    Jerry,

    The registers are running Win 7, but they are 32-Bit. They are on SP4 2.0.0159 (hotfix 24).

    Gregg

  • Gerald Rothaus Profile Picture
    Gerald Rothaus 2,930 on at
    Re: Will many years of batch information effect performance

    Oooohhh...

    I've only seen that with win 7 64bit. Is that what you have?

    Did you run the feature pack 1 and the service pack 4?

    I haven't had good luck with the 64 bit win 7.

    Maybe Shawn can speak to that.

    -Jerry

    JR Data Inc.

    jerry@jrdata.us

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Will many years of batch information effect performance

    Everyone,

    Thank you for the thoughts and suggestions. To answer your question Shawn, there are no wireless connections related to POS functionality.  I've looked at the PCI Compliance guide and it basically says that unless you have a seperate, dedicated computer just to host the database, you're not working in a supported environment. An ideal situation, but not realistic in many small businesses.

    Gerald - The server we're working with contains the RMS database as well a handful of other, smaller databases. None of the applications experience performance lags, and have been tested for performance response when the registers slow down.  As for an anti-virus, we run Trend Micro on the server. We've disabled the anit-virus on the registers.

    Right now, it seems like the database clean up I did a few weeks back has helped tremendously. There have been no total system lockups. The only place they are seeing a lag is occasionaly when the tender screen begins to draw itself. The box will appear, the first tender name will show up, it might pause 3-5 seconds before the remainder of the tender screen displays. It might do this for a dozen transactions, and then things speed back up.

    Thanks!

    Gregg

  • Shawn Meade Profile Picture
    Shawn Meade on at
    Re: Will many years of batch information effect performance

    Hello Gregg,

    I am not sure what you mean by "rebuilding' the customer's accounts, but yes, as Gerald mentions, deleting the item would delete the information in the customer history.

    You will be able to see the price of the sale, but not the item that was sold.

    Also, I would recommend going through the PCI Compliance document for Store Operations 2.0.  This can be found on the download page for the release, or any service pack or feature pack.  You migth able be able to get this from your partner, if you no longer have access to Customer Source.

    Thank you Gerald for your suggestions.

    Also, Gregg, I would like to bring something up.  I have not seen it asked in the past, but are you using a Wireless connection?  This has been known to cause performance issues, and it also is not supported by Microsoft in the event you ever need to contact Tech Support.

    Please let me know if you have any further questions.

    Shawn with MIcrosoft Retail Support.

  • Gerald Rothaus Profile Picture
    Gerald Rothaus 2,930 on at
    Re: Will many years of batch information effect performance

    Hi.

    Just out curiosity, could there be something else going on here? Seems that the sql server performance would be fairly consistent, good or bad.

    Is it possible that something else is running on occasion, at what may seem to be sporatic times, like other programs, updates, viruses?

    Might be worth going through your machines and shutting down all but key processes (go to msconfig, services, hklm run, you know). Then bring them up one by one.

    Registers and servers don't need adobe products, overly-intrusive antiviruses, windows live stuff, etc.

    Try maybe looking for intrusive printer or photo programs, especially those from Brother, HP, Epson and Kodak. Do they still have Kodak? Well anyway...

    Yes, by the way, if you delete items it will affect the customer history.

    -J

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Will many years of batch information effect performance

    Hi Shawn,

    I have set both the maximum server memory and minimum memory per query to 1024. Perhaps by design, changing the maximum server memory doesn't seem to have done anything. SQL is typically using about 1.5 gigs of memory (per Task Manager). It did that before and after changing the maximum server memory.

    I've done quite a bit of database cleanup, deleting old journals, old batches, items that were added to the database that were never sold, etc. After all of that, I reindexed the database. This seems to have helped, but I have another question. There are items in the database that were added 6-7 years ago, and might have been sold once or twice. It's possible that any of those items might have been charged to a customer's account at the time of sale. Would it cause any problems to get if I deleted those items out of the database? Would it effect the customer's account information? If the accounts ever had to be rebuilt, would the loss of these items cause a problem?

    Thanks very much!

    Gregg

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

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,802 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,133 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,154

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans