How to diagnose try_parse being very slow?
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::&lt;ParseSsDate&gt;
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.