asked
size of sqlservr.exe and ssmsee.exe showing greater than the max memory setting
Dear All, I have a issue with the max memory setting of sql server 2005 where sqlservr.exe and ssmsee.exe showing greater than the max memory setting in Task Manager. There is a issue of memory leakage on Live Server. I am trying to test the same on my local PC with Sql Server 2005 express edition installed. I set the maximum server memory to 16MB. Even after that sqlservr.exe showed 40484 KB and ssmsee.exe showed 77464 KB in the task Manager. After that I opened a query window and written a select statement of infinite loop like, Collapse | Copy Code WHILE 1=1 SELECT 10 After executing the above, the size of ssmsee.exe kept growing continuously and reached 364464 KB till I stopped the running query. I am confused here on how do I control this scenario. I have went through a lot of stuff on the net but could not find any thing concrete. Any help and explanation on memory would be much appreciated. Regards, Gopal
The site runs on votes. For each answer below that was helpful, please mark them by clicking on the thumbs up next to those answers. If anyone one answer lead to a solution, mark it by clicking on the check mark next to it.
1: the memory that SSMS uses is not controlled by the settings in SQL Server. Many people avoid installing SSMS on production SQL Servers to avoid it taking a lot of resources and making a problem for SQL Server. I get out of memory errors on my laptop when using SSMS intensively sometimes. It is something that happens and could take a server offline so could be a risk to live systems. 2: SQL Server settings should control the memory used by SQLServr.exe to a reasonable accuracy. I have never seen a server exactly match the value in the settings but under/over by 100MB when the setting is done in 10's of GB isnt so bad to be a concern. 3: Give SQL Server as much RAM as you can but be sure to leave enough for other processes on the server: at least 2GB for the OS and 2GB for other processes such as replication etc...
Fair point, well made. I don't know either as I have never used CLR
I addition to what FatherJack mentions, MAX Memory setting controls the buffer pool, usually the largest part of SQL Memory consumption. Other components will use memory as well all together can total more than MAX memory setting. You may want to look at this link to understand Min/Max memory settings.
http://msdn.microsoft.com/en-us/library/ms180797(v=sql.90).aspx
18 People are following this question.
