SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Non use of persisted data

Working at a client site, that in itself is good to say, I ran into a set of circumstances that made me ponder, and appreciate, the optimizer engine a bit more.

Working on optimizing a stored procedure, I found a piece of code similar to :

select BillToAddressID,
from sales.salesorderheader
where BillToAddressID = 985

A lovely scalar UDF was being used,  in actuality it was used as part of the WHERE clause but simplified here.  Normally I would use an inline table valued function here, but in this case it wasn't a good option.

So this seemed like a pretty good case to use a persisted column to improve performance.

The supporting index was already defined as

create index idxBill 
on sales.salesorderheader(BillToAddressID)
include (rowguid)

and the function code is

Create Function udfCleanGuid(@GUID uniqueidentifier)
returns varchar(255)
with schemabinding
Declare @RetStr varchar(255)
Select @RetStr=CAST(@Guid as varchar(255))
Select @RetStr=REPLACE(@Retstr,'-','')
return @RetStr

Executing the Select statement produced a plan of :


Nothing surprising, a seek to find the data and compute scalar to execute the UDF.

Lets get optimizing and remove the UDF with a persisted column

Alter table sales.salesorderheader
add CleanedGuid as dbo.udfCleanGuid(rowguid)

A subtle change to the SELECT statementā€¦

select BillToAddressID,CleanedGuid 
from sales.salesorderheader
where BillToAddressID = 985

and our new optimized plan looks likeā€¦


Not a lot different from before!  We are using persisted data on our table, where is the lookup to fetch it ?  It didnt happen,  it was recalculated.  Looking at the properties of the relevant Compute Scalar would confirm this ,  but a more graphic example would be shown in the profiler SP:StatementCompleted event.


Why did the lookup happen ? Remember the index definition,  it has included the original guid to avoid the lookup.  The optimizer knows this column will be passed into the UDF, run through its logic and decided that to recalculate is cheaper than the lookup.  That may or may not be the case in actuality,  the optimizer has no idea of the real cost of a scalar udf.  IMO the default cost of a scalar UDF should be seen as a lot higher than it is, since they are invariably higher.

Knowing this, how do we avoid the function call?  Dropping the guid from the index is not an option, there may be other code reliant on it.   We are left with only one real option,  add the persisted column into the index.

drop index Sales.SalesOrderHeader.idxBill
create index idxBill on sales.salesorderheader(BillToAddressID)
include (rowguid,cleanedguid)

Now if we repeat the statement
select BillToAddressID,CleanedGuid 
from sales.salesorderheader
where BillToAddressID = 985


We still have a compute scalar operator, but this time it wasnt used to recalculate the persisted data.  This can be confirmed with profiler again.


The takeaway here is,  just because you have persisted data dont automatically assumed that it is being used.


Posted by Steve Jones on 13 June 2011

Nice little writeup. Did this improve performance much? Any metrics that show how this might have changed the impact on the server?

Posted by Dave Ballantyne on 14 June 2011

Its was part of a much wider problem so I didnt really isolate the performance improvement of this per-se, needless to say things are in a much better state now :)

But, i think it is important to understand , this ( not using persisted data) could also improve performance.  If the definition is a simple A+B then the cost of executing that WILL be less than the bookmark lookup to fetch the data.

In this case it was a HEAVY udf , In a where clause ( more on that issue here sqlblogcasts.com/.../using-a-udf-as-a-sarg-make-a-hash-of-it.aspx ), that really hurt, but "It Depends".

Leave a Comment

Please register or log in to leave a comment.