Non use of persisted data – Part deux
- by Dave Ballantyne
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…..
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.
