Adjust Cost Sequal Server 2000 to 2005

tro#1

All,

two weeks ago we moved a Navision database (Version 4.0, Executable 4.2) from Sequal Server 2000 to 2005.

Since then the Adjust Cost (Report 795) is unbelivable slow. Usually we run it every night and it took up to two hours before we moved the database to the new server.

Last week it did not finish during night. So I thought I run the report at the weekend and there will be sufficient time for the report to finish. But I had to abort it Monday morning (after 30 hours) because it was blocking users. I watched the value entry table every now and then. The report still generated new value entries. Even at the very end before I aborted the report.

You probably will recommend to update the database. But since the AC report was fast on the old server I just wonder if moving to the new Sequal Server 2005 can be the reason at all.

Thank you

bbrown

Was this on the same server or is the SQL 2005 on a new server. If it is a new server could there be hardware or config issues with the new server?

tro#1

Thank you very much for your quick response.

It is a new Server.

Right now we are investigating into two directions:
My IT coworkers check the configuration of the new server and I try to find out about Navision itself.

I would assume it is a config issue but what makes me wonder is that the Navision user do not complain about the performance during day. It just seems to be the Adjust Cost report. That is why I asked if anybody knows about the Adjust Cost report and Sequal 2005.

kriki

Presuming the hardware is working well and configured correctly.

1) did you do an index rebuild?

2) it is also possible that SQL2005 is doing tablescans to find certain records. This you can find with the profiler, looking for SELECTS that take a long time and read many records. With this, you can create the correct key including all fields in the where and the order by. But first try no. 1 and check the hardware+ it's setup.

kine

Yes, this difference can be because non-optimal indexes. SQL 2005 have different behavior than SQL 2000 in this...

tro#1

All,
thanks for your replies.
We have not solve it yet but could limit the possible reasons.

We already had an existing test database on the same Navision server. We created a fbk of live database (as of yesterday) and restored it in the existing test database. I ran the report 795 Adjust Cost and it took only two hours!!!

At least we know now that it is not the server itself.

Right now I restore the fbk again. We will try to run the report again with a different Navision user. We will also check if there are different setups for the real and test database.

I will keep you updated. If you have any hints what we should check please let me know.
Thank you

jlandeen

I don't know if your backup/restore test completely proves that the server is not the problem.

Do both database have exactly the same configuration? Specifically are the disks involved with both of the SQL instances and the SQL files associated with the 2005 version of the database match those of the SQL 2000 database? I've seen a lot of administrators split their data and/or files across multiple drives to optimize performance. Have you checked that the disk and file configurations of the 2 databases are identical?

garak

the SQL 2000 and 2005 works different if the indexes are not optimal.
And the default navision indexes aren't optimal.
So check for example with SQL Profiler which sql statements are to slow (many reads for example > 1000, or high duration) when you are rund the report. For example the key "Item No.,Valuation Date,Location Code,Variant Code" is used in the "Adjust Cost" So if you not have Variants or and your setup is on Item and not Item /Variant / Location you can change the key in property SQLIndex.

If you see the queries in SQL Profile you can analyze the query and the used index. For this copy the query to query clipboard in
SQL Manager -> Query -> Include Actual Exec. Plan
SQL Manager -> Query -> Query Options -> Advanced -> Set Statistic Time Set Statistics IO

Than run the query. Now you will see, which index the server use for the query.

With the statement sp_helpindex [CRONUS International Ltd_$Cust_ Ledger Entry] and Select * from sys.dm_db_index_usage_stats, dbcc show_statistics ([CRONUS International Ltd_$Cust_ Ledger Entry],[$3]) you can check the index usage and statistics.

Also possible to check is using Add-On Client Monitor and Excel Pivot Tables.

Regards

tro#1

I am not a Sequal Server export but this is what my coworker told me.

After he installed the database on the new Sequal Server 2005 he started "maintenance jobs". I guess those jobs include to rebuild the indexes, etc. Obviously those maintenance jobs aborted.

He started them again yesterday night. They had finished successfully. Afterwards I ran report 795 Adjust Cost again. It was done after 1,5 hours. Amazing...

Thanks all for your help.

jlandeen

it's good to hear that you did get sql 2005 working quite well. It's further proof of just how important those maintenance tasks to keep indicies, statistics and log files in check.

kriki

jlandeen wrote:

it's good to hear that you did get sql 2005 working quite well. It's further proof of just how important those maintenance tasks to keep indicies, statistics and log files in check.

Just quoting someone : "Maintenance jobs for SQL are as important as maintenance jobs for your car"!