Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Naveed Amir
Starting Member
8 Posts |
 Posted - 2013-04-03 : 01:25:01
|
| Dear Team, Since we are using SQL Server 2008 R2 EE 64bit Sp2. We are facing memory issue, As we have allocated 21GB MAX memory to SQL Server out of total Physical memory 24GB. Problem: (As we know that SQL Server 2008 R2 caches all memory what we have allocated and does not release untill n unless we do not restart its services or restart the Server)When there is no load on SQL server, SQL server shows the total memory which we have allocated to SQL in task manager, i.e. 21GB but when we see the process sqlservr.exe takes only 200MB. How we can monitor the SQL server that we would know how actually SQL server memory required. Regards, Naveed Amir. --------------------------------------------------------------------------------Naveed AmirIT Specialist |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-04-03 : 11:30:47
|
| That setting is too high. I would knock it down to 19GB. You can use Perfmon, and look at SQL Server Memory Manager->Target Server Memory and Total Server Memory. Target is what SQL will try to use, and Total is what it is actually using.You may be misunderstanding how it works, SQL doesn't immediately use 21GB at startup, it gradually grows it's memory usag up to the max setting as it needs the memory.-Chad |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-04-03 : 14:47:42
|
| Also, do an assessment of other programs which may require memory , such as Anti Virus and monitoring siftware or any other agents. That way you can ensure you allow those services to run without starving the SQL Server. You can use DMVs , such as sys.dm_os_memory_clerks to see how SQL Server allocates the memoryJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Naveed Amir
Starting Member
8 Posts |
Posted - 2013-04-04 : 09:07:36
|
| HI Chad, As you said SQL server gradually grows its memory usage up to MAX memory setting as it needs the memory. Thats what i am concering when SQL server holds all memory up to MAX how can we find that is SQL server actually utilizing this memory or just it caches the MAX memory.Naveed AmirIT Specialist |
|
|
|
Naveed Amir
Starting Member
8 Posts |
Posted - 2013-04-04 : 09:12:06
|
| If it actually do not Using the MAX memory then its should release the memory from MAX memory... I just need to know that how can we find out SQL server memory consumption at time of when i run monthend...Naveed AmirIT Specialist |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-04 : 11:52:21
|
| As said earlier, check target server memory vs total server memory |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-04-04 : 12:53:58
|
| If it has it, it is using it. If you want to know what it is being used for, there are various DMVs you can query.-Chad |
|
|
|
Naveed Amir
Starting Member
8 Posts |
Posted - 2013-04-05 : 00:49:04
|
| thanks chad, Naveed AmirIT Specialist |
|
|
|
|
|
|
|
|
SQL Server 2008 R2 Memory Issue!