SQL: Persisted computed field in table sometimes not used during query execution [duplicate]

This question already has an answer here: 开发者_开发技巧 Closed 11 years ago.

Possible Duplicate:

Why does the Execution Plan include a user-defined function call for a computed column that is persisted?

In SQL Server 2008 I'm running the SQL profiler on a long running query and can see that a persisted computed column is being repeatedly recalculated. I've noticed this before and anecdotally I'd say that this seems to occur on more complex queries and/or tables with at least a few thousand rows.

This recalculation is definitely the cause of the long execution as it speeds up dramatically if I comment out that one column from the returned results (The field is computed by running an XPath against an Xml field).

EDIT: Offending SQL has the following structure:

DECLARE @OrderBy nvarchar(50);

SELECT
    A.[Id],     
    CASE
          WHEN @OrderBy = 'Col1' THEN A.[ComputedCol1]
          WHEN @OrderBy = 'Col2' THEN C.[ComputedCol2]
          ELSE C.[ComputedCol3]
    END AS [Order]
FROM
    [Stuff] AS A
INNER JOIN
    [StuffCode] AS SC
ON
    A.[Code] = SC.[Code]

All columns are nvarchar(50) except for ComputedCol3 which is nvarchar(250).

---

The query optimizer always tries to pick the cheapest plan, but it may not make the right choice. By persisting a column you are putting it in the main table (in the clustered index or the heap) but in order to pull out these values, normal data access paths are still required.

This means that the engine may choose other indexes instead of the main table to satisfy the query, and it could choose to recalculate the computed column if it thinks doing so combined with its chosen I/O access pattern will cost less. In general, a fair amount of CPU is cheaper than a little I/O, but no internal analysis of the cost of the expression is done, so if your column calls an expensive UDF it may make the wrong decision.

Putting an index on the column could make a difference. Note that you don't have to make the column persisted to put an index on it. If after making an index, the engine is still making mistakes, check to see if you have proper statistics being collected and frequently updated on all the indexes on the table.

It would help us help you if you posted the structure of your table (just the important columns) and the definitions of any indexes, along with some ideas of what the execution plan looks like when things go badly.

One thing to consider is that it may actually be better to recompute the column in some cases, so make sure that it's really correct to force the engine to go get it before doing so.