SQL Server memory limited

Question

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?

score 2 · Answer 1 · answered Oct 08 '09 at 13:47

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?

score 1 · Answer 2 · edited Apr 13 '17 at 12:14

1

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

Sql 2000 and AWE

edited Apr 13 '17 at 12:14

Community

1

answered Oct 08 '09 at 07:34

Paul

714
2
6
19

1

its not a bug! its a feature – Nick Kavadias Oct 08 '09 at 07:53
I'll check using perfmon, but my server is not using AWE – Oct 08 '09 at 10:30

score 0 · Answer 3 · answered Oct 08 '09 at 07:33

0

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.

answered Oct 08 '09 at 07:33

lg.

4,579
3
20
20

SuperCoolMoss · Answer 4 · 2009-10-10T18:42:27.560

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).

SQL Server memory limited

4 Answers4