THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

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

cmd>junk.exe "server=NYSQL1;database=junkDB;user=tpcc;pwd=tpcc;" "spJunk" RPC

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

cmd>junk.exe "server=NYSQL1;database=junkDB;user=tpcc;pwd=tpcc;" "dbo.spJunk" RPC

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

cmd>junk.exe "server=NYSQL1;database=junkDB;user=tpcc;pwd=tpcc;" "spJunk" TEXT

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

cmd>junk.exe "server=NYSQL1;database=junkDB;user=tpcc;pwd=tpcc;" "dbo.spJunk" TEXT

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

cmd>junk.exe "server=NYSQL1;database=junkDB;user=tpcc;pwd=tpcc;" "spJunk" RPC

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

cmd>junk.exe "server=NYSQL1;database=junkDB;user=tpcc;pwd=tpcc;" "dbo.spJunk" RPC

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

cmd>junk.exe "server=NYSQL1;database=junkDB;user=tpcc;pwd=tpcc;" "spJunk" TEXT

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

cmd>junk.exe "server=NYSQL1;database=junkDB;user=tpcc;pwd=tpcc;" "dbo.spJunk" TEXT

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.

 

Published Saturday, June 30, 2007 11:14 AM by Linchi Shea

Attachment(s): owner.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter W said:

Did the tpcc user have it's default schema set to dbo or tpcc?

ALTER USER [tpcc] WITH DEFAULT_SCHEMA=[tpcc]

ALTER USER [tpcc] WITH DEFAULT_SCHEMA=[dbo]

July 2, 2007 4:04 AM
 

Linchi Shea said:

Peter:

I reran all the tests with the default schema of the user tpcc set to tpcc, and the results came out similar to those when the default schema of the user tpcc was set to dbo.

See more info in the main blog text above.

July 2, 2007 4:25 PM
 

Steve Morris said:

I've seen this in test (& prod) on SQL 2000 system, but we were calling the stored proc in question about 40000 times per minute ! In sysprocesses you could see a lot of blocking with a wait type of compile lock (perhaps not the exact message, this is from memory of events 11/2 years ago). Client language was Java not sure what type of call was made, here specifying the sp owner (dbo) made a significant improvement. On the test system we were able to go from 7000 simulated players to c 10000 as a result of the change.

July 3, 2007 2:53 AM
 

Linchi Shea said:

Steve;

If you looked at the numbers in the chart, each of the two stored procedures was called more than 2000 times a second. Two stored procedure together, that's more than 4000 times a second or 240,000 times a minutes. Anyway, a 5~6% consistent performance degradation for the tested workloads is still something one can't ignore.

July 3, 2007 8:00 AM
 

Adam Machanic said:

Based on what I've heard here and elsewhere, I'm thinking that this was much worse in 2000 than 2005.  Linchi, do you still have a 2000 instance around to run a quick test on?

July 3, 2007 9:07 AM
 

Linchi Shea said:

Adam;

It's an excellent idea to run a comparison with SQL2000. I'll install a SQL2000 instance on the same machine, give it the same configurations (e.g. memory, etc), and re-run the tests.

Note that I repeated these tests many times, and the results were rather consistent. But it bothers me that I can't find a way to directly link the missing owner qualification as the root cause for the throughput drop, though indirectly that's the only parameter that's changed. I had expected to see some serious compile lock contention for instance.

Linchi

July 3, 2007 10:25 AM
 

Chuck Boyce said:

good stuff!

July 3, 2007 10:28 AM
 

Jared Ko said:

Very good stuff! I've tried to duplicate the same thing before and haven't been this successful. I'm glad to see some actual metrics around this.

July 3, 2007 11:59 AM
 

Linchi Shea said:

My reaction was "Wow!", when I first saw the test results. I had to triple check the results and repeat

July 5, 2007 12:30 AM
 

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM
 

Bill K said:

SQL Server Security, Performance and Tuning - (SSQA) said:

"If you ask an experience DBA about the reasons for performance loss then the answer should be straight"

OK, but for an unexperienced DBA such as myself, what's the reason?

July 25, 2008 6:38 PM
 

davetiyeci said:

düğün davetiyesi  ve davetiye sözleri

November 18, 2009 8:13 AM
 

davetiyeci said:

düğün davetiyesi ve davetiye sözleri

November 19, 2009 3:35 AM
 

Allenw said:

This testing should be conducted in a database with more than 10 schemas and 10,000 procedures.

March 2, 2012 12:39 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement