You can compare a query plan with a compiled program. Normally, each SQL would receive an individually calculated query plan, which, of course, causes some overhead to compile.

In a stored procedure, or in a parameterized SQL query, the compile will be done once for all later queries. This requires separation of SQL/procedure code from the actual parameter values. Reusing will normally not work if you concatenate parameter values as strings into the SQL, a bad practice anyway. SQL Server recognizes equal parameterized SQL (mostly delivered through the sp_executesql procedure) by string equality, so the @parameterPlaceholders must be the same, while the bound values can differ. Parameterized queries are, in terms of speed and resources, equivalent to stored procedures, except that they usually have no flow control.

The downside may be that the initial compile evaluates parameter values and table statistics, called Parameter Sniffing in SQL Server, and creates a query plan for them, which may be different than an optimal plan for later queries.

Imagine a query which always polls the newest entries in a table: the initial query will start with a min value and scan the whole table, so the same query will still do a full table scan later, even if only supposed to pick the last few of several million rows!

For this reason, one should look after what is happening with queries, even if the precompiled query plans are generally good and save the database a lot of time and CPU work.

You can compare a query plan to a compiled program. Normally, each SQL would receive an individually calculated query plan, which, of course, causes some overhead to compile.

In a stored procedure, or in a parameterized SQL query, the compile will be done once for all later queries. This requires separation of SQL/procedure code from the actual parameter values. Reusing will normally not work if you concatenate parameter values as strings into the SQL, a bad practice anyway. SQL Server recognizes equal parameterized SQL (mostly delivered through the sp_executesql procedure) by string equality, so the @parameterPlaceholders must be the same, while the bound values can differ. Parameterized queries are, in terms of speed and resources, equivalent to stored procedures, except that they usually have no flow control.

The downside may be that the initial compile evaluates parameter values and table statistics, called Parameter Sniffing in SQL Server, and creates a query plan for them, which may be different than an optimal plan for later queries.

Imagine a query which always polls the newest entries in a table: the initial query will start with a min value and scan the whole table, so the same query will still do a full table scan later, even if only supposed to pick the last few of several million rows!

For this reason, one should look after what is happening with queries, even if the precompiled query plans are generally good and save the database a lot of time and CPU work.