Welcome to MSDN Blogs Sign in | Join | Help
Performance tip for Office Accounting on SQL Server

Previously I did a post with a few general performance tips for Office Accounting.

 

I just saw a question on the newsgroup about performance on a SQL Server; basically the application was running very slow even though the server machine was a very powerful machine with lots of RAM. (In this case it would take minutes to save a transaction from Office Accounting)

 

The problem was/ is that SQL Server will release memory if it senses that other processes use a lot of memory. In fact SQL Server will release so much memory that it will slow down to a crawl as it will have to go to disc all the time.

 

The solution is to increase the minimum memory usage for SQL by setting the “min server memory” option…

 

For more info take a look at this article on Server Memory Options or Effects of min and max server memory from SQL Server 2005 Books Online 

Posted: Wednesday, March 28, 2007 9:15 PM by Jorn

Comments

Ellonreis said:

I'm a small business owner in Brazil(5 employees)and I used SBA 2006 for two years. About a month ago, we upgraded to Office Accounting 2008. It's has been my worst nightmare since we made the change. It's not just TOO SLOW, it's actually unusable because it crawls to a halt on every machine (multiuser 5 computers on workgroup). I'm desperate and don't know what to do. It has become a paralizing bottleneck in my office and now I can´t even go back to SBA 2006 because our database has been upgraded and SBA 2006 wont open it anymore. I found this article very interesting as it gave me hope that improving performance is posiible. Can someone please help me configure the memory usage for SQL on my machine. I have Windows Vista ultimate installed on the computer server and even though I read the related articles I cannot find where to go to make the changes. PLease Help Anyone!

# October 16, 2008 11:28 AM

Ellonreis said:

My computer has 4GB RAm with an Intel Core 2 Quad processor (Q6600).

# October 16, 2008 11:30 AM

Jorn said:

First off, the performance of 2008 should NOT be worse than the 2006 version, so let’s hope we can get things running for you.

A few questions:

Have you tried the tips in this post and the general performance tips post?

Are you running with the default SQL Express database or have you installed you own server?

Is the database installed on a dedicated machine?

Is the product just as slow if you run it on the same machine as the database is running on?

# October 16, 2008 12:20 PM

Ellonreis said:

Thank you ver much for the timely response Jorn. I will do my best to answer each of your questions on the paragraphs below.

I tried the tips on the general performance tips post. I tried to repair the database using the data utility but I get a message stating that it could not complete because of a database timeout error.

I'm running the default SQL Express database.

My database is not installed on a dedicated machine. I use the server machine as my workstation. I'm very careful about not using up too many resources at the same time.

The product is just as slow on my machine (where the database is)as in other computers. It runs VERY slow when there is only one user using the application and it actually stops working when there are two users using the application. It forces us to restart the application, but doing so doesnt solve the problems just starts the cycle again.

Let me know if you need any more details.

Thanks a lot!!!

Ellon.

# October 16, 2008 1:03 PM

Ellonreis said:

Hey Jorn

I'd like to thank you for your timely response and for having this useful blog. Since my first entry I decided to perform a back up of my data and simply do a clean reinstall of the program. I have no idea what was wrong with it but the reinstall seems to have solved the problem. I was thinking Microsoft had released a JUNK application. But my team and I are back up and working again .

Thanks a lot for the help!

# October 16, 2008 3:46 PM

Jorn said:

glad to hear it!

# October 16, 2008 3:50 PM

fortimaxusa said:

Does it make difference which driver MOA uses to work with SQL Server? When running MOA 2009 Pro x86 in a domain connecting to SQL Server 2008 x64? I have 3 drivers: SQL Native Client 2005.90.3042.00, SQL Server 6.00.6001.18000, and SQL Server Native Client 10.0 2007.100.1600.22. How do we control the driver usage by MOA?

# March 1, 2009 3:19 AM

brianclements said:

My database file has gotten ridiculously large lately, doubling in size in just a few months to 2.5GB. Additionally, application performance has degraded severely. Opening the Invoices list can take up to 3 minutes.

I have discovered over one million records in DocumentsTable where IsValid=0. So, I ran sproc_DeleteInvalidDocuments to delete those records as well as related records in other tables. I then re-built my indexes and shrunk the database. The result was a DB size reduced from 2.5GB to 740MB and being able to open invoices list in only 20 seconds.

So, now I'm wondering how so many invalid records exist in DocumentTable and related tables. I'd like to see if I can fix the problem and not just treat the symptom. Any ideas why this might happen?

# March 13, 2009 6:31 PM

Jorn said:

Hi Brian

I can't tell what version you are running based on your comment. I do know that the team fixed bugs related to this in the Office Accounting 2009 SP1 release. If that is not the version you are running my guess is that you could upgrade to it for free.

Hope that helps you - Jorn

# March 16, 2009 12:48 PM
Anonymous comments are disabled
Page view tracker