SQL Server – Table Spool – Causation – Indexes on Non-Persisted Columns (along with an Output Clause)

Background

Table Spools is a fact of Life in SQL Server.

One of the cases that I recently discovered is when one has an index on a non-persisted computed column and one introduces an Output Clause to capture the effects of the change.

 

Create Tables

if DB_ID('Demo') is null
begin

    exec('create database [Demo]');

end
go

USE [Demo]
GO

if OBJECT_ID('accountActivity') is not null
begin

    drop table [accountActivity]

end
go

if OBJECT_ID('accountActivityPersisted') is not null
begin

    drop table [accountActivityPersisted]

end
go

CREATE TABLE [dbo].[accountActivity]
(
	[AccountID] [bigint] NOT NULL , --IDENTITY(1,1) NOT NULL,
	[AccountCreationDate] [datetime] NULL,
	[TransactionDate] [datetime] NULL,
	[ageCalc]  
            AS (datediff(day,[AccountCreationDate],[TransactionDate])),
     CONSTRAINT PK_AccountActivity PRIMARY KEY CLUSTERED 
    (
	    [AccountID] ASC
    )
) 
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_AgeCalc] 
ON [dbo].[accountActivity] 
(
	[ageCalc] ASC
)
go

CREATE TABLE [dbo].[accountActivityPersisted]
(
	[AccountID] [bigint] NOT NULL , --IDENTITY(1,1) NOT NULL,
	[AccountCreationDate] [datetime] NULL,
	[TransactionDate] [datetime] NULL,
	[ageCalc]  AS 
          (datediff(day,[AccountCreationDate],[TransactionDate])) 
           persisted  ,
    CONSTRAINT PK_AccountActivityPersisted PRIMARY KEY CLUSTERED 
    (
	    [AccountID] ASC
    )
) 
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_AgeCalc] 
ON [dbo].[accountActivityPersisted] 
(
	[ageCalc] ASC
)
go

</code></pre>
<h2></h2>
<h1>Test SQL</h1>
<pre><code>

use [Demo]
go

set statistics io on
set nocount on
go

begin tran

    DECLARE @Results AS TABLE
    (
      AccountID BIGINT
    )

    declare	
           @AccountID bigint
	 , @AccountCreationDate datetime
    	 , @TransactionDate datetime

    set @AccountID = RAND() * 1E10
    set @AccountCreationDate = '1/1/2001'
    set @TransactionDate = GETUTCDATE()

    Insert into dbo.[accountActivity]
    (
         [AccountID]
       , [AccountCreationDate]
       , [TransactionDate]

   )
   OUTPUT 
      INSERTED.AccountID

  INTO @Results		
   (
       AccountID
   )
   select
              @AccountID
	    , @AccountCreationDate
	    , @TransactionDate

   Insert into dbo.[accountActivityPersisted]
   (
        [AccountID]
      , [AccountCreationDate]
      , [TransactionDate]
   )
   OUTPUT 
      INSERTED.AccountID
   INTO @Results		
   (
       AccountID
   )
   select
             @AccountID
           , @AccountCreationDate
	   , @TransactionDate

rollback tran

Execution Plan

  • On a scale of 100, the cost of maintaining an index when the columns are not persisted is about 59%
  • Whereas for non-persisted columns, it is 41%

Table Spool - Index on Non-Persisted Columns

Statistics I/O

  • The worktable maintenance cost is quite a bit 

Non-Persisted Columns



Table '#744F2D60'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'accountActivity'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Persisted Columns


Table '#744F2D60'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'accountActivityPersisted'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Screen Shot:
Table Spool - Index on Non-Persisted Columns (Statistics IO)

 

Moral of the Story

Persist your computed columns.

 

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s