Solved

Sql Server Memory used but not accounted for in Task Manager

Posted on 2010-08-19
2
699 Views
Last Modified: 2012-05-10
Hi,
I have an instnace of Sql-Server 2005 64-bit running on Windows Server 2003 R2 Standard x64 SP2.  The box has 8 gigs Ram.

For the Memory options in sql-server I have Minimum 128 MB and max 2147483647 MB.

However, Task Manager *always* shows around 7.8 GB on the Performance tab, under PF Usage (This tab, I've found from previous posts I've made, has some extremely counter-intuitive wording for these graphs and memory information, but I've been able to gleen that this is basically the amount of memory in use).  

Also, the "Physical Memory" numbers show:
Total: 8,386,176
Available: 171,732
System Cache: 554,092

This box, for all intents and purposes, is totally dedicated to sql-server, there are no other non-OS processes that run on this.  These numbers are essentially stagnant whether the server is being pounded with huge loads and when it's completely idle with essentially no db connections.

But in the "Processes" tab it only says 187,296 K for sqlservr.exe.

So my question is how can that much memory can be "off the books", or "stealth processes"?

It just makes me nervous that so much memory can be used up and hidden from any sort of accountability.  For things from viruses to errant programs, I've always sort of depended upon Task Manager to show me what the heck is taking up memory.  But I've lost trust in that now that I see that so much memory can be taken up and Task Manager can't even account for it.

My reasonable assumption is that it's Sql-server that's using this memory, but with the minimums set the way they are why doesn't it do one of two things:

A. Release the memory
B. Show the memory usage in Task Manager's Processes tab, showing the process using (or at least reserving) that huge chunk.

Thanks,
--Jim
0
Comment
Question by:prairie1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 29

Accepted Solution

by:
mass2612 earned 250 total points
ID: 33474694
Hi,

Task manager is no good for trying to get any real idea of what's happening with SQL you are just making life hard for yourself. For a dedicated 64 bit SQL server with 8GB of RAM I would start with setting the max memory to approx 6GB (leaving 1GB for the OS and 1GB for AV, backup, etc).

When SQL server starts is does not acquire all the memory at that point. It starts with the minimal amount and then grows as necessary. Once it grows above the min memory setting that you have set it won't release any memory below that.

Some of the Perf Mon counters I use for a quick look at this are: -

MSSQL$Instance - Memory Manager\Total Server Memory - shows current size of buffer pool
MSSQL$Instance - Memory Manager\Target Server Memory - shows ideal size of buffer pool if the Target Server Memory is greatly lower that this the server is under memory pressure
MSSQL$Instance - Buffer Manager\Page Life Expectency > 300 seconds

Lot of Perf info here : -
http://www.brentozar.com/sql/sql-server-performance-tuning/
0
 

Author Comment

by:prairie1
ID: 33474744
Thanks very much, I'll start looking at the perfmon stuff, that sounds a bit more reasonable.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
While rebooting windows server 2003 server , it's showing "active directory rebuilding indices please wait" at startup. It took a little while for this process to complete and once we logged on not all the services were started so another reboot is …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question