Compilations/sec high when load testing a simple procedure

Question

I am trying to load test a simple insert stored procedure:

CREATE TABLE _test(ID BIGINT)
GO
CREATE OR ALTER PROCEDURE dbo.test_sp
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN
        INSERT INTO _test
        SELECT CAST(RAND() * 10000 AS BIGINT)
    END
END

When I execute this stored procedure with the SQL Stress tool, I get SQL Compilations/sec equal to Batch Requests/sec. Interestingly, SQL Re-Compilations/sec is zero.

Both optimize for ad-hoc workloads and forced parameterization are enabled. The picture is the same even if I change my procedure to a simple SELECT 1.

I am using Microsoft SQL Server 2016 (SP3) (KB5003279).

A profiler trace shows the tool sends a simple EXEC dbo.test_sp

Answer 1

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()}