How do I set a fixed amound of memory for SQL?

  • kereynolds

    Grasshopper

    Points: 16

    Hi there- newbee question for ya. I have an SQL server with IGB RAM that was recently upgraded to 2GB but SQL still hogs all the RAM for itself. I have been told I should reserve 15% for the OS but I don't know how. Its SQL7 running on WinNT4.

    Thanks,

    Kevin Reynolds

  • Andy Warren

    SSC Guru

    Points: 119675

    I wouldnt worry about reserving for the OS, SQL will give back memory if the OS needs it. As long as SQL is the only thing on the box, let it manage the memory.

    Andy

  • kereynolds

    Grasshopper

    Points: 16

    Thanks for your reply Andy, but every Monday morning, when out staff are doing their time sheets on our Practice Management system, monitoring shows SQL using 100% of the memory, with nothing reserved for the OS. The server slows down to a crawl even though our vendors insist that it should be well able for the number of transactions. There is nothing else running on the server, although it is communicating with 2 IIS servers that are displaying the info. Any further ideas welcome.

  • Andy Warren

    SSC Guru

    Points: 119675

    Then why would OS memory matter? If you're just running queries, you need to look at what kind of load you're putting on the server and why. I'd suggest looking at cpu usage and disk activity during that period. You may need to tune the queries, may be that you need MORE memory!

    Andy

  • neiljacobson

    SSCommitted

    Points: 1717

    Check the virtual memory settings in the OS.

    The minimum pagefile should be RAM + 11MB.

    Also check if you are getting blocks by running sp_who2 active. This will tell you if you are getting lots of locking contention.

    If the query is regularly used, write it into a stored procedure and let the users call it.

    Run DBCC Checkdb against all the databases (after hours with a scheduled job).

    Also run the index wizard to evaluate where indexes might alleviate the bottlenecks.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715079

    If you really want to change it, and I HIGHLY recommend against it, you can right click on the server in Enterprise Manager , select properties, and choose the "Memory Tab". There you can set a max and a min.

    Is it 100% of memory (physical or available) or CPU? Where are you getting your 100% information?

    Steve Jones

    steve@dkranch.net

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

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