[dba-SQLServer] Set memory use without Management Studio

jwcolby jwcolby at colbyconsulting.com
Wed Jul 8 07:31:29 CDT 2009


I have a Windows Media Center (WMC) PC feeding movies to my TV.  That PC uses an add-in called 
MyMovies which allows me to access movies stored on a server up in my office.  MyMovies uses a SQL 
Server Express database to hold all of the data used to feed the MyMovies application running on the 
WMC PC.  Because it seemed logical to do so, I set up the MyMovies database on my Windows Home 
Server (WHS) PC up in my office, and all of the movies are physically stored there as well.

Pretty much since day one, MyMovies has had really long delays getting at the movie information, 
delays which show themselves as long pauses whenever you do ANYTHING in the MyMovies submenu inside 
of Windows Media Center.  All other parts of WMC are very speedy, so it was obvious that MyMovies 
was the culprit.

Last night I decided to go poking around the database.  For some reason I was able to see but unable 
to access the MyMovies database from Management Studio on my other machines, so I installed 
Management Studio Express on my WHS machine and I can now see and manipulate the MyMovies database.

The first thing I did was look at the memory assigned to the SQL Server instance and, sure enough, 
as is SQL Server's wont, SQL Server was assigned a minimum and maximum amount and the maximum amount 
was some ludicrously high terabyte figure.  IOW SQL Server was told it could have ALL of the memory 
of the machine it is running on.  This happens in the default installation AFAICT.

So, I adjusted that figure to give SQL Server 1/2 of the machine min and max and voila, the speed 
issues with MyMovies appear to be solved.

I have seen this in my business dealings with SQL Server, if SQL Server is assigned all the PCs 
memory it takes it, even if not needed, and then it can take a looooooooonnnnnnngggggg time giving 
some back when requested.

So my question is, is there a way to manipulate this setting without going through Management 
Studio?  I want to email the developer and suggest that he look at this issue and perhaps set the 
default values to something specific and sensible, but it would be nice to have a suggestion about 
how his program can access this parameter directly.

TIA for your help,

-- 
John W. Colby
www.ColbyConsulting.com



More information about the dba-SQLServer mailing list