sql server using more memory issue

  • 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

  • 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.

    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
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

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

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

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