Performance Impact of Procedure Calls without Owner Qualification

Initial posted: June 30, 2007Updated: 9:46PM, July 1, 2007Last 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:

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#:

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.

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.

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.

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.

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.