Upgrade Nav 4.0 to SP3 release 5 on sql 2005

colin_leversuch-roberts

I'd be really grateful for any confirmation from anyone that they're successfully upgraded to SP3.
If you could indicate somehow the size of your database too that would be good, mine is a terabyte, so perhaps an indication of under or over 500gb.
Any problems found and times would also be useful
thanks.

Scott_Frappier

Colin:

We have a 500 user site (750 GB to 1 TB) that is currently undergoing the transition. We have delayed the upgrade due to issues that we have faced with the FAST option being removed from the application (Update 05), and are hoping that 4.0 SP3 Update 06 will help with performance as it is adding the TOP operator to the queries.

Send me an email if you have questions and I'll keep you on my "flagged" list to give more information after we have concluded testing.

Thanks!

- Scott

ara3n

Scott.

On what version are you on? sql? native?

What kind of hardware are you running?

What kind of performance improvements have to you done to navision?

And what areas of navision do the users use?

Thanks.

Scott_Frappier

ar3an:

1.) SQL Server 2000. Upgrading to SQL Server 2005 by the end of the year with concurrent upgrades to Dynamics NAV 4.0 SP3 Update 0X.

2.) I'd have to get the specs again, but when we went live (4 years ago) we were on a quad processor with 16 GB of RAM. Since then, we have upgraded to 32 GB and are running a RAID 1+0 w/ 15k drives on a fibre channel connection. We are _not_ using a SAN but a direct connection that is featured with some of the HP servers. It has a capacity of 12 to 16 disks and we have separated the log file from the data partition.

3.) With NAV, we have optimized indexes, created SIFT re-creation scripts along with re-indexing scripts that run every night. We have introduced application server concepts (such as nTier Architecture Services) to increase the scalability of the application. All of these have resulted in a system that performs very well under high transaction workloads. They also have an on-staff C/AL programmer that is amazingly good at SQL Server and NAV.

4.) All areas of NAV except for Service Management and Advanced Warehouse Management is used. MRP/Manufacturing/Inventory/GL/AP/AR/Cash are all used heavily (this company is a medical device manufacturer). E-Ship has also been implemented, along with a customer configurator.

This was my first implementation within our company (I started as a network technician) and it put me into the deep end of Dynamics NAV. Along with a great team, we got them up and running and the site has been perfect since!

- Scott

Avidmedical

We have been using NAV 4.0 SP3 for about six months and upgraded to SQL 2005 about a month ago. Since then we have been encountering severe performance issues. Has anybody been able to resolve similar problems? The client locks up when drilling down for information quite frequently, however there are no SQL indications of problems.

colin_leversuch-roberts

Q. can I ask which release level of sp3 you are currently on and does it resolve the FAST option problems?
are you on sp2 of sql server?
finally do you have a >500gb database?

A. we've established a number of performance issues some of which are hopefully resolved with release 5 of sp3, mainly these relate to the FAST option in queries. We currently have around 500 plan guides which force recompilation on these and other "worst" queries. We've been working with microsoft on the plan guides.
We're currently on sp2 but with a fix for the FAST option and testing to go to sp3 release 5 with a fix which avoids rebuilding the SIFT tables.

kriki

Avidmedical wrote:

We have been using NAV 4.0 SP3 for about six months and upgraded to SQL 2005 about a month ago. Since then we have been encountering severe performance issues. Has anybody been able to resolve similar problems? The client locks up when drilling down for information quite frequently, however there are no SQL indications of problems.

I just did an upgrade to SQL2005 and in some places I had this problem.
The filters were on field A,B,C but order had to happen on X,Y,Z (=SETCURRENTKEY).
SQL2000 would search for an index with A,B or C in it and not X,Y or Z.
SQL2005 uses an index with X,Y or Z in it or even using a clustered index scan! :bug:
After some testing, I resolved it by changing the ordering so it became close to A,B,C and not X,Y,Z. This solved the problem each time.

The DB was 150GB and after my work on it (index and SIFT maintenance), it went down to less than 80GB.

colin_leversuch-roberts

sql 2005 isn't very keen on having order by columns that don't occur in the select list.
Index selection by the optimiser is unlikely to be affected by the order by clause, if at all, as logically the order by does not affect data selection.

kriki

colin leversuch-roberts wrote:

Index selection by the optimiser is unlikely to be affected by the order by clause, if at all, as logically the order by does not affect data selection.

But I have had problems with it. First I created a new index that was very close the fields in the filter but still SQL2005 didn't select it. It kept selecting the one that was close to the fields in the SETCURRENTKEY.
Then I studied the SETCURRENTKEY used and found it was not needed to have it sorted by those fields, so I just changed it to the new key I created and all worked fine. This I did for several forms based on different tables.
When I have some time, I want to write a detailed topic about one of the cases.

dverghese

Scott,

We are on SQL Server 2000, with about 75 concurrent users, 8G of RAM, and the database is on NETAPP (SAN). We are constantly running into locking issues. We used to be on a RAID 5 configuration and since performance was degrading we moved to the SAN (which has couple of other apps like Livelink on it as well).

We are aware that Navision does table level locks when you post. So users are constantly locking one another out. How are you working around this fundamental/architecture issue.

Even though there are couple of apps on the SAN, our network folk say that the controller utilization is at less than 10%. Our network pipes and port traffic also show under utilization. We have had a performance expert tune the keys for about 30 objects and it didn't help. We have the SQL Perform utility which we the expert had used to schedule SQL maintenance routines. Nothing seems to help and we are out of ideas.

Any feedback would be much appreciated.

Thanks,
Dennis Verghese
Senior Program Manager
Basic Chemical Solutions.

Scott Frappier wrote:

Colin:

We have a 500 user site (750 GB to 1 TB) that is currently undergoing the transition. We have delayed the upgrade due to issues that we have faced with the FAST option being removed from the application (Update 05), and are hoping that 4.0 SP3 Update 06 will help with performance as it is adding the TOP operator to the queries.

Send me an email if you have questions and I'll keep you on my "flagged" list to give more information after we have concluded testing.

Thanks!

- Scott

colin_leversuch-roberts

it almost seems there are parallel threads within this thread!!
It takes a book to describe how indexes work and then there's still going to be more.
select * from table where c1=value and c2=value
With select * queries there's always a chance the optimiser will choose the clustered index or a table scan, however, for the example above an index could be defined on c1 and c2 ( or c2 and c1 depending upon selectivity of the first column in the index ) The optimiser will use this to bookmark back to the clustered index to return the data set.
select * from table where c1=value and c2=value order by c3,c4
with a select * adding the order by columns to the index may be useful, so index on c1,c2,c3,c4 - you will want columns c1 and c2 as the first columns in the index otherwise the optimiser will probably ignore the index. It's a bit of a black art and despite your best efforts the optimiser may choose another index