Varbinary(max) parameters in Nested Stored Procedures by value or reference

Question

Varbinary(max) parameters in Nested Stored Procedures by value or reference

rob.delong

Old Hand

Points: 308
More actions
September 15, 2014 at 4:52 pm

#299436

Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to the nested stored procedure.
Is a copy of the BLOB provided to the nested stored procedure (passed by value) OR is the BLOB passed by reference.
My interest is in understanding the potential memory hit when handling large BLOBs in this environment.
For example, if the BLOB is 200MB, will SQL server need to allocate memory for a new copy each time it's passed to another stored procedure?
Looks like table type parameters are passed by reference, but I haven't been able to find any info on BLOBS in this context.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

rob.delong (9/15/2014)
Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to the nested stored procedure.
Is a copy of the BLOB provided to the nested stored procedure (passed by value) OR is the BLOB passed by reference.
My interest is in understanding the potential memory hit when handling large BLOBs in this environment.
For example, if the BLOB is 200MB, will SQL server need to allocate memory for a new copy each time it's passed to another stored procedure?
Looks like table type parameters are passed by reference, but I haven't been able to find any info on BLOBS in this context.

A short answer is no extra memory hit, the concept of passing by value or reference is irrelevant in SQL Server. All data is stored in units call pages which are read into memory when needed, there is no way of bypassing this.

😎

rob.delong Old Hand Points: 308 More actions · Answer 2

I appreciate your reply Eirikur. I wonder if you could provide more details though. I haven't had any luck finding any reference to "units call pages" (or variations thereof).

Your answer seems to conflict somewhat with Microsoft documentation on the subject which seems to say the question of 'by reference' or 'by value' is relevant, for example:

http://msdn.microsoft.com/en-us/library/bb510489.aspx

Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.

And the somewhat dated (but perhaps still relevant) blog by TSQL developers:

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/06/09/624531.aspx

When a parameter is passed to a stored proc, a local copy of the parameter is made for the stored proc to use. This is no different then the by-value semantics of most programming languages. Where TSQL differs is that when the stored proc finishes executing (without hitting an error) a copy-out is made which updates the parameter passed in with any changes that were made to it in the stored proc. In this manner it appears to the caller of the stored proc that the parameter was passed by reference, since any changes made to the parameter in the stored proc propagate back to the caller when the stored proc finishes executing successfully.

These are the primary references that led to my concern regarding memory use for varbinary parameters in nested stored procedures.

If you could provide a pointer to an explanation of the memory handling you referenced in your answer, I would be grateful.

thanks

rob.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

rob.delong (9/17/2014)
I appreciate your reply Eirikur. I wonder if you could provide more details though. I haven't had any luck finding any reference to "units call pages" (or variations thereof).
Your answer seems to conflict somewhat with Microsoft documentation on the subject which seems to say the question of 'by reference' or 'by value' is relevant, for example:
http://msdn.microsoft.com/en-us/library/bb510489.aspx
Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.
And the somewhat dated (but perhaps still relevant) blog by TSQL developers:
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/06/09/624531.aspx
When a parameter is passed to a stored proc, a local copy of the parameter is made for the stored proc to use. This is no different then the by-value semantics of most programming languages. Where TSQL differs is that when the stored proc finishes executing (without hitting an error) a copy-out is made which updates the parameter passed in with any changes that were made to it in the stored proc. In this manner it appears to the caller of the stored proc that the parameter was passed by reference, since any changes made to the parameter in the stored proc propagate back to the caller when the stored proc finishes executing successfully.
These are the primary references that led to my concern regarding memory use for varbinary parameters in nested stored procedures.
If you could provide a pointer to an explanation of the memory handling you referenced in your answer, I would be grateful.
thanks
rob.

I think you are over thinking the problem a bit. Based on the second article you linked to, it pretty clearly, at least to me, states that parameters passed in to stored procedures are passed by-value especially when from the calling application. They have to be coded this way because most calls are not made from within the server but from a client, so the SQL Server has no way to access values by reference because it doesn't have access to the client memory. This would be true of table-valued parameters as well, when passed to the SQL Server from the client. Once on the SQL Server, the table-valued parameter works like a table variable and is either stored in memory or spilled to tempdb if not enough memory is available, so when the article talks about passing table valued parameters by reference I'm pretty sure it is talking about passing the reference to the stored values.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rob.delong Old Hand Points: 308 More actions · Answer 4

Thanks,

I understand the initial call to sql server requires parameters be copied. I'm not asking the question very well, I guess.

In the following chain where SPR1 and SPR2 receive the same varbinary(max):

Call from client to ->SPR1: we get a new copy of varbinary(max) created on sql server.

...SPR1 does some stuff...

Call from SPR1->SPR2: ?? do we get a new copy of varbinary(max) with associated memory hit.

...SPR2 returns, SPR1 does some more stuff...

Jack Corbett SSC Guru Points: 184393 More actions · Answer 5

I have gotten confirmation from an outside source, Hugo Kornelis, that when you pass a parameter between SP's it is by value and is a new copy of the value in memory.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rob.delong Old Hand Points: 308 More actions · Answer 6

Thanks for looking into this. Yes, that's my understanding of the general rule as well.

Microsoft made an exception to this for table valued parameters (specifically to avoid the memory hit).

I take it Mr. Kornelis gave no indication that such an exception was also made for large varbinary(max) variables then?

I wonder if the READONLY keyword changes the memory handling situation for a parameter...

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

I can confirm that (MAX) (any form) variables are passed byVal up to version 2K8 R2. byRef is reserved only for the table variable parameter, and that is a read only item once it's generated. XML is also byVal. The easiest way to confirm is to nest a procedure and alter it in the subprocedure. Then return it from both the nested and the calling procedure.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

george.lewycky Mr or Mrs. 500 Points: 570 More actions · Answer 8

Thanks for this posting and replies. I too was looking for this answer, that was the first part of it.

I have a SP to send out up to 1,000 emails in batches (the same email and format) but it requires our logo. Right now I hosted it on my own web domain to make the user happy and the manager. But is more efficient storing and referencing an image for HTML emails using

sp_send_cdosysmail using a URL or straight from the database as a BLOB ??

Thanks

George