0

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?

4 Answers 4

2

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?

1

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

Sql 2000 and AWE

2
  • 1
    its not a bug! its a feature Oct 8, 2009 at 7:53
  • I'll check using perfmon, but my server is not using AWE
    – Víctor Rivero
    Oct 8, 2009 at 10:30
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.

0

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

You must log in to answer this question.