Limit SQL Server memory usage on your workstation, laptop or VM

Here’s a neat tip I learnt over the weekend.

All SQL Server instances are by default set up to use all the memory available on your workstation.

This is ideal when you have SQL Server running on it’s own dedicated server hosting, not so ideal when you have SQL Server installed on your laptop, workstation or even on a SharePoint VM.

Here’s what MSDN says

if SQL Server is one of several server applications running on a single computer, the system administrators may need to control the amount of memory allocated to SQL Server. In these cases, you can use the min server memory and max server memory options to control how much memory SQL Server can use.

In the Server Memory Options page they go on to say:

When you are running multiple instances of the Database Engine, there are three approaches you can use to manage memory

  • Use max server memory to control memory usage.
  • Use min server memory to control memory usage.
  • Do nothing (not recommended).

Which brings us to how we can set the maximum limit. Quite easy. Just connect to each SQL Server instance and set the maximum memory to a more palatable value.

Here’s a visual walk through to limit the maximum memory usage to 512MB for your SharePoint 2010 instance (if you installed it on Windows 7).

  1. Start SQL Server Management Studio (or SSMS Express) and connect to your SQL Server instance (SharePoint in this case): localhost\SharePoint

  1. Right-click on the instance node and select Properties.

  1. Click on the Memory node you’ll notice that the Maximum Server Memory is set to 2,147,483,647MB change it to a lower limit like 256 or 512MB. Click OK and your all set.

If you prefer SQL the same can be done with the following commands.

Enable advanced options:

USE master

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

Set the maximum amount of memory to 512 MB:

USE master

EXEC sp_configure 'max server memory (MB)', 512

RECONFIGURE WITH OVERRIDE

Display the newly set configuration:

USE master

EXEC sp_configure 'max server memory (MB)'

Set ‘show advanced options’ back to default:

USE master

EXEC sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

Limit SQL Server memory usage on your workstation, laptop or VM

SharePoint 2010 Service Manager

With the final release of SharePoint 2010, I finally had time to brush-up and release the Service Manager that I wrote sometime back when the 2010 betas was released.

This utility is basically akin to the SQL Server Service Manager of yore.

If you have SharePoint 2010 installed on your local Windows 7 workstation then you will definitely come across instances where your workstation suddenly freezes up and everything starts moving in slow motion. The most likely culprit is usually one of the SharePoint services. At other times the SharePoint services simply eat away at your RAM.

That’s where the SharePoint 2010 Service Manager comes into play. It lets you start and stop all the SharePoint Services running on your workstation with a single-click.

This release handles both the full version of SQL Server as well as SQL Express Edition (the SharePoint instance). There is also an option to permanently disable the SharePoint services from starting up when Windows starts up, hopefully leading to faster boot times.

Here are a couple of screenshots. Get the setup file from CodePlex at http://sharepointserviceman.codeplex.com/.

I’d like to thank my colleagues at UniqueWorld including Neil, Rehman and Dougie who tested the first version and gave valuable feedback.

Please do report any issues you find to merill at merill.net

SharePoint 2010 Service Manager

Installing Office Web Apps on Windows 7

If you haven’t heard of Office Web Apps before it’s basically Word, Excel, PowerPoint and OneNote running inside a browser. It lets you edit documents right within the browser.

A bit of history
You can be forgiven to think that this is all new. The truth is that a skunkworks team within Microsoft built a version of this (code-name NetDocs) way back in 2000 (even before .NET was launched). It is believed that Steven Sinofsky (now head of Windows 7, previously head of Microsoft Office) managed to get this project killed off. Many years down the line Google snagged a lot of the guys from that original team (including the guy who Ballmer threw a chair at).

Back to the Article
Right. Back to our installation. The key document that you need to follow is this Deploy Office Web Apps guide on TechNet.

Pre-Requisites
– The SharePoint 2010 beta needs to be installed
– The documents must be created with the new Office 2010 beta application in order to be editable (you can view all documents but to edit them in the browser you need to at least open and save the .docx using the latest beta of Word/Excel)

Installation
1. Download Office Web Apps from the download centre (here is the direct link). If you couldn’t figure it out the English version is the one named WcServer_en-us.exe

2. Extract the installation and edit the config.xml in order to allow installing on Windows 7. To do this run the following command from the command prompt.

WcServer_en-us.exe /exctact:C:\WCServer

If you have installed SharePoint 2010 on your Windows 7 you will be familiar with what this does.

Now you need to edit the config.xml in extract location and add the following line to \Files\Setup\config.xml just like you did for the SharePoint 2010 install.

<Setting Id="AllowWindowsClientInstall" Value="True"/>

That is it. Run setup.exe and follow the rest of the guide in the Deploy Office Web Apps article and you will be able to edit Office documents from within the browser in no time.

NB: I did not have to run any of the PowerShell scripts as the features were all activated. I simply created a new Web Application and uploaded some documents and was able to edit in the browser.

SharePoint-2010-Edit-In-Browser

SharePoint-2010-Word-Editor

Installing Office Web Apps on Windows 7

Installing SharePoint Server 2010 on Windows 7

Here’s a quick summary of how I installed the public beta of SharePoint Server 2010.

Minimum Requirements:
– Windows 7 64 bit: What this means is that your workstation needs to be 64 bit and you need to have the 64 bit version of Windows 7 installed.

Setup:
The key document that you need to follow for installing on Windows 7 is this article: Setting Up the Development Environment for SharePoint Server

Key Notes:
– This was installed on Windows 7 Build 7100
– I had UAC turned off
– I already had Visual Studio 2010 Beta before installing SharePoint
– You need to manually download and install the pre-requisites for SharePoint 2010
– You need to extract the setup (using /extract), change an xml file before being able to run setup on Windows 7.

Minor Deviation:
The only part of the guide that I was forced to skip was #5 of ‘Step 3: Install SharePoint’

The reason was that the install for the SQL Server patch kept asking for the other files in the multi-part zip. (Remember to unzip the file even though it has a .exe extension)

Summary:
Excluding the download times it took about 30 minutes to install SharePoint server on my Dell XPS laptop which has 4GB of RAM.

Although there is no guide published yet I was able to install Office Web Apps on my laptop as well, that guide will follow next.

Installing SharePoint Server 2010 on Windows 7