Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm having a problem with one SQL Server instance and its memory usage.

The issue is that even the server maximum memory option is set to 10 GB, when I take a look in the taskmanager the sqlserver.exe process never gets more than 200MB of memory.

In the same server (windows server 2008) there's another sqlserver instance configured in a similar way, and over there that is not happening (running the same queries and jobs allocates much more memory).

Do you have any clue about what I could do to solve this?

share|improve this question
add comment

4 Answers

Run perfmon and select the following counters from SQLServer Memory Manager list for the instance in question:

Target Server Memory Total Server Memory

The Total server memory will tell you accurately how much the SQL server instance is using for its buffer cache.

I expect the Target server memory will show 10GBs (assuming your environment is 64 bit or 32 bit with AWE enabled). If the Total is a lot less than the Target after the SQL instance has been running for a while then I expect the cumulative size of your databases on your instance is less than 10GBs? or possibly the databases tables/indexes haven't be touched since the last time the instance was restarted (to ensure every table is touched try running DBCC checkdb against the databases).

share|improve this answer
add comment

First, what version of SQL Server and edition are you running? Is this SQL 2000, 2005 or 2008? Is it standard, MSDE, enterprise, express?

Is it a 32 bit or 64 bit OS?

If you're 32 bit, you won't get more than 2+GB without AWE. I'd recommend you check out a few resources:

http://msmvps.com/blogs/bradley/archive/2009/01/15/how-to-troubleshoot-sql-server-memory-related-issues-part-1.aspx http://www.mssqltips.com/tip.asp?tip=1268

If you are SQL 2000, be sure this isn't an issue: http://support.microsoft.com/kb/899761/en-us

Also, are you sure you are checking the correct instance on the server?

share|improve this answer
add comment

For example this is a known bug in sql server 2000. See my question :

http://serverfault.com/questions/70678/sql-2000-and-awe

share|improve this answer
1  
its not a bug! its a feature –  Nick Kavadias Oct 8 '09 at 7:53
    
I'll check using perfmon, but my server is not using AWE –  Víctor Rivero Oct 8 '09 at 10:30
add comment

MS SQL Server has several limits depending by version/license. Maybe running two instances of sql server could have further limits. Try to search on Microsoft sites about its limits.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.