sqlserver.exe and memory usage

  • We all know by design sqlserver.exe is designed for a dedicated server and because of such eventually as its used will take up almost 95% of available RAM. (at least thats how i always have seen the case).

    Now, If i am using sql server developer edition (2008) on my workstation , what i usually do is manually limit the ram that sqlserver takes in order to keep my workstation usuable. Now let me get to my questions, and please respond to all the questions or the ones that you have an answer too if possible.

    1) Why does sqlserver under normal operation take up so much ram. I would assume its only due to large queries and it caches such queries, but im my operation im not really doing many selects if so its one field, but many inserts and updates. So in conclustion what is it that makes the ram grow, selects, updates, inserts , all, something else?

    2) by limiting my ram, am i hurting my server in anyway? Now remember, in my environment none of my select statments are redundant so cachine a queiry is useless to me because new data is asked from it evertime. My table is rather large, it has 42 million records in it with varchar(50) as the text field which is also a unique index, with an autoid that is primary key autoincriment

    3) is there a way to free all this ram. i remember some dbcc command but it didnt really free anything in terms of RAM. Obvioiusly what alway seems to work good is just restarting sql server but all my apps loose connection. What im trying to do, as an alternative to limiting the ram on my machine, is periodically run some stament to clear the ram. is there a way?

    4) does sql server also, in opposite, release ram if it noticed another process on my machine needing a large amounts of ram, i think i have noticed sqlserver.exe doing this were it would release ram for another exe is this true?

    Thanks all for your answers and time in advance.

    Mike Evanchik

    http://www.MikeEvanchik.com

  • queries are only a small part of what sits in RAM, you have the buffer pool too which comprises all the database pages read from disk to memory for modifying. There are many books\topics on this have a scout around

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • SQL Server will end up using as much RAM as you allow it, in most cases, not just to cache common data, but also hold execution plans, for execution space, for temp tables and table variables, for other variables, and so on. That's normal, and in most cases desirable, since it allows the server to do everything faster. And, yes, that includes updates and deletes and inserts.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • is there any answers to questions 2, 3 and 4?

  • 2) Yes you can, but if you don't it can have affect on server. How much memory do you have in the server? Ideally Windows Servers should ave at least 512mb to work with; I usually give windows servers at least 750mb and give rest to SQL Server.

    3 & 4) If SQL Server senses memory pressure it will release the memory itself. You should not have to do this. So I would not recommend you manually clearing buffer cache or such.

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • xgcmcbain (3/11/2009)


    2) by limiting my ram, am i hurting my server in anyway? Now remember, in my environment none of my select statments are redundant so cachine a queiry is useless to me because new data is asked from it evertime. My table is rather large, it has 42 million records in it with varchar(50) as the text field which is also a unique index, with an autoid that is primary key autoincriment

    no, you need to limit RAM to ensure both sql server and the OS all have enough resources to do their job. How much RAM does your server have?

    xgcmcbain (3/11/2009)


    3) is there a way to free all this ram. i remember some dbcc command but it didnt really free anything in terms of RAM. Obvioiusly what alway seems to work good is just restarting sql server but all my apps loose connection. What im trying to do, as an alternative to limiting the ram on my machine, is periodically run some stament to clear the ram. is there a way?

    there's more than one of them, but i assume you refer to DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. Realise the implications before using these, they are described in BOL

    xgcmcbain (3/11/2009)


    4) does sql server also, in opposite, release ram if it noticed another process on my machine needing a large amounts of ram, i think i have noticed sqlserver.exe doing this were it would release ram for another exe is this true?

    Thanks all for your answers and time in advance.

    Mike Evanchik

    http://www.MikeEvanchik.com[/quote%5D

    The OS will request sql server release memory if the OS is under pressure, this usually happens fairly quickly but you can get times when it doesnt happen quick enough and your server becomes vulnerable. Memory allocations should be carefully planned

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • no, you need to limit RAM to ensure both sql server and the OS all[/i] have enough resources to do their job. How much RAM does your server have?

    i have 3 gig of ram, im going to bump it up to 4 gig, usually Vista (but in this canse im running Window 2008 R2 beta 64bit) uses around 1 GIG easily, if i load apps it needs more, so i would say as a desktop i would allocate 2 GIG to my computer, and 1 GIG to SQL server

    there's more than one of them, but i assume you refer to DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. Realise the implications before using these, they are described in BOL

    clean buffers didnt do much, ill try the other, i didnt notice either of these releasing any RAM

    The OS will request sql server release memory if the OS is under pressure, this usually happens fairly quickly but you can get times when it doesnt happen quick enough and your server becomes vulnerable. Memory allocations should be carefully planned

    thankyou

  • Yes you can, but if you don't it can have affect on server. How much memory do you have in the server? Ideally Windows Servers should ave at least 512mb to work with; I usually give windows servers at least 750mb and give rest to SQL Server..

    I have # gigs of ram, im going to update it to $ gig soon (which is the max) My OS needs at least 1.5 to 2 gigs itself as a workstation, the rest i give to sql server, im just wondering by limiting sql servers memory how much does it hurt it.

    3 & 4) If SQL Server senses memory pressure it will release the memory itself. You should not have to do this. So I would not recommend you manually clearing buffer cache or such.

    Thanks.

    Thank you

  • Memory issues can lead to high disk IO which can lead to lagged reads/writes. Depends on your database throughput :).

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • The DBCC commands mentioned above will clear the cache but won't release memory back to the OS.

    Your best option is to limit the memory used by setting "max server memory".

    You don't say whether you are using 32 or 64 bit, or how much memory you have. There are other settings for AWE, parameters in boot.ini and a right you can give to the service account called "Lock Pages in Memory". One or more of these may need to be used, depending on your configuration.

    In answer to your question about whether limiting the memory can have an adverse effect, it is far better than forcing the OS to page out SQL Server memory, which is what could happen otherwise.

    Edit Alert !!!!!

    Just re-read the thread and you HAVE specified the details, although there are 2 conflicting posts.

    If you only have 4GB you can ignore most what I have just said about memory settings.

  • Richard Fryar (3/11/2009)


    The DBCC commands mentioned above will clear the cache but won't release memory back to the OS.

    didnt think so thanks

    [You don't say whether you are using 32 or 64 bit, or how much memory you have

    .

    Im running SQL Server 2008 Enteprise Edition 64 bit on Windows Server 2008 R2 Beta 64 bit

    all cummulitive rollupbackages on both OS and SQL server

    [ There are other settings for AWE, parameters in boot.ini and a right you can give to the service account called "Lock Pages in Memory". One or more of these may need to be used, depending on your configuration.

    not sure what this is are anything about it, i will have to research your statment above

    In answer to your question about whether limiting the memory can have an adverse effect, it is far better than forcing the OS to page out SQL Server memory, which is what could happen otherwise.

    yup you know what, i totally agree thanks

    Edit Alert !!!!!

    Just re-read the thread and you HAVE specified the details, although there are 2 conflicting posts.

    If you only have 4GB you can ignore most what I have just said about memory settings.

    I have 3g of memory and im going to upgrade to 4GB,

    1 GB im giving to SQL server, the rest im leaving for the OS.

    When intel7 chips and motherboard prices go down, i will consider building another computer where RAM wont be much of an issue =)

  • Since you are using 64-bit; awe settting does not apply to you. Now since I know that I strongly recommend you change it LOL. I seen a 64bit memory with 16gb and sql server chewed it all up becuase bad min/max settings (and because I didn't set up the SQL Server :P).

    For SQL Server I would give 2G and your os 2G :).... common be nice to SQL Server it is a good product :w00t:

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

Viewing 12 posts - 1 through 11 (of 11 total)

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