The current release cycle for SQL 2005 SP2, is to release a new update every 2-3 months. Currently (November 19th 2007), the latest update for SQL 2005 SP2, is update 4, which is available here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;941450

The updates for SQL 2005 SP2 are all accumulative, so you will not have to install Updates 1 - 3.


Each SQL update may contain small changes to the way that SQL Server behaves, including changes to the query optimizer. So when having any kind of SQL related problem (performance problems or otherwise), I would recommend to apply the latest update to begin with.

This update (Update 4) also contains a new trace flag which was made specifically for Navision installations. I have only seen the issue that it addresses very few times, so I would only recommend activating this trace flag if you see any of the symptoms. But this is how this new trace flag works, and what the symptoms are:


Symptoms:
Changing filters in a list form, which used to be quick on SQL 2000, is slower after moving to SQL 2005, even when filters and indexes are a perfect match. The symptoms typically cause a few 1.000s of extra reads. They are unlikely to cause complete table scans. So the problem we have here, is more of the system slowing down a bit, than the system hanging completely.

The queries causing the problem will look like this:
SELECT  Name FROM "CRONUS International Ltd_$Contact"  WHERE (("Name" LIKE @P1)) AND  "Name">@P2 ORDER BY "Name"','Steve%','Andy Anderson'

Note that the two filters (predicates) LIKE and > are on the same field (Name). 

Index hints or RECOMPILE will not make any difference to this query.

Navision can generate a query like this if you have a list of contacts sorted by "Name". Then while the cursor is on 'Andy Anderson', apply a field filter (F7), and filter on 'Steve*'.


So we have these two predicates:
  1)  LIKE 'Steve%'
  2)  > 'Andy Anderson'

SQL 2000 would evaluate each of them, and use the most selective predicate first, in this case the first one (LIKE). So SQL 2000 would first select contacts where name LIKE 'Steve%', and then from these contacts find the ones where name > 'Andy Anderson'.

SQL 2005 behaves differently when queries are parameterized (as they are). It will assume that a LIKE-predicate is always less selective than a > - predicate (which is often the case). So on this query, SQL 2005 will always run the second (>) - predicate first. So it would first select contacts where name > 'Andy Anderson', which would be most of the contact table.

 

Update 4 for SQL 2005 SP2 contains a new trace flag 4119, which will change SQL Servers behavior in this specific situation back to SQL 2000 behavior.

Before you set this trace flag, I would recommend that you collect some traces, and analyze whether you do see queries like the one mentioned above. You can read more about identifying problem-queries in the blog "Diagnose your SQL Server".

 

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