Take the 2-minute tour ×
Server Fault is a question and answer site for 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

4 Answers 4

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

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

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

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

Your Answer

 
discard

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