Solved

Sql Server Memory used but not accounted for in Task Manager

Posted on 2010-08-19
2
690 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
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Join & Write a Comment

Setting up a Microsoft WSUS update system is free relatively speaking if you have hard disk space and processor capacity.   However, WSUS can be a blessing and a curse. For example, there is nothing worse than approving updates and they just have…
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now