Calling stored procedures from within other stored procedures

Question

Calling stored procedures from within other stored procedures

kevaburg

SSCoach

Points: 18131
More actions
December 11, 2017 at 11:32 am

#330422

Hi folks,
I heard something today that has me wondering: Is it is a good idea to write a long stored procedure (200+ lines) or is it better to break the whole thing down into smaller procedures and call them from within another procedure? The idea behind it is based on parallelism. In the first instance it was explained (righty or wrongly) that when the main procedure is called that 16 threads (our MaxDOP in this instance) are reserved and held for the procedure/transaction. His train of thought is that when procedures are called from within another stored procedure this reservation only occurs once and the other "child" procedures run within those 16 threads.
My train of thought is that when a new transaction is started within a stored procedure called from within another stored procedure the reservation of the 16 threads happens again, thus reserving in this case a total of 32 threads.
The whole argument assumes of course that the cost threshold for parallelism is exceeded, which in this case it often is.
Unfortunately I can't post any code (if any of you have used Relativity then you will understand why) but then I think this is more of a question of understanding rather than an analysis of the code in question.
Any constructive thoughts on the matter would be greatly appreciated...please no comments on the MaxDOP....it gives me a headache trying to explain why it is too high....
In between I am going to give Itzik Ben-Gans and Grant Fritchleys books a good hammering in the continuing search for answers and perhaps redemption........ 🙂
Regards,
Kev

Viewing 7 posts - 1 through 6 (of 6 total)

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

The DOP needed is determined by the query optimizer for each statement that is run(unless you over ride it with a query hint), I don't think there's any concept of reserving threads for an entire SP as each statement in the SP might have different requirements and reserving the maximum possible threads for a SP that maybe only needs 1 thread would be a waste.

kevaburg SSCoach Points: 18131 More actions · Answer 2

ZZartin - Monday, December 11, 2017 12:07 PM
The DOP needed is determined by the query optimizer for each statement that is run(unless you over ride it with a query hint), I don't think there's any concept of reserving threads for an entire SP as each statement in the SP might have different requirements and reserving the maximum possible threads for a SP that maybe only needs 1 thread would be a waste.

Hi ZZartin,

thanks for the reply. This is exactly what I was thinking and you have confirmed it. Now all I need to do is prove it....

Regards,
Kev

Sue_H SSC Guru Points: 90860 More actions · Answer 3

kevaburg - Monday, December 11, 2017 12:20 PM
ZZartin - Monday, December 11, 2017 12:07 PM
The DOP needed is determined by the query optimizer for each statement that is run(unless you over ride it with a query hint), I don't think there's any concept of reserving threads for an entire SP as each statement in the SP might have different requirements and reserving the maximum possible threads for a SP that maybe only needs 1 thread would be a waste.
Hi ZZartin,
thanks for the reply. This is exactly what I was thinking and you have confirmed it. Now all I need to do is prove it....
Regards,
Kev

Pretty sure it's documented somewhere in MS docs about the startup procedures and each taking one thread. So those stored procedures aren't reserving any max dop number of threads. That would be one angle if someone said it always does this. I haven't checked for it yet - should be in the startup procs documentation I think.
But my question would be has whoever said this provided any proof of this reserving 16 threads? People always make different claims - it should be up to them to prove those claims. Too often it ends up where everyone else is trying to disprove such claims.

Sue

kevaburg SSCoach Points: 18131 More actions · Answer 4

Sue_H - Monday, December 11, 2017 12:30 PM
kevaburg - Monday, December 11, 2017 12:20 PM
ZZartin - Monday, December 11, 2017 12:07 PM
The DOP needed is determined by the query optimizer for each statement that is run(unless you over ride it with a query hint), I don't think there's any concept of reserving threads for an entire SP as each statement in the SP might have different requirements and reserving the maximum possible threads for a SP that maybe only needs 1 thread would be a waste.
Hi ZZartin,
thanks for the reply. This is exactly what I was thinking and you have confirmed it. Now all I need to do is prove it....
Regards,
Kev
Pretty sure it's documented somewhere in MS docs about the startup procedures and each taking one thread. So those stored procedures aren't reserving any max dop number of threads. That would be one angle if someone said it always does this. I haven't checked for it yet - should be in the startup procs documentation I think.
But my question would be has whoever said this provided any proof of this reserving 16 threads? People always make different claims - it should be up to them to prove those claims. Too often it ends up where everyone else is trying to disprove such claims.
Sue

Hi Sue,

thanks for the answer. This person hasn't produced any proof and it is my job to provide evidence to the contrary. The good thing is I will learn something while doing that....he will remain ignorant of the facts.

Cheers for the Startup procs hint...my next stop. 🙂

Regards,
Kev

x SSC-Insane Points: 23660 More actions · Answer 5

My guess:

Starting up stored procedures in parallel is much different than calling stored procedures from within other stored procedures. If you start up two stored procedures in an asynchronous manner then they do execute in parallel, they'll use two processes (or threads or whatever), and they'll each get your max dop. If you call one stored procedure from another, the calling stored procedure is only executing statements in the called stored procedure, so they'll share the same max dop, ie., there is no profit in starting new processes simply by executing a local bit of programming in a synchronous (non multithreaded/multitasking manner). Possibly maybe having one stored procedure call a clr would mean some context changes and I don't have a clue on that one!

But my guess would remain that for each database "connection" (remote connection, agent job, etc), the max dop is constant unless changed with a hint.

edit: I'm just thinking that while the "called" stored procedure is executing and maybe even parallelizing its workload, the calling stored procedure is literally twiddling its thumbs waiting on its "exec child_stored_procedure" to finish, right? So why would max dop even be spent at all with the caller?

edit 2:
In other words, I believe his train of thought is closer but also my guess is that technically both of you are wrong in that no threads are reserved at all, they're provided on demand up to the max dop limit currently in force, ie., max dop is not a reservation, its a limit.

His train of thought is that when procedures are called from within another stored procedure this reservation only occurs once and the other "child" procedures run within those 16 threads.

Would be pretty cool to be wrong though, new stuff to explore!

x SSC-Insane Points: 23660 More actions · Answer 6

ZZartin - Monday, December 11, 2017 12:07 PM
The DOP needed is determined by the query optimizer for each statement that is run(unless you over ride it with a query hint), I don't think there's any concept of reserving threads for an entire SP as each statement in the SP might have different requirements and reserving the maximum possible threads for a SP that maybe only needs 1 thread would be a waste.

oops should have read your post before posting, I agree with your assessment and additionally, I don't think calling a child stored procedure ups the limit in any way.