CLR Procedure seems to be spend more time in the SLEEP_TASK wait state then a straight t-sql query

Question

 

I have a CLR procedure that is calculating a cumulative total of a few different fields.  This procedure has to read through roughly 300 million + rows to calculate the cumulative total breaking the total on different fields.  To source the data I have a query that sorts the data according to the breaks.  When I run this query in SSMS it starts to return rows in about 3 minutes.  I have the same query executing inside my CLR procedure using a datareader through the context connection.  My thinking was that the CLR procedure could start to work on the cumulative totaling in about the same time 3 minutes, as soon as it gets the first record back from SQL Server it would have 1 record to return, that record would have a cumulative total equal to itself.  The CLR procedure then returns these rows through the use of the SqlPipe.SendResultsRow(Record) method.  It seems looking at dynamic management views that the T-SQL query gets put in SUSPEND status for CXPACKET waits while the CLR procedure is put in SUSPEND status for SLEEP_TASK waits.  The CLR procedure also uses about 5000% more tempdb space, while both queries share the same execution plan. 

 

Is there something that causes queries executing in the CLR to wait longer and more often?  Am I chasing the wrong issue and my waits are cause by my accumulation logic, which is very simple?  Any advice is welcome.

Thank you,

Steve

Note: If I was running SQL 2012 I could use the OVER clause with PARTITION but I don't have that option, and I wouldn't be using the CLR at all for this purpose.  Using other T-SQL methods of calculating cumulative totals, self joins, temp table variable, and a cursor do not work well with a record set of this size on the server I am working on.

Answer 1

Update:  Looks like this has to do with returning the rows to SQL Server not gathering them.  It looks like the SqlPipe.SendResultsRow method must be causing some kind of tempdb storage to be used when the results returned are being inserted into a table.  I don't know if this this is caused by the Insert or by the SendResultsRow or a combination of the both but SqlPipe.Send(Record) while creating many results sets, it does not seem to intially suffer the same issue.