Great Plains periodically running slow 5

[allanp0220]

Hi,
Great Plains 5.5, 12 workstations running NT4, Windows 2000 server, 2.2G memory.

Periodically operators involved in invoicing particularly report that the system is running slow - and I've seen it, its really slow like one line of an invoice every 10 seconds.

Usually I stop and start SQL server agent and then reboot the server to clear out the server memory. At first we thought this cured it but it seems this is not always so. In fact the server can have 1.3G memory free and they can still report it running slow - other operators are aware of it but not affected to the same extent. Sometimes, just occasionally, they report that its running fine!

I should point out that our database is now reaching 8G. Does anyone think this is causing the problem, surely if this were the case then the server would be using its full memory before it slowed. Although we know it has to be reduced I don't want to call our consultants out at vast cost to reduce the data only to find we still have the problem.

I'm not really an IT Manager, just got dropped into the position but I know that your'e a really knowledgable lot can anyone point out some likely checks I can do to try and pinpoint the problem?
Many thanks
Allan

 

[MentatDragon]

You may have a memory leak or a misbehaved application - or even spyware on the bad workstation. Try this:

1) Be sure that you have loaded the latest NT service packs.

2) Load a memory manager on the workstation (like CacheMan) and it will prevent memory problems.

3) Examine you Startup folder, AUTOEXEC.BAT and registry RUN and RUN SERVICES for stuff that shouldn't be loading at boot.

4) Run SPYBOT or AD-AWARE to clean off any spyware. (If you have internet access at the workstation.

5) If you can put a second hard drive into the workstation and move the SWAP file to it, that can help a lot. If you are having to use virtual memory, due to the leaks,footprints, etc. this could cause the slowness.

 

[jazgeek]

5.5 is pretty old, make sure everyone is on the same service pack and dex version. If I remember correctly there was some issues with 5.5 and invoicing slowdown.

Check your SQL jobs for transaction truncation and verify that if you have any online backups running that they are running properly.

I once had a sql log backup that didn't go for 3 months and my transaction log exceeded 4 gigs of HD space.... needless to say that slowed things down immensely.



If you have a few spare hours (probably 8 or 9) run a paid transaction removal... takes a while but it certainly speeds things up after it's been run.



-----------
and they wonder why they call it Great Pains!

jaz

 

[allanp0220]

Hi,
Thanks for your replies.

Mentatdragon, it appears to be a Great Pains problem rather than a particular workstation. Any workststion which was invoicing or posting at the time of the problem, would run slow. Other operations on all workstations are slower but not sufficient to cause the operators any dramatic problem.

jazgeek, thanks for those suggestions. I've checked the sql log backup, there doesn't appear to be any problem there. I'll run through the others during the day. Fortunately I can pass the paid transaction removal function to our Finance Director.

I'm very lucky in that I don't actually use Great Pains, but I do have to try and nurse the server along.

There are one or two companies on the web, in the US, who advertise a Company Data Archive programme for Great Plains which would be really useful and allow us to get rid of some old data but so far they have not responded to my emails for further information.
Cheers
Allan

 

[lpgforms]

we have a few clients who use Professional Advantage's "Company Data Archive" for Great Plains and are quite happy with it.


Brian Begley

 

[stef315]

You can also do your own archive:

1. Make a backup of your current company.
2. Create a new company like CoName99-02 or whatever to indicate the years.
3. Restore a backup to this new company. This will be your archive company and you can remove current information from it if you'd like.
4. In live company, remove history in all modules everywhere you can specify to only remove for a specified date range.

This isn't as clean, complete, or easy as those third party products probably but a lot of my clients do this.

Also, I've noticed that SP3 for SQL makes a HUGE difference on speed!

Good luck!

 

[allanp0220]

Thanks Ipgforms and stef315 for your replies.

I have contacted Professional Advantage for more details.

Not sure I'm brave enough create a new company by hand. I really don't know my way around Great Plains with any confidence.

In fact after I threw a wobbly we have asked our consultants to not only archive our data but also to upgrade us to v7.5! Could be the answer to all of our problems ............... or just the start of a new batch! However, I would still prefer it if we could do our own archiving so i look forward to the reply from Professional Advantage and their Company Data Archive programme.
Thanks
Allan

 

[lpgforms]

Allan


I was wondering why you had not upgraded past 5.5. I figured that you were probably not current on your enhancement charges. The upgrade should make a difference. Just make sure your consultants know to upgrade any custom code, and ensure that your server meets minimum specs for 7.5.


FYI - you may want to have your consultants hold off on the upgrade to 7.5 as 8.0 release is imminent, as in this month. I have a 50 page pdf with all the features new in 8.0 - if you want a copy, email me at brian@lpgforms.com.

Brian

 

[allanp0220]

Hi Ipgforms,
Thanks again for the advice.

I've mentioned the server suitability to our consultants although one of their guys used to be our IT Manager so he should be conversant with the system.

I think we never upgraded due to the cost and the fact that the system was working relatively well and the staff were familiar with it. The old adage "If it ain't broke don't fix it".

Yes, I had heard that version 8.0 was imminent. Our consultants didn't seem to want to push it just yet. Maybe they need to get up to speed on it and I tend to think there is some merit in using the previous version of software so that the bugs are known or sorted.
many thanks
Allan

 

[allanp0220]

Hi again,
My latest questions to our consultants as advised by lpgforms have just caused more anxiety!

When asked if our server was suitable for v7.5 the answer was "just about". Our consultant explained that most customers ran a separate SQL server machine dedicated to Great Plains. As far as I am aware this is what we have, I don't think it does much else. This means that we may well spend a small fortune on data reduction and GP upgrading and be no better off because the server still can't cope...
And our consultan couldn't give a definite answer. He felt it was reasonable since the data was growing that this was the most likely cause. We recently upgraded our raid array hard drives, the twin processors appear to be running fine and the memory appears to be working well.

Am I being naive but shouldn't I be able to expect an experienced consultant to check things like the Performance Monitor and be able to diagnose why our system is slowing? We did call in another consultant, some months ago, but he didn't check anything, not in a proper fashion anyway, and promptly suggested that he could build a new server. Surely this is just the easy, stock answer? and not really the solution at all?
I'm beginning to tear my hair out, the boss will not be pleased if we spend several thousand and get nowhere and I'm thinking of becoming a bricklayer or a plumber!.

Incidentally, Professional Advantage haven't replied to my enquiry regarding their Comapny Data Archive software, either.
Cheers
Allan

 

[lpgforms]

I was speaking to a salesperson today at Professional Advantage today for a client looking at their Webhouse product, and I asked about the archiving product. They no longer support 5.5, 6.0 is the earliest version supported. The cost is based on number of Great Plains licenses, so for example, a ten user system would be $1500 + 18% enhancement fee.

As for your consultants, well, some are more proactive than others when it comes to upgrades. My company has a full-time upgrade coordinator. Her only job is to manage upgrades, so she knows all the right questions to ask. She's going to be pretty busy over the next six months with 8.0 migrations.

If you want to speak to someone at my company for a second opinion, just let me know, and I can arrange it for you.

You should try getting a copy of MSGP recommended system configurations for servers. They are pretty modest, in my opinion, unless you are running manufacturing or field service with dozens of users.

Brian
brian@lpgforms.com

 

[jazgeek]

what's your network speed? I found that a few of my engineers were running streaming audio from the internet to their pc's and throwing around 50 meg cad files across the network, even with a 1000mbit backbone the 100mbit network couldn't handle the data transfer. Finally I segregated the engineers from the rest of the users and found the speed of GP improved.

it is a well known fact (at least I think it's well known) that even though GP uses sql for it's database, it makes very little use of the sql server to process any information. Thinks still happen on the local clients that require huge amounts of data to be brough across the network and crunched on a local machine. Just ask anyone who has to transfer orders to invoice or post inventory transactions.

its coming up to inventory time for me and I timed last years inventory quantity import of 3500 items and quantities into a single site.... 2 hours to post. This year I'll post on the sql server itself to see if it'll speed up.

I'm hoping that the changes that are strictly cosmetic in the v8 release are forecasting an end to dexterity. Hell, even a vb coded application would run better. I would still love to know how GP got the windows authorized recognition when you can't even right click on something.

I rant about this product too much, but when you've been working with it since v3.15 you've got a right to bash it a bit.

thanks for reading.



-----------
and they wonder why they call it Great Pains!

jaz

 

[allanp0220]

Hi,
Thanks for all of your replies.

We have asked our consultants to come in and do a data archive as soon as possible. However, they are busy and it could be a couple of weeks - we are not sure the system will stand up that long so we are thinking of trying our own data archive.

Following on from the reply from stef315, above, is there anything we really must be aware of, any pitfalls. We think we know how to create a new company and we think we know how to restore...............
Any advice welcome.
Thanks
Allan

 

[stef315]

BACKUP!! of course!

After you remove history, you'll have to shrink the files.

 

[mnla2z]

Been a while since 5.5 By the way, there is NO shrink files in SQL....
Do this:
1. Make sure you have enough disk space.
2. Use Dynamics Utilities to create a company on the server. You will need to use the SA user to do this.
3. Backup current company.
4. Do a force restore into the database just created from the live company. You will need to also in SQL Query Analyzer tool: sp_dropuser 'userid' (for all the users that are associated with the company.
5. Run Grant70.sql against this new historical company.
6. Go into Great Plains and grant access to the company for the appropriate users. Make sure you see your data!!!!
7. Look at the tables. You can go to tools-resource descriptions, table definitions. The historical tables usually begin with 30 for their number series. For example, the gl30000 is the gl historical detail table.
8. Use the sql query tool or access or whatever you want and create a query somehting like against your CURRENT Company (Not the historical):
Delete GL30000 where trxdate <='12/31/2003'
Or whatever date you like.
Repeat for PM series (Payables) RM (receivables), SOP (Sales Order Processing) IV (Inventory) UPR (Payroll)
Then when you are done removing records, run checklinks (File-Maintenance, Checklinks) against the whole system. It will remove the rest of the garbage records (keys etc...)
This should do the trick I've done this many times.
Anyway, regarding your speed, 5.5 had some bad stored procs that were dealt with on 6 and 7, as well as 7.5.
You might want to run a sql.trace or a Profiler aginast the database to see if there is a stored proc or something that is causing a problem.
You can reload stored proceedures from the file maintenance proceedure in Great Plains (SQL Option from File-Maintenance).
Just run Grant70.sql
Good luck!
Mike

 

[stef315]

There is a shrink file but it's the SQL file you are shrinking. You can do this in Enterprise Manager (or Query Analyzer for the ambitious). Right click on the database-All Tasks and you'll see shrink database.

If you remove large amounts of data from the database you'll want to do the shrink. However, your SQL Server could be set to either Auto Shrink or have it shrink during the database maintenance plan.

Most of my clients do not have auto shrink enabled because it's a lot of work for the database to shrink and then just have to auto grow more. They don't normally remove large amounts of data unless they do something like this and then we do the shrink at that time.

 

[mnla2z]

The shrink that I was referring to was inside Great Plains, and what we do is setup a database maintenance plan inside Enterprise Manager that will shrink the database on a weekly basis (say Sunday at 2am) when the databases are not being accessed.
Mike

 

[stef315]

No problem, just clarifying since you said no shrink in SQL. Anyway, some people don't configure the database maintenance to shrink every week. It just means the database will have to autogrow again which slows it down. The normal use of GP does not usually necessitate a shrink. So, it was just an FYI that they may need to shrink when they are done.

 

[allanp0220]

Hi,
Thanks, lots to go on there.

We are proceeding very slowly, step-by-step. So far we have done a backup and restore using The World Online database for practice. Then we plan to restore a backup of one of our small companies (we have two companies) into The World Online to see how that goes. If that is successful we'll set up a new company and have ago at archiving our big database. Course its a nuisance having to wait until after work to do anything.

Shrink is not set to operate on our databases, apparently our consultants told us not to use it although I can see that we will need to use it on the live company after the data archive.
many thanks
Allan

 

[stef315]

You can do the backup and restore to archive company parts while users are in the system.

You probably don't want to do the remove history while they are in though.