A compilation is not the same as an optimization.

Compilation itself is cheap. It happens any time an exact match for the submitted text is not found in the plan cache. Optimization is much more expensive.

A compilation may or may not require the query optimizer. Simple commands do not. You'll note there are separate counters for query optimizations. A compilation does not necessarily lead to an optimization.

Most commands that don't require the query optimizer are not cached, because it's just not worth it. These are generally referred to as 'zero-cost plans' and may not be queries at all.

For example, SELECT @@SPID is a zero-cost plan as is SET STATISTICS XML ON. Only the latter is cached as an exception because it is so common, most commands like that are not. They're simply compiled as needed.

The SQL stress tool submits:

EXECUTE dbo.test_sp;

All this does is call a stored procedure. It is considered zero-cost and not cached. Of course, the stored procedure itself is cached but the text that invokes it is not.

This is what you're seeing being 'compiled' on each iteration, the EXECUTE call itself. The procedure is cached and reused as you would expect.

Don't worry so much about SQL compilations per second. Pay attention to the number of optimizations instead. Invoking the query optimizer, even for a trivial plan, is far more expensive than a compilation.

If you really want your example to show no compilations, make the batch submitted by the tool more complex so it is considered worth caching and reusing by the server. Even this will do:

DECLARE @s smallint = (SELECT @@SPID);
EXECUTE dbo.test_sp;

Related reading: SQL Compilations/sec is not what you think it is by Fabiano Amorim.

* There is a (small) cost to invoking stored procedures this way. It is more efficient to call procedures directly as an RPC, but the tool you are using doesn't expose that facility.

A compilation is not the same as an optimization.

Compilation itself is cheap. It happens any time a match is not found in the plan cache. Optimization is much more expensive.

A compilation may or may not require the query optimizer. Simple commands do not. You'll note there are separate counters for query optimizations. A compilation does not necessarily lead to an optimization.

Most commands that don't require the query optimizer are not cached, because it's just not worth it. These are generally referred to as 'zero-cost plans' and may not be queries at all.

For example, SELECT @@SPID is a zero-cost plan as is SET STATISTICS XML ON. Only the latter is cached as an exception because it is so common; most commands like that are not. They're simply compiled as needed.

The SQL stress tool submits:

EXECUTE dbo.test_sp;

All this does is call a stored procedure. It is ad hoc SQL, considered zero-cost, and not cached. Of course, the stored procedure body is cached but the text that invokes it is not.

This is what you're seeing being 'compiled' on each iteration—the EXECUTE call. The compiled plan for the stored procedure is cached and reused as you would expect.

Don't worry so much about SQL compilations per second. Pay attention to the number of optimizations instead. Invoking the query optimizer is far more expensive than a simple compilation.

If you really want your example to show no compilations, make the batch submitted by the tool more complex so it is considered worth caching and reusing by the server. Even this will do:

DECLARE @s smallint = (SELECT @@SPID);
EXECUTE dbo.test_sp;

This won't reduce CPU or increase the throughput noticeably because compiling the EXECUTE text is a tiny cost.

Related reading: SQL Compilations/sec is not what you think it is by Fabiano Amorim.

* There is a (small) cost to invoking stored procedures as a language event. It is more efficient to call procedures directly as an RPC, but the tool you are using doesn't expose that facility.

You can invoke a stored procedure via an RPC instead of a language event from most programming languages, typically by setting the command type to 'stored procedure'.

The following PowerShell script example was provided by Dan Guzman:

$con = New-Object System.Data.SqlClient.SqlConnection(
  "Data Source=YourServer;Initial Catalog=YourDB;Integrated Security=SSPI");
$cmd = New-Object System.Data.SqlClient.SqlCommand("dbo.test_sp", $con);
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure;
$con.Open();
while($true) {[void]$cmd.ExecuteNonQuery()}

A compilation is not the same as an optimization.

Compilation itself is cheap. It happens any time an exact match for the submitted text is not found in the plan cache. Optimization is much more expensive.

A compilation may or may not require the query optimizer. Simple commands do not. You'll note there are separate counters for query optimizations. A compilation does not necessarily lead to an optimization.

Most commands that don't require the query optimizer are not cached, because it's just not worth it. These are generally referred to as 'zero-cost plans' and may not be queries at all.

For example, SELECT @@SPID is a zero-cost plan as is SET STATISTICS XML ON. Only the latter is cached as an exception because it is so common, most commands like that are not. They're simply compiled as needed.

The SQL stress tool submits:

EXECUTE dbo.test_sp;

All this does is call a stored procedure. It is considered zero-cost and not cached. Of course, the stored procedure itself is cached but the text that invokes it is not.

This is what you're seeing being 'compiled' on each iteration, the EXECUTE call itself. The procedure is cached and reused as you would expect.

Don't worry so much about SQL compilations per second. Pay attention to the number of optimizations instead. Invoking the query optimizer, even for a trivial plan, is far more expensive than a compilation.

If you really want your example to show no compilations, make the batch submitted by the tool more complex so it is considered worth caching and reusing by the server. Even this will do:

DECLARE @s smallint = (SELECT @@SPID);
EXECUTE dbo.test_sp;

Related reading: SQL Compilations/sec is not what you think it is by Fabiano Amorim.

A compilation is not the same as an optimization.

Compilation itself is cheap. It happens any time an exact match for the submitted text is not found in the plan cache. Optimization is much more expensive.

A compilation may or may not require the query optimizer. Simple commands do not. You'll note there are separate counters for query optimizations. A compilation does not necessarily lead to an optimization.

Most commands that don't require the query optimizer are not cached, because it's just not worth it. These are generally referred to as 'zero-cost plans' and may not be queries at all.

For example, SELECT @@SPID is a zero-cost plan as is SET STATISTICS XML ON. Only the latter is cached as an exception because it is so common, most commands like that are not. They're simply compiled as needed.

The SQL stress tool submits:

EXECUTE dbo.test_sp;

All this does is call a stored procedure. It is considered zero-cost and not cached. Of course, the stored procedure itself is cached but the text that invokes it is not.

This is what you're seeing being 'compiled' on each iteration, the EXECUTE call itself. The procedure is cached and reused as you would expect.

Don't worry so much about SQL compilations per second. Pay attention to the number of optimizations instead. Invoking the query optimizer, even for a trivial plan, is far more expensive than a compilation.

If you really want your example to show no compilations, make the batch submitted by the tool more complex so it is considered worth caching and reusing by the server. Even this will do:

DECLARE @s smallint = (SELECT @@SPID);
EXECUTE dbo.test_sp;

Related reading: SQL Compilations/sec is not what you think it is by Fabiano Amorim.

* There is a (small) cost to invoking stored procedures this way. It is more efficient to call procedures directly as an RPC, but the tool you are using doesn't expose that facility.