I had a very large query that ran slower than I thought it should do, but no amount of digging through the query execution plan helped shed any light on the slowness. Eventually I narrowed it down though:
try_parse was the culprit!
SELECT CloseDate FROM MyTable (4959 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 17 ms.
SELECT try_parse(CloseDate as datetime using 'en-us') FROM MyTable (4959 row(s) affected) SQL Server Execution Times: CPU time = 719 ms, elapsed time = 718 ms.
The execution plan in the latter case looks innocent enough:
Is there a way I can spot the culprit more easily in the future? The actual source of slowness is completely hidden from view.