asked
sqlservr.exe memory goes on increasing and never comes down
hi all, we use sql server 2005 version 9.0.4035 on windows 2003 r2and both are of 64 bit computer. server is of 16 GB RAM, with 8 concurrent processors. sqlservr.exe goes on increasing the memory.disaster recovery plan is clustering. what could be the problem , database size is of 100 GB ,having 600 users working over it. avalilable Mbytes become 545 MB, and we get mails from the user that applicaton is slow, then we change the node reset the iis, then application is fine.. please help me
SQL Server *lives* in RAM. I really can't add to what has been said with respect to the memory settings. If the data is in memory, access to it is much quicker - that's good. Memory usage is a given - you aren't running a file and print server. I would say that you may need to analyze your implementation and see if concurrent users are causing blocking. When you change nodes all the connections are terminated and the first users in to the system have free rein. Another possible fix may be to setting Max DOP to 4 (or 6 or 8 or...) to allow more concurrency - make sure you understand what is normal operations before going too drastic. Slow is a vague user answer - find out what is slow, what "normal" is and, at a minimum, check the Activity Monitor.
SQL Server 2005 is designed to use all the memory on the server - what you are seeing is normal behaviour. If the SQL Server installation is on the same server as other application processes, then you can limit the amount of memory available to SQL Server - look for "sp_configure". EDIT: To be more precise, Documentation for sp_configure: [
http://msdn.microsoft.com/en-us/library/ms188787(SQL.90).aspx][1]. The parameter you'll be looking for is "max server memory (MB)". This will probably be set to "2147483647". If you really want to restrict SQL Server, then the command to run is: sp_configure 'max server memory (MB)', < > You may then need to run: reconfigure with override to apply the settings. [1]:
http://msdn.microsoft.com/en-us/library/ms188787(SQL.90).aspx
thanks man
Gentle nudge first... cajole, encourage, enlighten, educate the users, then prod, steer, force and eventually brainwashing.
Maybe an annoying pop-up that shows every 7-10 visits until a certain karma level is reached reminding users of accepting answers and voting...?
@ramesh, if Thomas's answer has helped, please accept it as the answer by clicking the tick icon next to it
This is what I was on about with the 'mods accepting answers' question...
It would be nice if the OP also clicked the "thumbs up"... (he hasn't yet - I've checked my karma history)
Min Server Memory (MB) and Max Server Memory (MB) control the allowable size of SQL Server’s buffer pool. The settings do not control all of SQL Server’s memory usage, just the buffer pool During the SQL Server Startup SQL Server will only Consume the memory that you configured in the Min Server Memory (MB) option, as the usage increased beyond the Min Server Memory (MB), SQL Server never release any memory beyond that limit until there is any desperate requirements from the Windows once there is any requirements then SQL Server will release the required memory to the OS. We can limit the consumption Max Server Memory by configuring appropriate value for Max Server Memory (MB). By Observing the buffer pools maximum usage or By finding the maximum non buffer pool usage you can configure the exact values for Max Server Memory (MB) By using performance monitor tools observe the counter MSSQL$:Memory Manager\Total Server Memory (KB) . Observe this counter for a while, if there is any memory requirements from any other process causes the value to decrease then you can set Max Server Memory to the lowest value that was observed for Total Server Memory (KB). Another option is to understand the non SQL Server buffer pools, you should always reserve - 2GB to Windows - Find the no worker threads (select max_workers_count from sys.dm_os_sys_info) and Multiply by the appropriate value from (0.5MB for 32 bit processor,2MB for 64 bit processor and 4MB for itanium processor) - 1 GB for Multi-Page Allocations(MPA), linked servers, and other consumers of memory outside the buffer pool. - Finally 1-3GB for the other applications if any (basically you will not be hosting any other application on the database production server) Ex. For example, on a server with eight CPU cores and 16GB of RAM running SQL Server 2008 x64 and a third-party backup utility, you would allow the following: - 2GB for Windows - 1GB for worker threads (576 × 2MB rounded down) - 1GB for MPAs - 1GB for the backup The total is 5GB, and you would configure Max Server Memory to 11GB. Once you configured the appropriate value for Max Server Memory by using sp_configure stored procedure SQL Server will never acquires more memory than the level specified in max server memory.
