How to diagnose try_parse being very slow?

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!

Normal query:

SELECT CloseDate
FROM MyTable

(4959 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 17 ms.

With try_parse:

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.

CPU time = 719 ms, elapsed time = 718 ms.

Shows that this is CPU bound.

If you can reproduce the issue on a dev machine one way of seeing what the CPU is spending time on is to use Windows Performance Recorder.

After tracing for a few seconds whilst the following was running concurrently...

SET nocount ON;

DECLARE @d DATETIME

WHILE 1 = 1
  SELECT @d = try_parse(NAME AS DATETIMEusing 'en-us')
  FROM   master..spt_values

... I see (click to embiggen)

SQL Server is attributed 20.88% of total CPU time over that period. Over 75% of that amount is taken up with

SqlAccess.dll!System.Data.SqlServer.Internal.SqlParseIntrinsicImpl::
SqlAccess.dll!System.Data.SqlServer.Internal.SqlAppDomain::ExecuteExtension

With a healthy chunk of that taken up with

clr.dll!IL_Throw
mscorlib.ni.dll!System.DateTimeParse.GetDateTimeParseException(System.DateTimeResult ByRef)

None of the names in master..spt_values happen to be parsed as valid dates so all end up returning null.

The above shows that for some reason TRY_PARSE calls the DateTime.Parse method and catches the exception rather than using the built in TryParse method that would likely perform better in this case.

StackExchange Database Administrators Q#156012, answer score: 5