jwcolby
jwcolby at colbyconsulting.com
Wed Jul 8 08:19:03 CDT 2009
Cool, thanks. I sent that along to the developer. John W. Colby www.ColbyConsulting.com Nancy Lytle wrote: > 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 >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >