opc.three (5/16/2013) chandan_jha18 (5/16/2013) opc.three (5/16/2013)
As you posted, you have already fixed SQL Server and given it a range between which it an fly up or down.
I would suggest to collect some 'Available Memory' through Perfmon or DMVs to see if the remaining memory on the OS goes too low(depending upon threshold of diagnostic manager). There could be some process other than SQL causing this and should be looked upon. Ideally the operating system should have 2-3 GBs free all the time. Going too low may crash it
Where you getting this information?
Sir, Apologize if there is a mistake in the comment above. As far as I know, we restrict memory for SQL Server so that if some other process wants to get some memory, the sql server does not have to release it from its buffer which is not considered ideal.
Also, through perfmon counters, we can track how much physical memory is available on OS and if it goes too low, it indicates external memory pressure and I have read so many times even in this forum that the OS should have some free memory for its own work.
Kindly let me know which assumption is wrong here.
It is good to leave some memory for non-buffer pool and non-SQL Server processes, and to leave enough to allow for peak usage of those processes so as not to force the OS to make a hard decision about who to give memory too, or worse, to page memory to disk. However making a blanket statement about leaving 2-3 GB RAM free at all times on a machine with only 12GB of RAM is not what I would call a good use of available resources in a lot of cases. It will depend on what else the server does. Some servers dedicated to running SQL Server happily run with 99% of RAM utilized on a consistent basis because a level of stability has been reached where that is possible.
thanks very much for the response....
i will try setup a job to see the processes.. i'm believe this is an OS issue...
At the time when alert was generated...i don't see much activity in the sql server ( we can see past history using history browser in Idera ).
and here we got another alert on this instace...not sure if its related to memory...
5/16/2013 10:40:14 AM, SQL Server Thread Count on XXXXXX is Warning.
SQL Server instance XXXXXX- SQL Server Thread Count is 400.00.
SQL Server Thread Count: The number of threads currently active in this process. An instruction is the basic unit of execution in a processor, and a thread is the object that executes instructions. Every running process has at least one thread.
please let me know if you need anymore information