Post reply

Memory Setting in SQL SERVER 2008

  • June 16, 2010 at 1:15 pm

    #222206

    Hi guys,I am new on SQL SERVER 2008.We have installed SQL SERVER 2008 R2 on a machine(10GB RAM,Windows 2008 64 Bit).When I check the Memory usage,I noticed that SQL SERVER is using most of memory(9.6GB)

    Is there any consideration or setting realted to the memory in SQL SERVER 2008(Configuration Setting) or this situation is normal.

    Thanks

  • June 16, 2010 at 1:20 pm

    #1181253

    What's the SQL instance's max memory set to ?

    10GB memory? That's a fairly odd amount.

    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

  • June 16, 2010 at 1:47 pm

    #1181268

    When I checked the instance property ,it is (2,147,483,647 kb),which I guess is default one,because nobody has not changed it.So should I apply any changes on this memory setting.

  • June 16, 2010 at 2:43 pm

    #1181284

    If the server really does have 10GB of memory (which is a seriously odd amount) set max memory to 8GB.

    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

  • June 16, 2010 at 3:05 pm

    #1181290

    Is there any standard for setting the Max Memory for SQL SERVER 2008(Like 80% of Physical memory)?

  • June 16, 2010 at 3:27 pm

    #1181296

    Not exactly. You should limit it and the more memory that the server had, the more memory should be left for the OS. My very rough rule of thumb is < 4GB, up to 1GB for OS. 8-16GB, 2 GB for OS. 32-64GB, 4GB reserved for OS. Interpolate as necessary. That's not a rule or a standard, it's just my personal rough guide that I use when recommending server memory settings

    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

  • June 16, 2010 at 4:58 pm

    #1181310

    Thanks alot for your reply.

  • June 18, 2010 at 5:45 am

    #1182046

    By defaul SQL Server uses all the memory of the machine. I suggest you set up less memory.;-)

  • June 24, 2010 at 8:44 am

    #1184548

    As per Microsoft recommendation, You have to give 6 GB memory to Operating System(Window 2008 64Bit). Remaining you can give to SQL Server as Max Memory. we also have same kind of environment here, so we got this information from Microsoft Engineer. It performs very well in our Prod Env.

    Thanks

    AKP

  • June 24, 2010 at 8:54 am

    #1184557

    AKP (6/24/2010)

    As per Microsoft recommendation, You have to give 6 GB memory to Operating System(Window 2008 64Bit).

    6GB reserved for the OS on a server that only has 10GB? That's a bit much, don't you think?

    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

  • June 24, 2010 at 12:42 pm

    #1184652

    This is microsoft recommended figure for Windows 2008 R2 64bit Server. But for windows 2003 Server, you can give 4GB.

    Thanks

    AKP

  • June 24, 2010 at 5:04 pm

    #1184750

    AKP (6/24/2010)

    This is microsoft recommended figure for Windows 2008 R2 64bit Server. But for windows 2003 Server, you can give 4GB.

    Reference?

    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

  • June 25, 2010 at 1:41 am

    #1184832

    If the server had a LOT of installed RAM I could see those numbers being reasonable--the memory page size Windows uses doesn't change, so the internal structures for keeping track of the memory in use will necessary be larger if you have more RAM. Definitely agree with GilaMonster that 6Gb or 4Gb reserved for the OS on a machine with only 10Gb of RAM in total is overkill, though.

  • June 25, 2010 at 1:58 am

    #1184838

    paul.knibbs (6/25/2010)

    If the server had a LOT of installed RAM I could see those numbers being reasonable--the memory page size Windows uses doesn't change, so the internal structures for keeping track of the memory in use will necessary be larger if you have more RAM.

    Agreed. Up around 64GB installed RAM, I'd be very happy to reserve 4-6GB of memory for the OS.

    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

  • June 28, 2010 at 3:59 am

    #1185610

    I saw a table once from Microsoft that said you needed to reserve 6Gb for Windows ...on a 64GB server. The same table showed (I think) that about 1.5 GB would be needed on a 10GB server. My memory of the table is better than my memory of its URL, but maybe Google knows where it is.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 15 posts - 1 through 15 (of 19 total)

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