While troubleshooting a slow query I was misled for a while by the actual execution plan telling me a sort late in the query where there should be few rows was taking >80% of the query time. At the time the query was built dynamically in a stored procedure and the bulk of the slowness was being caused by parameter sniffing, but for a long time I tried to figure out why it was taking so long to sort these rows.
Here's the query:
SELECT Id , FirstName , LastName , FullName , DateOfBirth , CityStateZip , DriversLicenseState , DriversLicenseNumber FROM (SELECT c.EntityId Id , p.FirstName , p.LastName , p.FullName , c.DateOfBirth , c.UpdateDate , CityStateZip = CASE a.EntityAddressId WHEN NULL THEN NULL ELSE CONCAT(a.City, ', ', s.Code, ' ', a.Zip) END , ds.Value DriversLicenseState , dn.Value DriversLicenseNumber , ROW_NUMBER() OVER ( PARTITION BY p.FirstName, p.LastName, c.DateOfBirth , ds.Value, dn.Value ORDER BY c.UpdateDate DESC) RowNum FROM Store.Customer c INNER JOIN Entity.Person p ON c.EntityId = p.EntityId LEFT JOIN Entity.EntityAddress a ON c.EntityId = a.EntityId LEFT JOIN Vendor.StateProvince s ON a.StateProvinceId = s.StateProvinceId LEFT JOIN ( SELECT ca.CustomerId, ca.Value FROM Store.CustomerAttribute ca INNER JOIN Models.Attribute a ON ca.AttributeId = a.AttributeId WHERE a.AttributeCode = 'DriversLicenseState') as ds ON c.EntityId = ds.CustomerId LEFT JOIN ( SELECT ca.CustomerId, ca.Value FROM Store.CustomerAttribute ca INNER JOIN Models.Attribute a ON ca.AttributeId = a.AttributeId WHERE a.AttributeCode = 'DriversLicenseNumber') as dn ON c.EntityId = dn.CustomerId WHERE LOWER(FirstName) LIKE '%bob%' AND LOWER(LastName) LIKE '%smith%' AND a.EntityAddressTypeId = 0 ) c WHERE RowNum = 1 ORDER BY UpdateDate DESC
And here's the relevant part of the execution plan:
Notice the large disparity between the estimated and actual number of rows.
If I remove the 2 LOWER calls from the inner WHERE clause and change nothing else, the execution plan steps are identical, but the cost of the steps are different. The same bits of the execution plan:
I cleared the plan cache between executions. Is this a bug?