How is SQL Server using all that memory, anyway?

I want to stick with the subject of memory because I think it’s probably the most misunderstood (and sometimes downright mysterious) component of SQL Server. I know I, for one, could benefit from a better understanding of its inner workings. So today I’d like to share that little bit of code I mentioned last week, [...]

May 2, 2011  Tags: , , ,   Posted in: General, SQL Tuning, Troubleshooting  No Comments

Available Workspace Memory

My research into the Resource Governor question and its answer brought up another question for me: Where does that total_memory_kb number for the default pool actually come from? After all, I have a max server memory of 26000MB for the instance, so why 18175MB?В  The BOL description for this field wasn’t very helpful.В  Googling for [...]

April 28, 2011  Tags: ,   Posted in: General, SQL Tuning, Troubleshooting  No Comments

Aha!

I found the answer to my Resource Governor quandary in this outstanding article, Understanding SQL Server Memory Grant, on MSDN.В  The juicy bit: The server calculates the memory limit for one query. By default, this is 25% (20% on 32bit SQL 2005) of total query memory (which is set by memory broker as about 90% [...]

April 25, 2011  Tags: , ,   Posted in: Features, Troubleshooting  One Comment

Resource Governor causing memory problem??

So.В  Last week I blogged about the experiment I was running with Resource Governor for one of our newly upgraded development databases.В  I had enabled Resource Governor and created a procedure to categorize sessions, but I hadn’t set any limits and everyone was sharing the same default pool.В  The plan, if you’ll recall, was to [...]

April 22, 2011  Tags: , , ,   Posted in: Features, Troubleshooting  One Comment