SQLServer taking 12GM of RAM how to trouble shoot it

Question

SQLServer taking 12GM of RAM how to trouble shoot it

narasimhareddy.dba

SSC-Addicted

Points: 404
More actions
December 30, 2010 at 1:08 pm

#388527

Hi,
sqlserver taking 12gm Ram how to trouble shoot it .sql server is running slowley.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Nothing unusual about the memory, SQL loves memory, it will use as much as it can get. Be sure to limit the max memory so that the OS has some reserved.

As for the slow performance, can you elaborate?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

As Gila said, SQL loves memory, it wll take all available to it if you let it. Depending on how much physical memory the system has depends on how much you let it use, but at at least 12GB I'd probably set total memory - 2GB for SQL. This is if SQL is the only thing running on this box, if there are other things then they might be fighting SQL for memory..

CEWII

Indianrock SSC-Insane Points: 20333 More actions · Answer 3

narasimhareddy, I think you need to give us more information. How much memory does the sql server box have, total? On windows server 2003 we found we could allocate about 13GB of 16GB available to sql using the max memory setting. Our main production server is now Windows Server 2008R2 with 64GB of memory and we eventually had to limit sql server to about 45GB using that max memory setting. That setting really only controls the data buffer cache so the plan/procedure cache and other sql server overhead have to use some of the 19GB remaining along with the operating system.

Indianrock SSC-Insane Points: 20333 More actions · Answer 4

I forgot to mention that every time you adjust the memory setting on sql server, the plan cache will get emptied ( not sure about data buffer ), so you don't want to be fiddling with it all the time because performance will suffer for a while.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Indianrock (12/30/2010)
That setting really only controls the data buffer cache so the plan/procedure cache and other sql server overhead have to use some of the 19GB remaining along with the operating system.

Max memory sets the buffer pool, that's all paged memory. Data cache, plan cache and most other caches. What's outside the buffer pool (non-paged memory) is CLR memory, thread stacks, backup buffers, in process DLLs and a couple other smallish things.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Indianrock SSC-Insane Points: 20333 More actions · Answer 6

Gail, I stand corrected. Just checked our Quest Spotlight tool and it's showing max memory 42GB ( that is our max setting ), with 36.5 for buffer cache and 5.26 for plan cache. With Server2008R2 using about 16GB constantly for file cache ( I'm assuming that is totally unrelated to sql's caches' ) it leaves about 6GB for everything else.