Prev: Auto Update Statistics
Next: Report server setup for multiple applications
From: Abraham Ocón C. on 5 Aug 2010 05:28
I am the new DBA in a company and I see that their production server
has between 90 and 95% of memory been used. The problem is that
according to the task manager, is not SQL Server or related services
which is using the memory, in fact is using only 460MB and MS SQL
Server Analysis services is using 571MB, Rerporting Services use
252MB. This server has 8GB and I don't the see the sum of all the
other processes give around 500MB more.

The sum is around 2GB then. What can be using the resting 5.5GB? And
if is SQL Server, where can I see that in detailed?

I found that as a problem because I am running a Maintenance plan that
is continuosly failing but the log file says that succeded. When I try
to see the history of the Maintenance plan in Management Studio, it
gives me an "Exception of type System.OutOfMemoryException" error so I
can not see the actual log of the maintenance plan, I just can go to
the .txt file to see the log.

The SQL server is configured to use the 8GB, no AWE option enabled and
is a 64bit version, in a 4 processors server. Minimum memory per query
is 1MB. OS is Windows Server 2008 and SQL Server is 2008 too.

I will appreciate a lot your reply and possible solutions.
From: Lijun Zhang on 5 Aug 2010 12:25
I think in your case you should configure the SQL Server use 6GB, and leave
at least 2GBof memory to the operating system. SQL Server 2008 64 bits does
not require AWE option enalbed.

DBCC MEMORYSTATUS command will give you the detail memory status of SQL
Server.

Lijun


"Abraham Oc�n C." <abraham.ocon(a)gmail.com> wrote in message
news:072679b6-086e-4383-ab06-fb646f6005a5(a)t20g2000yqa.googlegroups.com...
>I am the new DBA in a company and I see that their production server
> has between 90 and 95% of memory been used. The problem is that
> according to the task manager, is not SQL Server or related services
> which is using the memory, in fact is using only 460MB and MS SQL
> Server Analysis services is using 571MB, Rerporting Services use
> 252MB. This server has 8GB and I don't the see the sum of all the
> other processes give around 500MB more.
>
> The sum is around 2GB then. What can be using the resting 5.5GB? And
> if is SQL Server, where can I see that in detailed?
>
> I found that as a problem because I am running a Maintenance plan that
> is continuosly failing but the log file says that succeded. When I try
> to see the history of the Maintenance plan in Management Studio, it
> gives me an "Exception of type System.OutOfMemoryException" error so I
> can not see the actual log of the maintenance plan, I just can go to
> the .txt file to see the log.
>
> The SQL server is configured to use the 8GB, no AWE option enabled and
> is a 64bit version, in a 4 processors server. Minimum memory per query
> is 1MB. OS is Windows Server 2008 and SQL Server is 2008 too.
>
> I will appreciate a lot your reply and possible solutions.


From: Erland Sommarskog on 5 Aug 2010 17:28
Abraham Oc�n C. (abraham.ocon(a)gmail.com) writes:
> I am the new DBA in a company and I see that their production server
> has between 90 and 95% of memory been used. The problem is that
> according to the task manager, is not SQL Server or related services
> which is using the memory, in fact is using only 460MB and MS SQL
> Server Analysis services is using 571MB, Rerporting Services use
> 252MB. This server has 8GB and I don't the see the sum of all the
> other processes give around 500MB more.
>
> The sum is around 2GB then. What can be using the resting 5.5GB? And
> if is SQL Server, where can I see that in detailed?

Yes, I think it is SQL Server. If memory serves, if the service
account has the permission "Lock Pages in Memory", the memory does
not display in Task Manager.

I got this query from SQL Server MVP Glenn Berry:

SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
page_fault_count, memory_utilization_percentage,
available_commit_limit_kb, process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;

It shows you the actual memory usage for SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Auto Update Statistics
Next: Report server setup for multiple applications