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: 353
        
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
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
