September 2, 2013 at 2:12 pm
sql server is 2008 r2 on 64 bit systems and os is windows 2008.
Hi friends at one of our client side, it show sql server is using 12gb of ram out of 32 .my client is asking why sql is using this much.also there is not much hits on it
can any body tell queries which can tell me how much memory is sql server and its components are using with details description.
They want to decrease its memory consumption i said its not possible once sql gets memory it doesnt release.but they are adamant.plz tell me solution
can max memory server fix this problem , buy what if i set it and then sql server requires more memory .
since its 64 bit setting awe wont help
but does "lock pages in memory" and min max setting would be usefull.
plz tell queries through which i can know what memory is begin used by sql server and its components like Bpool,others etc and all that.
what other options/things i should check since database is of 2 gb so fragmentation is not an issue i think.
plz help
regards
Anoop
September 2, 2013 at 3:17 pm
Set max server memory to a sensible value.
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
September 2, 2013 at 11:34 pm
GilaMonster (9/2/2013)
Chapter 4: https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/Set max server memory to a sensible value.
hi gail thanks for link but can u more specific
I would download the book an study it but can you be more specific for my post its urgent
thanks and regards
Anoop Pandey
September 3, 2013 at 12:41 am
Err... the second line of my post....
As for why SQL's using the memory, because it can. Because it has not been told that it can't (via max server memory). Set max server memory and have a read through chapter 4 (which is around 15 or so pages) for all the details on what to set it to, how SQL uses memory, queries that let you view memory usage, etc.
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
September 4, 2013 at 3:21 am
GilaMonster (9/3/2013)
Err... the second line of my post....As for why SQL's using the memory, because it can. Because it has not been told that it can't (via max server memory). Set max server memory and have a read through chapter 4 (which is around 15 or so pages) for all the details on what to set it to, how SQL uses memory, queries that let you view memory usage, etc.
Thanks gail one last question
suppose sytem is 64 bit
suppose i set max server memory to certain value , enabled lock pages in memory is enabled, now suppose sql server requires memory more then that what i have set.so then what server do , will it acquire form os.
2> secound suppose sytem is 32 bit and awe is eanbled ,so is lock pages in memory min .max server option is set too,what happens in this scene
3>setting max server memory option does limit memort for all sql server components or only that of buffer pool
September 4, 2013 at 4:40 am
anoop.mig29 (9/4/2013)
suppose i set max server memory to certain value , enabled lock pages in memory is enabled, now suppose sql server requires memory more then that what i have set.so then what server do
Nothing. You set the max memory it can use.
2> secound suppose sytem is 32 bit and awe is eanbled ,so is lock pages in memory min .max server option is set too,what happens in this scene
Nothing. You set the max memory it can use.
3>setting max server memory option does limit memort for all sql server components or only that of buffer pool
Buffer pool (which is the larges component of SQL's memory usage)
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply