2

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:

enter image description here

Is there a way I can spot the culprit more easily in the future? The actual source of slowness is completely hidden from view.

  • try_parse is a clr function, and those are quite heavy operations. Same thing with format. I would assume SQL Server has no way of even estimating the real cost of CRLs (and it doesn't do it even for UDFs) – James Z Nov 22 '16 at 18:32
  • A Mssqltips,com benchmark showing large performance differences for try_convert/cast/parse() across different datatypes. – crokusek Jul 30 '18 at 17:45
4

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)

enter image description here

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.

0

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.

  • 3
    How would that help here? The compute scalar with try parse is shown as 0% cost. – Martin Smith Nov 22 '16 at 12:19
  • A also said to hover the mouse on each stage. Check the costs there – Renato Afonso Nov 22 '16 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.