Nav hangs on searches

Business Central/NAV Technical Forum
1

Hello,

We recently upgraded our Nav DB from 2005 to 2008 on a faster server with more RAM. Since moving across we’ve noticed that some of the searches in Nav result in the session hanging - it starts responding again by itself after a few minutes.

I was wondering if anyone had any suggestions on what to check or if this might be a known issue?

I suspect that since the upgrade Nav is download more data faster from the DB server and the client is struggling to handle it all?

Thanks.

2

Most likely upgrade is not the reason, if only you have lost some indexes during upgrade…

Although you can search/filter ListForms on unindexed fields, this will be terribly slow (just as you described - coming back live after minutes), especially, if great amount of records must be processed. Maybe there your problem lies?
Or you search/filter on FlowField(s)? Then no index will help you, as FF must be processed, it takes time.

3

Matthew, I agree with Modris that you should reaffirm that all expected indexes exist and none have been marked as Disabled.

In addition, it might be worthwhile to rebuild all indexes and then update statistics. Comments based on this:

  • A product upgrade may induce a high amount of fragmentation in the indexes. It depends on what occurred “under the covers”. I’ve seen this before even in non-Nav products such as Siebel where they do a lot of UPDATE statements on mass volumes of data, add or change columns, change data types, etc. It all adds up.
  • You’d likely need to take a system outage to do a full set of maintenance. May not be that bad if your database isn’t that large.
  • I would do a REBUILD (and not a REORG) for the indexes. A REORG is shuffling records inside the index, whereas a REBUILD is effectively dropping and recreating the index.
  • Doing an UPDATE STATISTICS after the REBUILD has a bit of duplicity in that the REBUILD just collected statistics for the indexed columns, but I would do the UPDATE STATISTICS with a FULLSCAN and for ALL columns/indexes such that you collect a healthy set of data that can be later used by the cost-based optimizer.
  • You can easily accomplish these two tasks using a Maintenance Plan.
4

One more question. Did you verify your settings for the following on the new SQL Server instance?:

  • Max Memory
  • Max Degree of Parallelism (MAXDOP)

Are they appropriate?

5

First of all, degragded performance of NAV/SQL could have a gazillion reasons - thus gazillions of potential solutions might exist!
I highly recommend to search the forum about “SQL Performance” and you’ll get plenty of advices according to this permanently discussed issue.

I agree, sufficient maintenance is crucial to have the system running propertly, but this (quoted) statement is not correct:
In any case the ALTE INDEX command is only defragmenting the index pages while the index itself is still existing and maintained! The basic difference of both methods is - briefly -: REORGANIZE only defragements the “leaf nodes” of and index, while REBUILD is defragementing the whole index (B-Tree plus leaf nodes). Please refer to the “Books Online” about more details.
REBUILD is definitely NOT dropping an index - and this is a big difference to the CREATE INDEX WITH DROP EXISTISTING statement!
Anyway …

If NAV “hangs” this means it waits for something. This is either due to I/O issues or blocks. Please check out these articles to learn how to detect these problems:
http://dynamicsuser.net/blogs/stryk/archive/2010/12/02/block-amp-deadlock-detection-updated-scripts.aspx
http://dynamicsuser.net/blogs/stryk/archive/2010/05/20/directions-emea-2010-nav-sql-performance-indexes.aspx

Cheers,
Jörg