SQL using available ram

  • A week ago we moved to a new server running Windows 2003 64 bit with SQL server 2005 64 bit and Coldfusion 8 64 bit. We increased the memory from 4 gigs to 8 gigs. We have currently 3 instances of Coldfusion running with 1 gig allocated to each. SQL is taking nearly all the rest. When we only had 4 gigs, SQL 2005 32 bit was only using around 1.8 gigs. I was told that you can insert a maximum into the SQL so that it does not go above that amount. However, I was also told that Microsoft does not recommend it because it could affect performance. Also that if it needed more memory it would start to write to the disk.

    Yesterday when the available memory got extremely low, I restarted the SQL.

    Does anyone have advice for me?

    Thanks DR

  • My first recommendation would be to move ColdFusion to another server. MS SQL is much more efficient when it is not competing for hardware resources - particularly memory intensive ones.

    In the server properties, you can configure a minimum and maximum amount of memory for MS SQL to use. Every install is different and you will have to do some testing to determine the best settings in your case.

    As far as running low on memory and spilling over to disk - that is what virtual memory and the swap file(s) are for. This happens based on what the OS needs to do. Limiting the memory SQL can use may prevent this from happening, but it may also prevent SQL from caching something in memory that it needs to and therefore having to go back to the data files on disk anyway. That is why it will be important to test the settings carefully.

  • Something to consider when virtual memory paging is happening, it's causing a read from disk and an extra write to disk to happen. When SQL Server is managing its cache it knows which pages are clean and don't need to be written to disk, so it can do a disk read of the new pages needed without doing a write of the old pages that were in memory. Depending on the configuration of your disk drives, this can easily lead to I/O bottlenecks on a system that depends too much on virtual memory.

  • SQL Server will manage memory, but it's slow to do so and it doesn't like to give it up. If you must run Cold fusion or something else that needs memory (IIS, etc.), you need to set a max for SQL Server.

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

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