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:
- Not owner qualifying stored procedure calls had a much smaller adverse impact in SQL Server 2005 than in SQL Server 2000.
- For the test workload, SQL Server 2005 performed significantly better than did SQL Server 2000 in terms of measured transactions per second.
- 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.