We are running NAV 2009 SP1. I am relatively new to NAV but have a reasonable knowledge of SQL. Basically I have dropped and rebuilt the indexes to reduce fragmentation. But now when a user tries to run the Analysis By Dimensions it is taking 30 seconds to load. You can see it drawing the grid and populating the data very slowly.
Does anyone have any suggestions why this would happen?
Apparently this has been running very slow for sometime now. The rebuild of the indexes has not improved it. You can see it takes an age to even draw the screen and the CPU is being hammered whilst this process is running by the SQL server exe.
All data --even filtering!-- is processed by NAV client, so SQL server can’t be blamed here, all it got is SELECT * FROM…
However, some finetuning can be done on SQL side, browse SQLforum’s previous threads for more info and check Jorg’s blog:
NAV/SQL Performance - My Two Cents
Thanks for the reply, sorry but I am new to this.
Can you explain how the analysis by dimensions works? Does it collate data into temp tables and if so I may need to alter fill facter to a lower value?
I think you have posted to the wrong article???
@Roger - do not pay attention, that was spam post you refer to, I deleted it. Moderator
Did you find a solution to your problem?
I’m experiencing the same after a technical upgrade to 2009 R2 from 4.3.
Sorry no but I do remember reading something about index maintenance on the tables; such things like defraging etc.
Optimize the tables,
Disable Unwanted Indexes
Def rag the tables
This will surely improve the performance.