Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. However the first query completes in 132 ms and the second query completes in 8,531 ms.

```
--query 1
with results as(
SELECT
P.[patient_guid]
,P.[client_guid]
,P.[patient_account_id]
,C.[database_guid]
,P.[patient_name]
,P.[rabies_tag_number]
,P.[rabies_serial_number]
,P.[rabies_brand_name]
,P.[species]
,P.[breed]
,P.[coat_color]
,P.[sex]
,P.[birthdate]
,P.[latest_visit]
,P.[first_visit]
,P.[maturity_code]
,P.[medical_alert]
,P.[chronic_ailment]
,P.[continuous_medication]
,P.[weight]
,P.[doctor_preference]
,P.[tattoo_number]
,P.[avid_chip_number]
,P.[comment]
,P.[special_instructions]
,P.[health_plan_name]
,P.[deleted]
,P.[has_lab_results]
,P.[trainer_id]
,P.[stable_id]
,C.[first_name]
,C.[last_name]
,P.[active]
,P.[deceased_date]

,row_number() over (order by patient_name) as rownumber
,row_number() over (order by patient_name DESC) as totalRows
FROM [patients] P

inner join clients C on P.client_guid = C.client_guid
where C.database_guid = @DatabaseGuid and P.patient_name like '%'+@SearchText+'%')

select patient_guid, client_guid, patient_account_id, database_guid, patient_name, rabies_tag_number, rabies_serial_number, rabies_brand_name, species,
breed, coat_color, sex, birthdate, latest_visit, first_visit, maturity_code, medical_alert, chronic_ailment, continuous_medication, weight,
doctor_preference, tattoo_number, avid_chip_number, comment, special_instructions, health_plan_name, deleted, has_lab_results, first_name, last_name, trainer_id, stable_id, active, deceased_date,
totalRows + rownumber - 1 as total fr

The sub query in the second query it's what's killing you that requires that the sub query needs to be run once per row that's returned by the record set. If you look at the plan it'll tell you how many times each operator is executed. I'll bet that you'll see a high number of executions on some operators of the second query. (I'm on my phone at the moment so I can't open the plan.)

StackExchange Database Administrators Q#62424, answer score: 4