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