New trace flag in Update 4 for SQL 2005 SP2

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:;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:

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".

- Lohndorf

Comments (1)

  1. Lohndorf says:

    In a recent case, trace flag 4119 proved to cause performance problems rather than solving them. The problem was a query like this:

    SELECT  * FROM "CRONUS International Lts.$G_L Entry" WITH (READUNCOMMITTED)  WHERE (("Entry No_">=@P1 AND "Entry No_"<=@P2)) AND  "Entry No_">=@P3 ORDER BY "Entry No_"

    Caused by C/AL code like this:

    GLEntry.SETRANGE("Entry No.","From Entry No.","To Entry No.");

    FORM.RUN(FORM::"General Ledger Entries",GLEntry);

    It showed to be causing clustered index scans, hurting performance. Removing trace flag 4119 solved the problem here.

    Trace flag 4119 was for a very specific case, not to improve performance in general. So I would not recommend turning on this trace flag unless you specifically have the issues described above, and certainly not just because it's there!

    Thank you to Martin @ Kumavision for sharing this information!

Skip to main content