Hi all Experts,
I’m having a (to mee) strange issue at a client.
We experience a really bad performance in 1 particullar index in table 17.
The index in question is “Document No.,Posting Date”
We have 3 DB’s, 1 for Development (DEV), 1 for testing (TEST) and 1 for production (PROD).
This issue is in all 3 DB’s.
DB version is 4.00 SP3
Client is 2009 R2 with latest hotfix (35179)
PROD and TEST are on SQL2008R2 SP2 no further updates (10.50.4000)
DEV is on SQL2005 SP4 with KB2494120 security update (9.0.5057)
PROD table holds 36 mio. entries
TEST table holds 32 mio. entries
DEV table holds 27 mio. entries
We experience that when ever we want to sort data by this index, the system hangs for an unreasonable time.
Sorting by any other index gives a reasonable response time.
This is also if i Query in sql level, with an “order by” clause.
I’ve tried optimizing the table from within NAV.
I’ve tried rebuilding the index (right click in management studio, and selecting “Rebuild”)
But nothing seems to better the situation.
I’ve discovered that at some point, some user have used Danish special chars in Document No., and i suspect that this could be a part of the issue.
Therefore i’m right now looping through entries, and removing those special chars (only in a test DB…). I hope this will help on performance.
Funny thing though is that those entries are more than 2 years old, and the bad performance just started mid december.
Does someone have any input as to what i could look into?
I’m totally lost here…