Properly Persisted Computed Columns

Persistent

Introduction

Pretty much every computed column related performance problem I have encountered over the years has had one (or more) of the following root causes:

  • Implementation limitations
  • Lack of cost model support in the query optimizer
  • Computed column definition expansion before optimization starts

An example of an implementation limitation is not being able to create a filtered index on a computed column (even when persisted). There is not much we can do about this problem category; we have to use workarounds while we wait for product improvements to arrive.

The lack of optimizer cost model support means SQL Server assigns a small fixed cost to scalar computations, regardless of complexity or implementation. As a consequence, the server often decides to recompute a stored computed column value instead of reading the persisted or indexed value directly. This is particularly painful when the computed expression is expensive, for example when it involves calling a scalar user-defined function.

The problems around definition expansion are a bit more involved, and have wide-ranging effects.

The problems of computed column expansion

SQL Server normally expands computed columns into their underlying definitions during the binding phase of query normalization. This is a very early phase in the query compilation process, well before any plan selection decisions are made (including trivial plan).

In theory, performing early expansion might enable optimizations that would otherwise be missed. For example, the optimizer might be able to apply simplifications given other information in the query and metadata (e.g. constraints). This is the same sort of reasoning that leads to view definitions being expanded (unless a NOEXPAND hint is used).

Later in the compilation process (but still before even a trivial plan has been considered), the optimizer looks to match back expressions to persisted or indexed computed columns. The problem is that optimizer activities in the meantime may have changed the expanded expressions such that matching back is no longer possible.

When this occurs, the final execution plan looks as if the optimizer has missed an “obvious” opportunity to use a persisted or indexed computed column. There are few details in execution plans that can help determine the cause, making this a potentially frustrating issue to debug and fix.

Matching expressions to computed columns

It is worth being especially clear that there are two separate processes here:

  • Early expansion of computed columns; and
  • Later attempts at matching expressions to computed columns.

In particular, note that any query expression may be matched to a suitable computed column later on, not just expressions that arose from expanding computed columns.

Computed column expression matching can enable plan improvements even when the text of the original query cannot be modified. For example, creating a computed column to match a known query expression allows the optimizer to use statistics and indexes associated with the computed column. This feature is conceptually similar to indexed view matching in Enterprise Edition. Computed column matching is functional in all editions.

From a practical point of view, my own experience has been that matching general query expressions to computed columns can indeed benefit performance, efficiency, and execution plan stability. On the other hand, I have rarely (if ever) found computed column expansion to be worthwhile. It just never seems to yield any useful optimizations.

Computed column uses

Computed columns that are neither persisted nor indexed have valid uses. For example, they can support automatic statistics if the column is deterministic and precise (no floating point elements). They can also be used to save storage space (at the expense of a little extra runtime processor use). As a final example, they can provide a neat way to ensure that a simple calculation is always performed correctly, rather than being explicitly written out in queries each time.

Persisted computed columns were added to the product specifically to allow indexes to be built on deterministic but “imprecise” (floating point) columns. In my experience, this intended usage is relatively rare. Maybe this is simply because I do not encounter floating point data very much.

Floating point indexes aside, persisted columns are pretty common. To some extent, this may be because inexperienced users assume that a computed column must always be persisted before it can be indexed. More experienced users may employ persisted columns simply because they have found that performance tends to be better that way.

Indexed computed columns (persisted or not) can be used to provide ordering and an efficient access method. It can be useful to store a computed value in an index without also persisting it in the base table. Equally, suitable computed columns may also be included in indexes rather than being key columns.

Poor performance

A major cause of poor performance is a simple failure to use an indexed or persisted computed column value as expected. I have lost count of the number of questions I have had over the years asking why the optimizer would choose a terrible execution plan when an obviously better plan using an indexed or persisted computed column exists.

The precise cause in each case varies, but is almost always either a faulty cost-based decision (because scalars are assigned a low fixed cost); or a failure to match an expanded expression back to a persisted computed column or index.

The match-back failures are especially interesting to me, because they often involve complex interactions with orthogonal engine features. Equally often, the failure to “match back” leaves an expression (rather than a column) in a position in the internal query tree that prevents an important optimization rule from matching. In either case, the outcome is the same: a sub-optimal execution plan.

Now, I think it is fair to say that people generally index or persist a computed column with the strong expectation that the stored value will actually be used. It can come as quite a shock to see SQL Server recomputing the underlying expression each time, while ignoring the deliberately-provided stored value. People are not always very interested in the internal interactions and cost model deficiencies that led to the undesirable outcome. Even where workarounds exist, these require time, skill, and effort to discover and test.

In short: many people would simply prefer SQL Server to use the persisted or indexed value. Always.

A New Option

Historically, there has been no way to force SQL Server to always use the stored value (no equivalent to the NOEXPAND hint for views). There are some circumstances in which a plan guide will work, but it is not always possible to generate the required plan shape in the first place, and not all plan elements and positions can be forced (filters and compute scalars, for example).

There is still no neat, fully documented solution, but a recent update to SQL Server 2016 has provided an interesting new approach. It applies to SQL Server 2016 instances patched with at least Cumulative Update 2 for SQL Server 2016 SP1 or Cumulative Update 4 for SQL Server 2016 RTM.

The relevant update is documented in: FIX: Unable to rebuild the partition online for a table that contains a computed partitioning column in SQL Server 2016

As so often with support documentation, this does not say exactly what has been changed in the engine to address the issue. It certainly does not look terribly relevant to our current concerns, judging by the title and description. Nevertheless, this fix introduces a new supported trace flag 176, which is checked in a code method called FDontExpandPersistedCC. As the method name suggests, this prevents a persisted computed column from being expanded.

There are three important caveats to this:

  1. The computed column must be persisted. Even if indexed, the column must also be persisted.
  2. Match back from general query expressions to persisted computed columns is disabled.
  3. The documentation does not describe the function of the trace flag, and does not prescribe it for any other use. If you choose to use trace flag 176 to prevent expansion of persisted computed columns, it will therefore be at your own risk.

This trace flag is effective as a start-up –T option, at both global and session scope using DBCC TRACEON, and per query with OPTION (QUERYTRACEON).

Example

This is a simplified version of a question (based on a real-world problem) that I answered on Database Administrators Stack Exchange a few years ago. The table definition includes a persisted computed column:

CREATE TABLE dbo.T
(   
    ID integer IDENTITY NOT NULL,
    A varchar(20) NOT NULL,
    B varchar(20) NOT NULL,
    C varchar(20) NOT NULL,
    D date NULL,
    Computed AS A + '-' + B + '-' + C PERSISTED,

    CONSTRAINT PK_T_ID 
        PRIMARY KEY CLUSTERED (ID),
);
GO
INSERT dbo.T WITH (TABLOCKX)
    (A, B, C, D)
SELECT
    A = STR(SV.number % 10, 2),
    B = STR(SV.number % 20, 2),
    C = STR(SV.number % 30, 2),
    D = DATEADD(DAY, 0 - SV.number, SYSUTCDATETIME())
FROM master.dbo.spt_values AS SV
WHERE SV.[type] = N'P';

The query below returns all rows from the table in a particular order, while also returning the next value of column D in the same order:

SELECT
    T1.ID,
    T1.Computed,
    T1.D,
    NextD =
    (
        SELECT TOP (1)
            t2.D
        FROM dbo.T AS T2
        WHERE
            T2.Computed = T1.Computed
            AND T2.D > T1.D
        ORDER BY
            T2.D ASC
    )
FROM dbo.T AS T1
ORDER BY
    T1.Computed, T1.D;

Adding an index

An obvious covering index to support the final ordering and lookups in the sub-query is:

CREATE UNIQUE NONCLUSTERED INDEX IX_T_Computed_D_ID
ON dbo.T (Computed, D, ID);

The execution plan delivered by the optimizer is surprising and disappointing:

Plan with Clustered Index Scan and Sort

The Index Seek on the inner side of the Nested Loops Join seems to be all good. The Clustered Index Scan and Sort on the outer input, however, is unexpected. We would have hoped to see an ordered scan of our covering nonclustered index instead.

We can force the optimizer to use the nonclustered index with a table hint:

SELECT
    T1.ID,
    T1.Computed,
    T1.D,
    NextD =
    (
        SELECT TOP (1)
            t2.D
        FROM dbo.T AS T2
        WHERE
            T2.Computed = T1.Computed
            AND T2.D > T1.D
        ORDER BY
            T2.D ASC
    )
FROM dbo.T AS T1
    WITH (INDEX(IX_T_Computed_D_ID)) -- New!
ORDER BY
    T1.Computed, T1.D;

The resulting execution plan is:

Plan with Key Lookup

Scanning the nonclustered index removes the Sort, but adds a Key Lookup! The lookups in this new plan are surprising, given that our index definitely covers all columns needed by the query.

Analysis

Looking at the properties of the Key Lookup operator:

Key Lookup Properties

For some reason, the optimizer has decided that three columns not mentioned in the query need to be fetched from the base table (since they are not present in our nonclustered index by design).

Looking around the execution plan, we discover that the looked-up columns are needed by the inner side Index Seek:

Index Seek Details

Expansion

The first part of this seek predicate corresponds to the correlation T2.Computed = T1.Computed in the original query. The optimizer has expanded the definitions of both computed columns, but only managed to match back to the persisted and indexed computed column for the inner side alias T1. Leaving the T2 reference expanded has resulted in the outer side of the join needing to provide the base table columns (A, B, and C) needed to compute that expression for each row.

As is sometimes the case, it is possible to rewrite this query so that the problem goes away (one option is shown in my old answer to the Stack Exchange question).

Using the trace flag

Using SQL Server 2016, we can also try trace flag 176 to prevent the computed columns being expanded:

SELECT
    T1.ID,
    T1.Computed,
    T1.D,
    NextD =
    (
        SELECT TOP (1)
            t2.D
        FROM dbo.T AS T2
        WHERE
            T2.Computed = T1.Computed
            AND T2.D > T1.D
        ORDER BY
            T2.D ASC
    )
FROM dbo.T AS T1
ORDER BY
    T1.Computed, T1.D
OPTION (QUERYTRACEON 176); -- New!

The execution plan is now much improved:

Plan with TF 176

This execution plan contains only references to the computed columns. The Compute Scalars do nothing useful and would be cleaned up if the optimizer were a bit tidier around the house.

The important point is that the optimal index is now used correctly, and the Sort and Key Lookup have been eliminated. All by preventing SQL Server from doing something we would never have expected it to do in the first place (expanding a persisted and indexed computed column).

Using LEAD

The original Stack Exchange question was targeted at SQL Server 2008, where LEAD is not available. Let us try expressing the requirement on SQL Server 2016 using the newer syntax:

SELECT 
    T1.ID, 
    T1.Computed, 
    T1.D, 
    NextD =
        LEAD(T1.D) OVER (
            PARTITION BY T1.Computed 
            ORDER BY T1.D)
FROM dbo.T AS T1
ORDER BY 
    T1.Computed;

The SQL Server 2016 execution plan is:

LEAD execution plan

This plan shape is quite typical for a simple row mode window function. The one unexpected item is the Sort operator in the middle. If the data set were large, this Sort could have a big impact on performance and memory usage.

The issue, once again, is computed column expansion. In this case, one of the expanded expressions sits in a position that prevents normal optimizer logic simplifying the Sort away.

Added flag

Trying exactly the same query with trace flag 176:

SELECT 
    T1.ID, 
    T1.Computed, 
    T1.D, 
    NextD =
        LEAD(T1.D) OVER (
            PARTITION BY T1.Computed 
            ORDER BY T1.D)
FROM dbo.T AS T1
ORDER BY 
    T1.Computed
OPTION (QUERYTRACEON 176);

Produces the plan:

LEAD plan with TF 176

The Sort has disappeared as it should. Note also in passing that this query qualified for a trivial plan, avoiding cost-based optimization altogether.

Disabled General Expression Matching

One of the caveats mentioned earlier was that trace flag 176 also disables matching from expressions in the source query to persisted computed columns.

To illustrate, consider the following version of the example query. The LEAD computation has been removed, and the references to the computed column in the SELECT and ORDER BY clauses have been replaced with the underlying expressions. Run it first without trace flag 176:

SELECT 
    T1.ID, 
    Computed = T1.A + '-' + T1.B + '-' + T1.C,
    T1.D
FROM dbo.T AS T1
ORDER BY 
    T1.A + '-' + T1.B + '-' + T1.C;

The expressions are matched to the persisted computed column, and the execution plan is a simple ordered scan of the nonclustered index:

Simple query plan

The Compute Scalar there is once again just leftover architectural junk.

Without matching

Now try the same query with trace flag 176 enabled:

SELECT 
    T1.ID, 
    Computed = T1.A + '-' + T1.B + '-' + T1.C,
    T1.D
FROM dbo.T AS T1
ORDER BY 
    T1.A + '-' + T1.B + '-' + T1.C
OPTION (QUERYTRACEON 176); -- New!

The new execution plan is:

Simple query plan with TF 176

The Nonclustered Index Scan has been replaced with a Clustered Index Scan. The Compute Scalar evaluates the expression, and the Sort orders by the result. Deprived of the ability to match expressions to persisted computed columns, the optimizer cannot make use of the persisted value, or the nonclustered index.

Persistence

Note that the expression matching limitation only applies to persisted computed columns when trace flag 176 is active. If we make the computed column indexed but not persisted, expression matching works correctly.

In order to drop the persisted attribute, we need to drop the nonclustered index first. Once the change is made we can put the index straight back (because the expression is deterministic and precise):

DROP INDEX IX_T_Computed_D_ID ON dbo.T;
GO
ALTER TABLE dbo.T
ALTER COLUMN Computed
DROP PERSISTED;
GO
CREATE UNIQUE NONCLUSTERED INDEX IX_T_Computed_D_ID
ON dbo.T (Computed, D, ID);

The optimizer now has no problems matching the query expression to the computed column when trace flag 176 is active:

-- Computed column no longer persisted
-- but still indexed. TF 176 active.
SELECT 
    T1.ID, 
    Computed = T1.A + '-' + T1.B + '-' + T1.C,
    T1.D
FROM dbo.T AS T1
ORDER BY 
    T1.A + '-' + T1.B + '-' + T1.C
OPTION (QUERYTRACEON 176);

The execution plan returns to the optimal nonclustered index scan without a sort:

Computed column not persisted but indexed

Summary

To summarize: Trace flag 176 prevents persisted computed column expansion. As a side-effect, it also prevents query expression matching to persisted computed columns only.

Schema metadata is only loaded once, during the binding phase. Trace flag 176 prevents expansion so the computed column definition is not loaded at that time. Later expression-to-column matching cannot work without the computed column definition to match against.

The initial metadata load brings in all columns, not just those referenced in the query (that optimization is performed later). This makes all computed columns available for matching, which is generally a good thing. Unfortunately, if one of the loaded computed columns contains a scalar user-defined function, its presence disables parallelism for the whole query even when the problematic column is not used. Trace flag 176 can help with this as well, if the column in question is persisted. By not loading the definition, a scalar user-defined function is never present, so parallelism is not disabled.

Final Thoughts

It seems to me that SQL Server world be a better place if the optimizer treated persisted or indexed computed columns more like regular columns. In almost all instances, this would better match developer expectations than the current arrangement. Expanding computed columns into their underlying expressions and later attempting to match them back is not as successful in practice as theory might suggest.

Until SQL Server provides specific support to prevent persisted or indexed computed column expansion, new trace flag 176 is a tempting option for SQL Server 2016 users, albeit an imperfect one. It is a little unfortunate that it disables general expression matching as a side effect. It is also a shame that the computed column has to be persisted when indexed. There is then the risk of using a trace flag for other than its documented purpose to consider. I have heard of problems using this trace flag together with Adaptive Joins.

It is fair to say that the majority of problems with computed column queries can ultimately be resolved in other ways, given sufficient time, effort, and expertise. On the other hand, trace flag 176 often seems to work like magic. The choice, as they say, is yours.

To finish, here are some interesting computed column problems which benefit from trace flag 176 (broken links removed):