January 21, 2014 at 8:06 am
On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()
to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end
it takes 8 seconds.
I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
I really have no idea why this takes so long. As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
Can anybody help? anybody any idea what might be the issue here?
January 21, 2014 at 9:30 am
First thing to do is to check how long SQL Server thinks it took to run. There are example queries all over the web for this - you can adapt one of these queries to specifically return your SP: http://sqlserverperformance.wordpress.com/2008/01/21/five-dmv-queries-that-will-make-you-a-superhero[/url]
Check your worker time and elapsed time. Worker time is CPU, and elapsed time is the overall duration. Note - the times are in microseconds so you'll need to do some maths to get seconds back.
If the times correspond to the 8 seconds you're seeing, then it probably suggests your instance is overworked and lacking CPU and/or RAM.
I suspect however it's network latency, where it's taking pretty much all of that time to pass the 12k records to SQL Server.
January 21, 2014 at 9:44 am
Hi, thanks for the answer.
CPU and RAM on the machine are fine. when running the procedure there is no significant CPU or memory stress in SQL Server (or outside).
How can it be network latency when a CLR proc within SQL server is calling a T-SQL proc within the same database of the same server?
12k rows may sound much, but the columns are actually
A varchar(3)
B int
C decimal(30, 10)
so the whole table is actually less than 300 kb.
January 21, 2014 at 9:59 am
Sorry, totally missed the CLR references and thought you were calling it from app code.
You should check your wait stats. If you can run the SP in isolation on your instance then you can clear the wait stats, run it, then see what waits have appears in the DMV, sys.dm_os_wait_stats. If this isn't feasible and you need to isolate the SP, have a look at this article: http://www.sqlskills.com/blogs/paul/capturing-wait-stats-for-a-single-operation/[/url]
It should help identify what is causing the delay.
You're right, 300Kb is not a lot of data so it should be very fast. It would probably help if you also posted your CLR code to see if there's anything amiss in it. It doesn't sound right that a statement is fired for every record in the input.
January 22, 2014 at 6:02 am
It was a congestion in the tempdb.
In the end even "normal" inserts were taking ages. A restart of SQL Server and thus recreating the tempdb solved the problem for now.
In the long run we will change the architecture, since TVP in SQLCLR doesn't seem to be supported very well.
Thanks for your thoughts and help.
January 22, 2014 at 7:20 am
Thanks for posting the update.
Sounds like you have some work to do optimising your tempdb setup & usage. Let me know if you have any questions around that - there are lots of resources on the web around the subject though.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply