Non use of persisted data – Part deux

Posted by Dave Ballantyne on SQL Blogcasts See other posts from SQL Blogcasts or by Dave Ballantyne
Published on Mon, 20 Jun 2011 07:30:00 GMT Indexed on 2011/06/20 16:30 UTC
Read the original article Hit count: 295

Filed under:

In my last blog I showed how persisted data may not be used if you have used the base data on an include on an index.

That wasn't the only problem ive had that showed the same symptom.  Using the same code as before,  I was executing similar to the below :

select BillToAddressID,SOD.SalesOrderDetailID,SOH.CleanedGuid 
from sales.salesorderheader SOH
join Sales.SalesOrderDetail SOD
on SOH.SalesOrderID = SOD.SalesOrderID

But,  due to a distribution error in statistics i found it necessary to use a table hint.  In this case, I wanted to force a loop join

select BillToAddressID,SOD.SalesOrderDetailID,SOH.CleanedGuid 
from sales.salesorderheader SOH
inner loop join Sales.SalesOrderDetail SOD
on SOH.SalesOrderID = SOD.SalesOrderID
 
But, being the diligent  TSQL developer that I am ,looking at the execution plan I noticed that the ‘compute scalar’ operator was again calling the function.  Again,  profiler is a more graphic way to view this…..
 

image

All very odd,  just because ive forced a join , that has NOTHING, to do with my persisted data then something is causing the data to be re-evaluated.

Not sure if there is any easy fix you can do to the TSQL here, but again its a lesson learned (or rather reinforced) examine the execution plan of every query you write to ensure that it is operating as you thought it would.

© SQL Blogcasts or respective owner