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

Question

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

Does that query also manipulate the XML column by any chance?? — marc_s, Feb 23 '11 at 12:26
No. Please see additional example query in question for more info. — redcalx, Feb 23 '11 at 14:49
@finnw - For some reason the auto generated comment didn't come up so I had to create one manually. Must have messed it up then! It is the corresponding question to that answer though. — Martin Smith, May 22 '11 at 18:13

ErikE · Answer 1 · 2011-05-23T16:42:26.840

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.

Really?! Not in SQL Server. I'm willing to be proved wrong, though. Please do! — ErikE, May 22 '11 at 19:02
Just add the `persisted` keyword in the column definition. It gets saved in the data page. You can also index these but it not a requirement to persist them to be indexed except in some circumstances (CLR datatypes, imprecise/floating point expressions) — Martin Smith, May 22 '11 at 19:04

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

1 Answers1