T-SQL batch V/S Using Stored Procedure

Question

T-SQL batch V/S Using Stored Procedure

Mahesh Bote

SSCrazy Eights

Points: 9107
More actions
March 7, 2019 at 7:26 am

#377149

Hi Experts,
Please can you guide on the approach wrapping of a T-SQL batch into a Stored Procedure?
We have few T-SQL queries getting passed to SQL Server to execute. We have observed couple of times, those T-SQL batches tend to consume more time to consume. We have identified the the root cause and have fixed accordingly. Now those batches are running smoothly. Next one more approach we are going to implement is to wrap the T-SQL batches into Stored Procedure. So can anybody advise the benefits of smoothly running T-SQL batch into Stored Procedure.
Thanks in advance,
MH-09-AM-8694

Viewing 5 posts - 1 through 4 (of 4 total)

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Wrapping your T-SQL code in a stored procedure is a matter of syntax, (look up CREATE PROCEDURE) and the usual benefits are those associated with encapsulation as a general principle. There's also the benefit of probable re-use of the query plan. And then perhaps only having one place to go to make changes. It also might make keeping a source control system up to date, easier to do, for similar reasons.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

sgmunson - Thursday, March 7, 2019 12:47 PM
Wrapping your T-SQL code in a stored procedure is a matter of syntax, (look up CREATE PROCEDURE) and the usual benefits are those associated with encapsulation as a general principle. There's also the benefit of probable re-use of the query plan. And then perhaps only having one place to go to make changes. It also might make keeping a source control system up to date, easier to do, for similar reasons.

queries have reusable plans also, don't they?

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 3

patrickmcginnis59 10839 - Friday, March 8, 2019 8:36 AM
sgmunson - Thursday, March 7, 2019 12:47 PM
Wrapping your T-SQL code in a stored procedure is a matter of syntax, (look up CREATE PROCEDURE) and the usual benefits are those associated with encapsulation as a general principle. There's also the benefit of probable re-use of the query plan. And then perhaps only having one place to go to make changes. It also might make keeping a source control system up to date, easier to do, for similar reasons.
queries have reusable plans also, don't they?

Technically they can but in practice, not very likely.
With ad-hoc queries, a "plan stub" is cached, if the same query is called again the actual plan will be cached and be made reusable.
the problem is that plan matching is based on the query hash value... Meaning that if a single character, anywhere in the script (including comments), changes, the hash value changes and the plan is no longer reusable.
The "optimize for ad-hoc" setting also alters this behavior.

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

Jason A. Long - Friday, March 8, 2019 10:56 AM
patrickmcginnis59 10839 - Friday, March 8, 2019 8:36 AM
sgmunson - Thursday, March 7, 2019 12:47 PM
Wrapping your T-SQL code in a stored procedure is a matter of syntax, (look up CREATE PROCEDURE) and the usual benefits are those associated with encapsulation as a general principle. There's also the benefit of probable re-use of the query plan. And then perhaps only having one place to go to make changes. It also might make keeping a source control system up to date, easier to do, for similar reasons.
queries have reusable plans also, don't they?
Technically they can but in practice, not very likely.
With ad-hoc queries, a "plan stub" is cached, if the same query is called again the actual plan will be cached and be made reusable.
the problem is that plan matching is based on the query hash value... Meaning that if a single character, anywhere in the script (including comments), changes, the hash value changes and the plan is no longer reusable.
The "optimize for ad-hoc" setting also alters this behavior.

I read that your description regarding query stubs ONLY works when "optimize for ad-hoc" is set to 1. Also typically queries from applications should be parameterized for many reasons, the biggest of course being injection. People think this is a web app problem, but any app doing this can be vulnerable if the query is built by simple concatenation and not parameterization. So based on that, even with ad-hoc settings, the first execution with a parameterized query might be less than optimal, but the 2nd and subsequent should be ok.