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 -- SQL Server 2000

My reaction was "Wow!", when I first saw the test results. I had to triple check the results and repeat the tests several more times in random order to make sure that I didn't make a mistake and that the results weren't a fluke. Good news is that the results are highly reproducible!

The results I'm excited about are from the tests I conducted to compare the performance impact of not owner qualifying stored procedure calls (1) on SQL Server 2005 (9.00.3042) and (2) on SQL Server 2000 (8.00.2187). For information on the test results on SQL Server 2005, see my earlier blog post. The tests on SQL Server 2000 were suggested by SQL Server MVPs Erland Sommarskog and Adam Machanic.

The wow reaction was in response to three observations:

  1. Not owner qualifying stored procedure calls had a much smaller adverse impact in SQL Server 2005 than in SQL Server 2000.
  2. For the test workload, SQL Server 2005 performed significantly better than did SQL Server 2000 in terms of measured transactions per second.
  3. For the test workload, SQL Server 2005 was able to sustain heavy load better than SQL Server 2000.

Let's take a look at the following chart that summarizes the test results:

The test workload was exactly the same as described in the earlier blog post. Briefly, it's a read-only in-memory workload, which was chosen to eliminate the storage subsystem as a factor in the tests and to simplify the test setup. In the above chart, the data for SQL Server 2005 are taken from the same earlier blog post. For all four test scenarios (represented by the four lines in the chart), the user login was not the owner of the stored procedures, and in the case of SQL Server 2005 the default schema of the user was not dbo but the user itself.

Note that all the tests were run on the same piece of hardware with SQL Server 2005 and SQL Server 2000 installed in turn. Both the SQL Server 2005 instance and the SQL Server 2000 instance was given the same amount of memory (fixed at 12GB).

The chart shows a striking difference between SQL Server 2005 and SQL Server 2000 in terms of the performance impact of not owner qualifying procedure calls and in terms of the peak transaction throughput.

The first thing to note is that on SQL Server 2005, the performance degradation caused by procedure calls without owner qualification was less than 5% with 100 or more concurrent users, whereas on SQL Server 2000 the performance degradation with exactly the same test workload was between 15% and 26% with 100 or more concurrent users. So, either SQL Server 2000 was really bad on this issue or Microsoft has done a superb job in crafting SQL Server 2005. Either way, this represents a huge improvement. At the risk of extrapolating too far beyond the test workload, I congratulate the SQL Server dev team for making SQL Server 2005 more resilient to poor user practices, which is absolutely a step forward in the right direction.

Although I didn't go out to specifically compare the performance between SQL Server 2005 and SQL Server 2000 on this particular test workload, I cannot ignore but highlight the rather obvious in the chart--SQL Server 2005 was a clear winner with this workload. With procedure calls owner qualified, SQL Server 2005 showed 15~18% improvement over SQL Server 2000 under heavy load. With procedure calls not owner qualified, SQL Server 2005 improved transaction throughput by a whopping 35~55% under heavy load.

In addition, it's worth noting that when the stored procedure calls were not owner qualified on SQL Server 2000, the transaction throughput actually started to drop as the load level continued to increase beyond 100 concurrent users. With 500 users, the throughput drop was rather significant. Contrast this with the relatively stable transaction throughput on SQL Server 2005 as the number of concurrent users increased over the same range. Kudos to the SQL Server 2005 dev team for this huge improvement.

Again, I have taken a black-box view in designing the tests and reporting the results. What I have not covered, i.e. exactly what changes inside SQL Server are responsible for the reported results, should be interesting, or perhaps more interesting.

Published Thursday, July 05, 2007 1:25 AM by Linchi Shea

Attachment(s): sql2000.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

 

Ross Whitehead said:

As always, very insightful and interesting results.  Thanx Linchi.

Hats off to the SQL 2005 team ;-)

July 5, 2007 7:18 AM
 

Denis Gobo said:

Linchi, thanks for the evidence

I have emailed all the developers this post

Denis

July 5, 2007 8:11 AM
 

Adam Machanic said:

This is great news!  Now if we do screw up and forget it somewhere, at least we know it won't totally bite us later :)

July 5, 2007 8:52 AM
 

Linchi Shea said:

Adam;

I very much wanted to agree with you, but I do want to be cautious and remind people that the results apply 100% to the test workload. Beyond the test workload, your mileage may vary. So, even with SQL Server 2005 it's still a good practice to prefix the proc calls with the owner, and it's better not to forget it :-)

July 5, 2007 10:25 AM
 

Rick Townsend said:

Thanks Linchi, I was looking for a cross-comparison like this.  I did think I should point out, though, that in SQL 2005 you are "schema" qualifying your stored proc calls.  It's a small change, but it can have a big impact if you didn't schema-qualify an object reference and the DBA switches your default schema!

July 8, 2007 4:09 PM
 

Glenn Berry said:

Another good test Linchi. As you say, it is always better to owner or schema qualify than not to.

July 9, 2007 7:17 AM
 

Leo Pasta said:

Thanks Linchi,

 It was very enlighten to see in numbers and graphs what effect qualifying really has on performance.

July 10, 2007 6:16 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
 

Rob D said:

Good info.

Do we know why there is a difference with and without the owner prefix? Does this also hold true for all DML against tables and views? That is, should table names also be prefixed?

April 3, 2008 10:49 AM
 

otreakt said:

Works great. Another good test Linchi.

November 1, 2008 4:21 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