Scaling Up SharePoint With SQL 2005


From pBoy in Sharepoint Thoughts an article about SQL Server 2005 in Sharepoint 2003:

For those of you who may not have noticed, Microsoft recently updated the capacity planning whitepaper for SharePoint 2003.  I did this work earlier this winter to look at what had been in the capacity planning whitepaper, and update it to reflect a change in databases from SQL 2000 to SQL 2005.  You can see the results of this testing at http://office.microsoft.com/en-us/assistance/HA100993571033.aspx.

So, what are the important things to note about this testing?  First, all of the tests were recreated and run from scratch.  We obviously didn't have the same hardware and network sitting around from three years ago when the original whitepaper was created.  So to ensure that we could accurately measure the differences only as they pertained to a different database, we re-ran all of the tests on our hardware in our network with SQL 2000.

From a platform perspective, we also made one significant change from the original tests – we used 64-bit Windows 2003 to host SQL Server.  All of the SharePoint servers ran on good 'ol 32-bit Windows SP1, with SharePoint 2003 SP2.  But for the SQL Server we wanted to give it a run for the money to see how far we could push it.  There are several factors that account for performance differences between the original tests and our new baseline tests, but overall we were very pleased with the improved capacity we saw with SQL running on 64-bit Windows.

There was another interesting twist to how we did the testing compared to the original paper.  In the original tests Microsoft Application Center Test was used to drive the load on the servers.  Since we're a few years later now we had a great new tool that we decided to use for our test – Visual Studio 2005 Team Test Edition.  It allowed us to create our tests, use data that we captured in SQL about the portal hierarchy and plug that into the tests that it generated, finitely control the user count at different points in time, etc.  It also supports a test harness scenario, where we had one computer acting as the test "controller".  It managed the test through four other servers that we had configured as test agents.  The agents actually generated the traffic to the portal, and the controller was responsible for driving them and capturing the test results.

The results of our baseline tests compared to SQL 2005 showed very favorable gains in almost every circumstance.  Our test bed covered the same farm scenarios as the original capacity planning document, although we did not test a single server farm.  We did test all the way up to a farm with 4 search servers and 14 web front ends however, and got some truly amazing numbers in terms of requests per second (RPS).  In our standard usage mix scenario (which is explained in the link above) we were able to service 1932 RPS, which was an 18% gain over the baseline numbers on SQL 2000.  Supporting that level of traffic required a gigabit ethernet network, along with F5's BigIP load balancer.  We ran tests for a number of different scenarios, including different farm configurations and different page mixes.  More details are available in the link above.

One final point on the results.  If you look through the numbers some appear to be only marginally better, and in one case it's actually 1% worse on SQL 2005.  There are a variety of reasons for why that varies, but a primary one is related to how queries were optimized for SQL 2000 when WSS 2003 and SPS 2003 were being developed.  The query engine in SQL 2005 changed dramatically from SQL 2000.  For reasons of backwards compatibility, it still honors any SQL 2000-specific query hints.  Because of the new query engine it may have newer, better, smarter ways of running the same query, but it will continue to honor those hints that have been built into the product.  There is work going on today to iron out those differences so that when Microsoft Office SharePoint Server 2007 (MOSS) ships you will see equally great performance no matter which version of SQL Server you use.  The net of this is that you should be aware that there are some of those fringe cases out there, but overall the switch to SQL 2005 showed great improvement.

You can find this article here.

Computer developer/consultant from Portugal. You can find me at ricardo.magalhaes@gmail.com

Posted in Sharepoint

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: