NAV Sustained Engineering Team Blog: Overview of updates for SQL Server 2005 SP2 - Dynamics CRM: Blogs

Blog bot · 11.01.2008, 17:49

This post has the following three purposes:

Give awareness that there are updates available for SQL Server 2005 SP2
Give an overview of what the available updates mean in relation to running Microsoft Dynamics NAV on SQL Server 2005
Help deciding whether a customer who runs Microsoft Dynamics NAV on SQL Server 2005 should install any of these updates (and which one)

Overview:

The base version of SQL Server 2005 SP2 is build 9.00.3042.00 (build 3042). Since this build, a number of updates have been released, some of them containing important corrections.

To find the build of SQL Server 2005, run this script from SQL Server management Studio:

SELECT @@Version

Q: Should I install a SQL Update?
Especially cumulative update 1 (build 3161) contains some important corrections (see details below). So if your SQL Server has a build number lower than 3161, then I would recommend that you update it when convenient - even if you have not been affected by any of the issues described here. If SQL Server is build 3161 or higher, then I would recommend to install the update as part of your normal update practise, unless you experience any of the symptoms described here.

Q: If I install a SQL update, which one should I install?

All the SQL updates are cumulative and contain all previous updates. So whenever you do install an update, you might as well install the latest one. The latest update for SQL 2005 (January 2008) is "Cumulative update package 5 for SQL Server 2005 Service Pack 2 (KB 943656)".

Plpease note: Any time you experience a problem, you should search support.microsoft.com / Partnersource, and/or raise a support request. This post only describes a small part of potential issues, and only issues that are directly related to NAV.

Further details:

The following section is a description of the problems we have seen on NAV installations, which have been corrected in SQL updates:

1) SQL Serv er slows down
KB 933564 / SQL Hotfix bug number 50000945:

FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005

This article mentions that "This problem can lead to an increase in memory consumption by the USERSTORE_TOKENPERM cache store.". It means that the "cache store" can end up consuming all available memory, which will slow down SQL Server. Restarting SQL Server will clear out the cache, and it will run fine again until the "cache store" fills up again. The typical symptom is that SQL Server is running OK for a while, but then for no apparent reason slows down. Users experience very slow response on both read and write operations. This problem has especially been experienced at systems with large amount of memory (12-16GB +).

The correction for this was included in cumulative update 1.

2) Another user has changed ... - errors from NAV

KB 930775 / SQL Hotfix bug number50000695:

FIX: Error message when you try to retrieve rows from a cursor that uses the OPTION (RECOMPILE) query hint in SQL Server 2005: "Could not complete cursor operation because the table schema changed after the cursor was declared"

When using the $ndo$dbconfig-table to add OPTION(RECOMPILE) to NAV queries (documented elsewhere), NAV would throw this error when browsing forms:

"Another user has changed the definition of the [xyz] table after the activity was started.

Start again."

This SQL correction corrected that problem. This correction is also included in cumulative update 1.

3) Some queries ran faster on SQL2000 than they do on SQL2005

KB 942659 / SQL Hotfix bug number50001716:

FIX: The query performance is slower when you run the query in SQL Server 2005 than when you run the query in SQL Server 2000

This problem was experienced by a very few NAV customers after they upgraded from SQL2000 to SQL2005. The symptoms were: When changing a field filter (F7) on "Name" in a contact list, the response would take a couple of seconds. The same actions would give instant response on SQL2000. This problem did not cause drastic table scans, "only" 3-5.000 more reads than SQL 2000 would have done. But of course, in a multi user system this would cause a lot of unnecessary reads.

The correction was to implement a new trace flag 4119. Enabling this trace flag will set SQL2005's behaviour to that of SQL2000 for these specific queries. This trace flag is further documented in this post:
New trace flag in Update 4 for SQL 2005 SP2

This correction is included in cumulative update 4.

Finally, refer to this list for an overview of some of the updates that are available for SQL2005:

The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released

Lars Lohndorf-Larsen

Escalation Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.