Sunday, December 5, 2010

Too much SQL

On my SBS 7 (a.k.a. Windows 2011 Standard), I’ve been testing some SharePoint functionality of late as you can see from recent posts. I do so on a virtualized server to allow me to easily and quickly roll backwards and forwards. As such I try and run these machines a lean as possible because there is only limited RAM.


One of the most common memory hogs is SQL which, by default, has no memory cap. This means that it consumes as much as it can. Using the SQL Management Studio you can go in and set a limit to keep it under control. On SBS 7 (a.k.a. SBS 2011 Standard) I did this for the sharepoint and sbsmonitoring instances which both run on SQL Server 2008 Express R2 (settings it to 256MB). Problem was, when I started getting sluggish performance I found that I had 3 SQL instances running and one was exceeding the memory limit I had set. What the hell?


As you can see from the following screen shot, on the right is task manager displaying the three sqlservr.exe instances (sqlservr.exe), with two at the top of my memory consumption values. Now I know one is sharepoint and the other is sbsmonitoring but what is the third one? And why is running at a value above what I limited it to (at 341,828K)?




To find out what’s going on I add the PID column to task manager so I can see the culprit is process 3188. Then at the command prompt I run tasklist /svc (which is displayed on the left) and discover that task 3188 belongs to mssql$microsoft##ssee! This is the embedded edition of SQL Server 2005. What ….would….that…be…running…. Of course, WSUS!


Much to my amazement Windows Server Update Services on SBS 7 (a.k.a. Windows 2011 Standard) is still using SQL Server 2005 Embedded Edition. That means another version of SQL installed on the server. That means another program that needs to be updated in the future. That means, as you can see, another SQL application which you’ll have to limit memory on.


I can understand perhaps why SQL 2005 Embedded Edition remains, it has unlimited database sizes unlike the SQL 2008 Express R2 version which is also on the machine and limited to 10GB databases. However, even though WSUS in theory could get >10GB does that really warrant not running WSUS on SQL Express R2 and doing away with the Embedded Edition? Surely some sort of warning mechanism could be created is the WSUS databases approach 10GB in some rare circumstance.


This indicates to me that SBS 7 (a.k.a. SBS 2011 Standard) is simply a progressive upgrade to SBS 2008 (i.e. more like SBS 2008 R2). Out of the box it is going to try and consume as much memory as it can for the three SQL instances installed by default (sharepoint, sbsmonitoring and WSUS). Unless these are trimmed they are going to affect performance. Again, after all this time I still can’t understand why there is no wizard to allow you to limited the memory of the SQL instances (maybe I just haven’t found it?).


To me this adds more complexity to the next release of on site SBS. It increases its maintenance due to the different versions of SQL installed (i.e. more patches). It increases its complexity and makes it harder for the average person to optimize. It also means that out of the box it is potentially going to be a memory hog and therefore have its performance degraded and potentially turn people away from it as solution. This has always been one of the downsides of SBS, running so much on one box. In this case, I honestly believe that one application (SQL 2005 Embedded Edition) could have been removed.