Sql Server Memory used but not accounted for in Task Manager

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
prairie1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mass2612Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
prairie1Author Commented:
Thanks very much, I'll start looking at the perfmon stuff, that sounds a bit more reasonable.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.