We have SQLSERVER 2008R2 with 32Bit AWE not enabled RAM 10GB pagefile 10GB(not assigne max and min memory) 1. Buffer usage 6.4GB 2. Available MB 695MB 3. Processes usage memory 7gb 4. Virtual address space 66MB 5. singale page 294664KB 6. multipage 34232KB all this get the info from below queries select * from sys.dm_os_sys_memory select COUNT(*) from sys.dm_os_buffer_descriptors select * from sys.dm_os_process_memory select * from sys.dm_os_memory_clerks select SUM(single_pages_kb),SUM(multi_pages_kb) from sys.dm_os_memory_clerks select * from sys.dm_exec_query_memory_grants select * from sys.dm_exec_requests page life expectency 17,769 i also checked the windows task bar- memory usage is 8.83 is it sqlserver consuming the space or OS..? Experts Please help me on this
This sounds like you're seeing a lot of memory being used, and you're not sure why. SQL Server is a memory-grabber. This is good - it's all used to help performance of SQL Server by caching commonly-used data from your database. On the other hand, if the memory settings for SQL Server aren't managed properly, then it can cause problems with anything else on that server. A few things to read:
http://technet.microsoft.com/en-us/library/ms178067(v=sql.105).aspx - configuring max/min memory settings in SQL Server Brent Ozar's blog on memory in SQL Server:
http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/ various blog posts from the SQLSkills team on memory in SQL Server:
http://www.sqlskills.com/blogs/paul/category/memory/ The theory is that you should set a maximum amount of memory that your SQL Server instance is allowed to use - generally this would be at least 2GB less than the total RAM in the server, in order to allow some room for everything else! If the server is multi-purpose, then you might need to only allow SQL access to, say, half the RAM. It all depends on what else is running.
What performance issue do you perceive? SQL Server lives in RAM and will use it to provide better performance to you rather than loading data from disk constantly. On a dedicated server you want to give SQL Server as much of the RAM as you can, short of starving the O/S. That is what MAXMEM is there for. Your own queries told you what was consuming memory - SQL Server components.