Compilations/sec high when load testing a simple procedure

4 votes

1 answer

642 views

sql-server sql-server-2016 stored-procedures performance-testing performance-counters

                          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
                        

Asked by Artashes Khachatryan (1533 rep)

Aug 24, 2023, 01:02 PM
Last activity: Aug 25, 2023, 10:38 AM

Compilations/sec high when load testing a simple procedure

Related Questions