Solved

Sql Server Memory used but not accounted for in Task Manager

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Scenerio: You have a server running Server 2003 and have applied a retail pack of Terminal Server Licenses.  You want to change servers or your server has crashed and you need to reapply the Terminal Server Licenses. When you enter the 16-digit lic…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

680 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