Code line limitation in Stored Procedures

Question

Hi,

I have a more generic Question about Stored Procedures.
Is there a limitation of Code Lines in a SP, to create a optimized Execution Plan?

Lets say in case that a SP has more than 1000 Code lines (excluding Comments and other non relevant lines) is it possible that the optimizer can not run proper and create a not or less optimized execution plan?

Kind regards

Answer 1

The largest proc I heard about had 70,000 lines of code - and that was almost 20 years ago.

The DML statements in the proc are optimized per statement. I.e., possible optimization restrictions isn't dependent on proc size, but rather on each statement complexity.

Answer 2

See Maximum capacity specifications for SQL Server => Bytes in source text of a stored procedure = 250 MB

Answer 3

Hi @Jens W ，

Welcome to the Microsoft TSQL Q&A Forum!
As TiborKaraszi said, the possible optimization limits do not depend on the size of pro, but on the pro statement.Maybe you should understand the working principle of the query optimizer in SQL Server.

Please refer to:

The SQL Server Query Optimizer

Summary
How the Query Optimizer Works
At the core of the SQL Server Database Engine are two major components: the Storage Engine and the Query Processor, also called the Relational Engine. The Storage Engine is responsible for reading data between the disk and memory in a manner that optimizes concurrency while maintaining data integrity. The Query Processor, as the name suggests, accepts all queries submitted to SQL Server, devises a plan for their optimal execution, and then executes the plan and delivers the required results.

Queries are submitted to SQL Server using the SQL language (or T-SQL, the Microsoft SQL Server extension to SQL). Since SQL is a high-level declarative language, it only defines what data to get from the database, not the steps required to retrieve that data, or any of the algorithms for processing the request. Thus, for each query it receives, the first job of the query processor is to devise a plan, as quickly as possible, which describes the best possible way to execute said query (or, at the very least, an efficient way). Its second job is to execute the query according to that plan.

Each of these tasks is delegated to a separate component within the query processor; the Query Optimizer devises the plan and then passes it along to the Execution Engine, which will actually execute the plan and get the results from the database.

In order to arrive at what it believes to be the best plan for executing a query, the Query Processor performs a number of different steps;

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.