[dba-SQLServer] Set memory use without Management Studio

This is what I use.

 

 

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'allow updates', 0;

GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC SP_CONFIGURE 'Max Server Memory', 2048;

RECONFIGURE WITH OVERRIDE;

GO

 

 


Nancy Lytle 
N_Lytle at terpalum.umd.edu











 EMAILING FOR THE GREATER GOOD
Join me


 
> Date: Wed, 8 Jul 2009 08:31:29 -0400
> From: jwcolby at colbyconsulting.com
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] Set memory use without Management Studio
> 
> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>