How to diagnose try_parse being very slow?

Question

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.

Answer 1

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::<ParseSsDate>
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.

Answer 2

It's never that simple. You really have to try several ways of executing the query and check the differences. Not only checking the execution times, but hovering the mouse over each stage in the execution plan, and check the costs.