Monday, September 1, 2008

Computed column is PERSISTED?

Hi everybody.
I'd like to share with you how important to define a computed column to be PESRISTED.
As you know from the BOL
/*
For columns specified as PERSISTED, the SQL Server 2005 Database Engine physically stores the computed values in the table and updates the values when any other columns on which the computed column depends are updated. By marking a computed column as PERSISTED, you can create indexes on computed columns defined on expressions that are deterministic, but not precise.
*/
I visited our client two days ago who has been experienced with performance issue for one of their very important query.One big table containes a computed column which SELECT statement is using for to return to the client. We have seen very high number of logical reads and TWO computer scalar iterators. For an obvious reason we define the computed column as PESRISTED and performance was increased dramatically.Moreover, create an index on computed column and see how perfromance will be increased more..


See demo script to see how it is affected.

CREATE TABLE t(c INT NOT NULL identity(1,1) PRIMARY KEY,
c1 AS '00'+cast(c AS VARCHAR(100)))

SET NOCOUNT ON
INSERT INTO t DEFAULT VALUES
GO 100000


SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT c1 FROM t
--Table 't'. Scan count 1, logical reads 1250, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--select c1 from t
|--Compute Scalar(DEFINE:([master].[dbo].[t].[c1]=[master].[dbo].[t].[c1]))
|--Compute Scalar(DEFINE:([master].[dbo].[t].[c1]='00'+CONVERT(varchar(100),[master].[dbo].[t].[c],0)))
|--Clustered Index Scan(OBJECT:([master].[dbo].[t].[PK__t__1446FBA6]))



SET STATISTICS IO ON
SET STATISTICS PROFILE ON

SELECT c1 FROM t
--Table 't'. Scan count 0, logical reads 50, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--select c1 from t
|--Compute Scalar(DEFINE:([master].[dbo].[t].[c1]=[master].[dbo].[t].[c1]))
|--Clustered Index Scan(OBJECT:([master].[dbo].[t].[PK__t__1446FBA6]))

DROP TABLE t

No comments: