Solved

Sql Server Memory used but not accounted for in Task Manager

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
execute a MS SQL script as a schedule SQL job 72 123
Shadow copies windows server 2003 2 79
server DNS address could not be found 22 131
Need help debbuging stored procedure 21 34
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

13 Experts available now in Live!

Get 1:1 Help Now