Performance issue regarding report 795

gdi163

Hello everybody,

maybe somebody can give me a hint to resolve the following situation:
On one of the databases of a customer the runtime of the "Adjust Cost - Item Entries" - report exploded from the usual 30 minutes to 14 hours from one day to another. I had no better idea than performing table optimizing on table 32 (the call from the client: File - database - information - tables...). To my surprise it worked! Runtime went back to 30 minutes but only for 2 or 3 days. Then it exploded again. Another optimization and we have smooth performance for the next few days. Naturally the customer demands a permanent solution.
It is a SQL database (2008) an we use client 5.0 SP1.
What is happening there and is it possible to run the table optimization automatically? Any hint is appreciated.

regards


Guido

kriki

[Topic moved from 'Navision Financials' forum to 'NAV/Navision Classic Client' forum]

Did you implement a maintenance plan to rebuild the indexes?
I hope you maintenance plan does NOT contain a shrink database and your database property "Auto Shrink" is FALSE.

If you rebuild the indexes in your DB and then do a Shrink DB, your rebuilding of the indexes does serve at anything. And shrinking your DB creates a lot of fragmentation.

gdi163

Thank you for your reply.
There is a maintenance plan running but it does not contain a shrink database. "Autoshrink" ist false, too.
Other databases of that customer have the identical maintenance plan running but without any problems.

Andwian

Maybe you can try to backup, create a new DB, and then restore the DB. Then use this new DB as your new production environment.

DenSter

Andwian wrote:

Maybe you can try to backup, create a new DB, and then restore the DB. Then use this new DB as your new production environment.

What is that going to accomplish? I don't think that will make any difference whatsoever.

I suspect that the answer lies in setting up proper database maintenance, starting with reindexing and statistics maintenance. Then I would also take a good look at the infrastructure to make sure that this is still adequate. A lot of times, performance problems start appearing when the company grows, and more users are added. The server that used to run fine all of a sudden has to do more than it is comfortable with.

Just to be clear: DO NOT SHRINK YOUR DATA FILES!!!

Andwian

DenSter wrote:

What is that going to accomplish? I don't think that will make any difference whatsoever.

That is because:

gdi163 wrote:

Other databases of that customer have the identical maintenance plan running but without any problems.

From that perspective I conclude to create a new DB . Please do apologize if my conclusion is wrong. :oops:

kriki

Andwian wrote:

DenSter wrote:

What is that going to accomplish? I don't think that will make any difference whatsoever.

That is because:

gdi163 wrote:

Other databases of that customer have the identical maintenance plan running but without any problems.

From that perspective I conclude to create a new DB . Please do apologize if my conclusion is wrong. :oops:

If you are referring to a SQL backup, it won't change a thing, because a SQL-backup is almost a bit-by-bit copy of the database.
If you are referring to a native backup, this is a long process and probably it will create even more problems because when restoring the backup all data is inserted again creating even more fragmentation and bad statistics.

Andwian

kriki wrote:

If you are referring to a SQL backup, it won't change a thing, because a SQL-backup is almost a bit-by-bit copy of the database.
If you are referring to a native backup, this is a long process and probably it will create even more problems because when restoring the backup all data is inserted again creating even more fragmentation and bad statistics.

Thank you for sharing this

davmac1

It sounds like your problem belongs in the SQL Performance area.
You could look at how your database has been setup for file expansion (and whether that is taking place), and whether your database and disk drives have fragmention issues.
Are you regularly updating statistics and tuning your database?
How large is it?