Performance Tuning

  • Hello experts,

    i have 8 gb ram in my production srver, i see most of the time it takes 7.5 gb in use, how wud i knw is it genuine or some tuning is required, because now it is taking 7.8 gb

    pls help

    thank u

  • By default, SQL Server will attempt to use as much physical memory as possible in order to cache data. The more data that's cached in memory, the less the disks have to work, and the quicker your queries will be.

    A good rule of thumb for an 8 GB machine is to set the "Max Server Memory" value for SQL Server to 6.5 GB, which will allow 1.5 GB for other processes. Later, if you find that you've got plenty of physical RAM free, and you never dip into that spare memory, you can increase the figure.

  • how wud i set max server memory upto 6.5 gb ?? pls tell me

  • Are you the DBA? If not, ask the DBA to do it for you.

    If you are, you can use either management studio GUI (Object explorer, right click server -> properties -> memory) or you can use sp_configure.

    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
  • i am the dba but i have never done, this it is showing 2147483647 kb shud i directly change it or will have to take any coution on production, and will it work directly or i have restart service

  • you have to put 6500 in the max memory.

    and you don't have to restart the sql

  • 2147483647 is the default, which means "use as much as you can". It's not the most explanatory value, but it is more memory than a server will have for quite a few years yet (at least, a server that we're likely to touch, anyway).

    After setting the value, you may find that memory usage is still high, and it may remain this way until SQL Server responds to memory pressure, or you restart the service. If you never have memory pressure, then its memory usage may stay higher than 6.5 GB for quite some time.

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

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