Memory configuration on SQL 2008

I have tried running the default configuration on a machine running nothing but SQL Server with 8GB server memory and when the machine is running a large query then SQL uses all of the available memory which is pretty much all of it leaving the OS to cope with what it currently has. If this happens at the moment you decide you want to connect to the machine by remote desktop then you will find the performance is awful for the interactive user. Doing anything as simple as moving a file around will be painful.

On the server I am using (dev server), reporting services is also installed locally so the situation is compounded so I allocate 2GB to the non-sql tasks using the following command.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'max server memory (MB)', 6144
RECONFIGURE WITH OVERRIDE
GO

SQL will not immediately grab 6GB here but if it needs lots of memory then this is the max that it can have.

 

 

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in SQL Stuff, Uncategorized. Bookmark the permalink.

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