Performance Issues after DB upgrade

[bubarooni1]

Hi,

I am running GP 8 SP 5 sitting on a box with a nice dual core processor, 4 Gb's of memory and WinXP Pro.

I am scheduled to upgrade to GP 10 next month. As part of that upgrade process (and to escape the 2 GB MSDE limit) I recently performed a side-by-side upgrade of the existing MSDE database to SQL Server 2005 Express. I wasn't able to do an in-place upgrade of the database due to a 'installation path to long error'. Anyway, since the upgrade, my users have complained bitterly about the speed, or rather the lack thereof.

I wasn't being careful when I installed Express and I actually installed the original Express instead of Express SP 2. I am going to do that upgrade over the weekend while no one is on the machine. I am doing that in the hopes that it will contain fixes that will help speed issues AND because I should be able to mimick Maintenance Plans using techiniques I've found elsewhere on the web.

Is my horrible performance due to the lack of maintenance plans? Is it the processor/memory limitations imposed on Express (vs MSDE)? Have I neglected to do something in Express to the databases after I attached them? Did I not do something in Great Plains that I should have done?

If someone else has been down this road before and can share how they addressed it, I'd sure appreciate some pointers!

Insurrection is brewing in the Accounting/HR Depts.!

 

[JohnTcolo]

Apply the service pack for SQL Express. Check your event logs, make sure nothing odd happened the upgrade.

I would also create a complete maint plan and do all the index rebuilds, repairs, etc.

Good luck!

 

[bubarooni1]

i think i'm gonna need it. there are no maintenance plans in the Express version of sql 2005!

 

[bubarooni1]

well, upgraded to sp2 yesterday while everyone was out at the annual company pool party.

being the computer guy really blows sometimes...

to duplicate the Maintenance Plans MSDE had, I'm using the ExpressMaint utility found on this webpage:

http://www.codeplex.com/ExpressMaint

I've used it to backup, reindex and update the stats on all the user dbs.

i'll just have to cross my fingers and see if they think it acts better tomorrow.

 

[JohnTcolo]

Narrow down the specific modules that your users are having performance issues with, you may want to run checklinks for these modules if you have not done so.

 

[bubarooni1]

Well, no real improvement based on user feedback.

I applied the service pack, rebuilt all indexes, updated all stats and ran checklinks on everthing.

Is it possible that SQL Server 2005 Express is just plain old slow compared to MSDE?

 

[VictoriaYudin]

This is not my area of expertise (I am more knowledgeable on the GP application side) but unless there is a resource issue, I would typically expect SQL Server 2005 to be faster than MSDE.

Is EVERYTHING now slower in GP? Or only certain things? Do you have anything in the Windows event logs pointing to delays or time outs?

We had a similar thing happen once at a client when we installed a new server, but they didn't want to get a new switch. At the same time as getting the new server we upgraded them to the full version of SQL 2005 (from MSDE). After the upgrade some things in GP started taking a very long time. Not always, but more often than not. At times just opening transaction entry windows in GP would take almost a minute. When we tested the same thing directly on the server it was incredibly fast. Long story short, it turned out the new server and the old switch were not communicating properly so that the connection speed was not being auto-detected properly. Forcing the speed worked, but only for a period of time. Changing the switch to a new one took care of the issue.

Not saying this is what's happening in your case, but you may need to do some troubleshooting to narrow down exactly where the problem is.

A few things to try:
- Run GP directly on the server - is it still slow?
- Do something else on that server to see if this is strictly a GP issue - maybe copy a large file (something like 100 MB) from the SQL server to one of the GP users desktops, then copy the same file back....how long does it take?

Victoria Yudin
Dynamics GP MVP 2005 - 2008
Flexible Solutions - home of GP Reports

http://www.flex-solutions.com/gpreports.html

 

[bubarooni1]

Yeah, everything in GP was terribly slower.

However, I was rummaging through the internet trying to find a similar problem with an easy fix and came across this:

In MSDE the Auto-Close property of databases in False. In SQL Server 2005 Express that property is set to True. This value when set to true closes the database after 300 milliseconds of inactivity.

I checked the property in the database ( the msde instance i had upgraded to 2005 express) and it was now set to True. I changed that value to False and the performance instantly tripled.

I'm checking now whether or not that change is posing some other type of risk, but in the meantime the girls in Accounting and HR are lovin' on me big time so I'm gonna let it roll until I find a compelling reason to undo the 'fix'.

 

[VictoriaYudin]

LOL! Excellent.

I found this on auto close in an old GP KB article: "DO NOT check this option. This closes the database data file and log file every time the last users closes its connection. Could cause performance issues."

Here is the link to the entire article in case you want to take a look:

https://mbs.microsoft.com/knowledge...UUXNNZOUKYPPPZWLKRKLLNMYURTVLLPXKQUXWUVLVXYZZ

There are probably a number of other SQL settings that are very important.

Two other things you might want to look into:
- Depending on how you upgraded to SQL Express, your databases might still be set to compatibility level 80, which may be slower than compatibility level 90.
- Did you create new ODBC data source connections using SQL Native Client for all the users?

Not sure if either of these will make any difference in performance, but may be worth checking.

Victoria Yudin
Dynamics GP MVP 2005 - 2008
Flexible Solutions - home of GP Reports

http://www.flex-solutions.com/gpreports.html