Performance Impact of Procedure Calls without Owner Qualification

Initial posted: June 30, 2007
Updated: 9:46PM, July 1, 2007
Last updted: 5:14pm, July 2, 2007 (The main text updated to include new test results and corrections of copy/paste errors)

It has been widely recommended that a stored procedure should be owner qualified when it is called. When it comes stored procedure best practices, this gotta be the most recommended practice. Do a Google search, and you'll find this is recommended in books, conference presentations, TechNet webcats, blogs, newgroups, websites, and countless other places. Microsoft KB article Description of SQL Server blocking caused by compile locks gives the fundamental reason why not following the recommendation can cause performance problems. The KB article further suggests that the recommendation applies to all the editions of SQL Server 2005 as well.

I'm not trying to suggest that there is reason not to follow the recommendation, but am curious as to what kind of performance impact it actually has in a controlled test environment at various levels of load from light to very heavy.

So I pulled out my TPC-C kit and rigged it to call the two read-only transactions (out of the five transactions that are used by a standard TPC-C setup): Order Status and Stock Level. These two transactions were called  in a 50/50 transaction mix on a test database that's sized small enough to fit entirely into the physical memory after sufficient ramp up. The SQL2005 SP2 instance was given 12GB of memory and the  allocated space of the test database was less than 9GB. Each of the two transactions was wrapped in a stored procedure: dbo.spOrderStatus and dbo.spStockLevel, respectively.

I ran the following six types of tests, each at multiple load levels:

• Owner Qualified with User sa: The two stored procedures were called with dbo owner prefix and the user login was sa.
• Not Owner Qualified with User sa: The two stored procedures were called without dbo owner prefix and the user login was sa.
• Owner Qualified with User tpcc (default schema=dbo): The two stored procedures were called with dbo owner prefix and the user login was tpcc, and the user tpcc was granted EXECUTE permission on both stored procedures. The default schema of user tpcc is dbo.
• Not Owner Qualified with User tpcc (default schmea=dbo): The two stored procedures were called without dbo owner prefix and the user login was tpcc, and the user tpcc was granted EXECUTE permission on both stored procedures. The default schema of user tpcc is dbo.
• Owner Qualified with User tpcc (default schema=tpcc): The two stored procedures were called with dbo owner prefix and the user login was tpcc, and the user tpcc was granted EXECUTE permission on both stored procedures. The default schema of user tpcc is tpcc.
• Not Owner Qualified with User tpcc (default schmea=tpcc): The two stored procedures were called without dbo owner prefix and the user login was tpcc, and the user tpcc was granted EXECUTE permission on both stored procedures. The default schema of user tpcc is tpcc.

For example, in the 'owner qualified' cases, the procedures were called as follows:

exec dbo.spStockLevel @w_id=69,@d_id=5,@threshold=13
exec dbo.spOrderStatus @w_id=90,@d_id=8,@c_id=0,@c_last='ANTIBARCALLY '

In the 'not owner qualified' cases, the procedures were called as follows, for example:

exec spStockLevel @w_id=69,@d_id=5,@threshold=13
exec spOrderStatus @w_id=90,@d_id=8,@c_id=0,@c_last='ANTIBARCALLY '

The load level was controlled by the number of concurrent users that were issuing the stored procedure calls. The numbers of users tested were 5, 20, 100, 200, 300, and 500. For each load level, the test ran for 200 seconds with the number of transactions recorded only for the second half of the test duration. Note that with 100 users or more, the four processors (four in total) were completely saturated with the % Processor Time counter flattened at 100%, and with 500 users the average processor queue length was about 30.

The following chart summarizes the test results:

Two observations can be made from the chart:

• When the user was sa, it didn't make a difference whether a procedure call was owner qualified or not.
• However, when the user was tpcc which was not an owner of the stored procedures, there was a small but consistent and unmistaken difference in transaction throughput between owner-qualified procedure calls and procedure calls that were not qualified with owner. The difference was about 5~6% regardless whether the default schema of the user tpcc is dbo or tpcc.

So far so good. But not everything was as expected. Even with 500 concurrent users, I didn't see any SP:CacheMiss event when the two stored procedures were called by user tpcc without being qualified with dbo. Nor did I see any LCK_M_X wait type in sysprocesses. Checking sys.dm_os_wait_stats with track_waitstats_2005 didn't find any wait on LCK_M_X either.

I don't know whether my not seeing SP:CacheMiss event or the contention on compile locks was an artifact of the test design or due to some fundamental improvement in SQL Server 2005 SP2. The test results reported here do support the recommendation that a stored procedure be owner qualified when it is called. After all, a 5~6% performance degradation can be significant in some applications.

I'd like to reiterate that these are just a limited number of data points, and your results may vary. But I very much like to hear what you may have seen in your test or production environments with respect to this best practice recommendation.

July 1, 2007 Update:

To see whether it makes a difference to call a stored procedure as a language event as opposed to RPC, I tried the following .NET 2.0 test code in C#:

/// use junkDB
/// go
/// create table dbo.junk(i int);
/// go
/// create proc dbo.spJunk as select * from dbo.junk;
/// go

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

class Junk {

    public static void Main(string[] args) {
    
        // There must be exactly two arguments on the command line
        if (args.Length != 3) {
            Console.WriteLine("Usage:\n  junk.exe <SQL connect string> <proc name> {RPC|TEXT}");
            Environment.Exit(-1);
        }
        
        // Open a connection to the SQL instance
        SqlConnection cxn = new SqlConnection(args[0]);
        cxn.Open();
        	
        int i = 0;
        while (true) {
            i++;
            if (i % 10 == 0)
            {
               Console.WriteLine("Called " + args[1] + " 10 times.");
            }

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cxn;

            if (String.Equals("RPC", args[2]))
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }
            cmd.CommandText = args[1];
            
            SqlDataReader sqlReader = cmd.ExecuteReader();

            while (sqlReader.Read())
            {
            }
            sqlReader.Close();

            Thread.Sleep(2000);
        }
    }

}

I executed the compiled executable junk.exe in a number of different ways with SQL Profiler turned on, and the following is a summary of the test results with respect to SP:CacheMiss.

Case 1: RPC without owner qualification

Events observed with SQL Profiler:

EventClass        Text Data
RPC:Starting      exec spJunk
SP:CacheHit
SP:Starting       exec spJunk
SP:Completed      exec spJunk
RPC:Completed     exec spJunk

Case 2: RPC with owner qualification

Events observed with SQL Profiler:

EventClass        Text Data
RPC:Starting      exec dbo.spJunk
SP:CacheHit
SP:Starting       exec dbo.spJunk
SP:Completed      exec dbo.spJunk
RPC:Completed     exec dbo.spJunk

Case 3: Language event without owner qualification

Events observed with SQL Profiler:

EventClass            Text Data
SP:CacheMiss          spJunk
SQL:BatchStarting     spJunk
SP:CacheHit
SP:Starting           spJunk
SP:Completed          spJunk
SQL:BatchCompleted    spJunk

Case 4: Language event with owner qualification

Events observed with SQL Profiler:

EventClass            Text Data
SP:CacheMiss          dbo.spJunk
SQL:BatchStarting     dbo.spJunk
SP:CacheHit
SP:Starting           dbo.spJunk
SP:Completed          dbo.spJunk
SQL:BatchCompleted    dbo.spJunk

Observations:

• If the stored procedure is called as RPC (i.e. SqlCommand.CommandType = CommandType.StoredProcedure), no SP:CacheMiss is seen in the trace regardless whether the procedure call is owner qualified or not.
• If the stored procedure is called as a language event (i.e. SqlCommand.CommandType=CommandType.Text), SP:CacheMiss is always seen in the trace regardless whether the procedure call is owner qualified or not.

In summary, the observed behavior is not consistent with the description in the KB article.

July 2, 2007 Update:

To see whether it would make a difference if the default schema of the user tpcc was set to tpcc instead of dbo, I re-executed the compiled executable junk.exe in a number of different ways with SQL Profiler turned on, and the following is a summary of the test results with respect to SP:CacheMiss. Note that in the following four cases, the default schema of the user tpcc was tpcc.

Case 1: RPC without owner qualification

Events observed with SQL Profiler:

EventClass        Text Data
RPC:Starting      exec spJunk
SP:CacheHit
SP:Starting       exec spJunk
SP:Completed      exec spJunk
RPC:Completed     exec spJunk

Case 2: RPC with owner qualification

Events observed with SQL Profiler:

EventClass        Text Data
RPC:Starting      exec dbo.spJunk
SP:CacheHit
SP:Starting       exec dbo.spJunk
SP:Completed      exec dbo.spJunk
RPC:Completed     exec dbo.spJunk

Case 3: Language event without owner qualification

Events observed with SQL Profiler:

EventClass            Text Data
SP:CacheMiss          spJunk
SQL:BatchStarting     spJunk
SP:CacheHit
SP:Starting           spJunk
SP:Complted           spJunk
SQL:BatchCompleted    spJunk

Case 4: Language event with owner qualification

Events observed with SQL Profiler:

EventClass            Text Data
SP:CacheMiss          dbo.spJunk
SQL:BatchStarting     dbo.spJunk
SP:CacheHit
SP:Starting           dbo.spJunk
SP:Completed          dbo.spJunk
SQL:BatchCompleted    dbo.spJunk

Observations:

• If the stored procedure is called as RPC (i.e. SqlCommand.CommandType = CommandType.StoredProcedure), no SP:CacheMiss is seen in the trace regardless whether the procedure call is owner qualified or not.
• If the stored procedure is called as a language event (i.e. SqlCommand.CommandType=CommandType.Text), SP:CacheMiss is always seen in the trace regardless whether the procedure call is owner qualified or not.

In summary, even with the default schema of the user tpcc set to tpcc, the observed behavior is not consistent with the description in the KB article.