none
Memory allocation RRS feed

  • Question

  • Hello,

    If SQL Server is given unlimited memory to use, can it create problem for other applications in the server?

    Thank you.

    Friday, October 21, 2011 4:39 PM

All replies

  • Hello Ryan,

    No. If other application requires memory then Windows System request SQL Server to release memory.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Friday, October 21, 2011 4:44 PM
    Moderator
  • You can control SQL Server Memory usage by settings Max Memory and Min Memory.

    Ideally it is not recommended to have multiple applications running on the same server where SQL Server is configured.It depends from situation.If your system is super critical,then running other apps is a strict NO.

    Thank you,

     


    Anup | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Friday, October 21, 2011 4:47 PM
  • Thanks for your reply.

    We have a small server, where sql server is given unlimited memory, the users recently started complaining that another front-end application in the server has been consistently been very slow when it loads or does anything. We have the same application in other branches but it is working fine there. I checked the sql server, it is not a busy server and I think the SQL is working fine. Any thoughts on where we need to look to find the problem with the front-end application?

    Friday, October 21, 2011 4:52 PM
  • yes it can especially if its 64 bit. Please please please.. limit the max server memory.
    SQL would definitly respond to memory pressure requested by OS but I would not do that.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Friday, October 21, 2011 5:04 PM
    Moderator
  • Well its SQL Server 2005 standard edition. How can we find if it is 32 or 64 bit?
    Friday, October 21, 2011 5:23 PM
  • Well its SQL Server 2005 standard edition. How can we find if it is 32 or 64 bit?
    Run
    Select @@version
    


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Friday, October 21, 2011 5:34 PM
    Moderator
  • Ok, I got the following,

    Microsoft SQL Server 2005 - 9.00.3175.00 (Intel X86) Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    Intel X86 - so its 64 bit, right?

     

    Friday, October 21, 2011 5:39 PM
  • Ok, I got the following,

    Microsoft SQL Server 2005 - 9.00.3175.00 (Intel X86) Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    Intel X86 - so its 64 bit, right?

     


    x86 - 32 bit!
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Friday, October 21, 2011 5:43 PM
    Moderator
  • please post sp_confgure output


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Friday, October 21, 2011 5:44 PM
    Moderator
  • Which values from sp_configure output do you want to see?
    Friday, October 21, 2011 5:48 PM
  • Which values from sp_configure output do you want to see?

    AWE Enabled
    Max Server Memory
    Min Server Memory
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Friday, October 21, 2011 5:55 PM
    Moderator
  • AWE Enabled is false, min memory is 0 and max memory is set to unlimited.
    Friday, October 21, 2011 5:58 PM
  • What is your total memory for the server?

    You need to set Max Memory for SQL Server using sp_configure

    An example is  - Setting max memory to 4 GB

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'max server memory', 4096;

    GO

    RECONFIGURE;

    GO

     

    It depends on how much max memory you want to assign to SQL Server.As mentioned earlier,always remember this point.

    'Ideally it is not recommended to have multiple applications running on the same server where SQL Server is configured.It depends from situation.If your system is super critical,then running other apps is a strict NO.'

     


    Anup | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Friday, October 21, 2011 7:10 PM
  • There is a formula to determine how much memory you need for your server.

    First of all you probably wants 2 GB for Windows, can be less but 2 GB is a nice start. Second you need 1 GB for Linked servers, MPA (Multipage Allocation), and other process outside the buffer pool, and 1 GB or more for applications running in your database server, but as they said before, no APPs should be running on your database server.

    Besides, you have to check your total working threads, (Task Manager - Performance - System )

    Let's see the following example.

    We have here 886 threads, so we have to use a little math here to multiplicate the total threads for 0.5 in 32 bits environment and for 2 for 64 bit architecture.

    So let's say my server is under 32 bit.

    886* 05=44 so 886+44 =934

    Now we have 2 GB for Windows, 1 GB for APP such a Backup Library,  1GB for other SQL Server process and 934 extra MB, we're talking about 5 GB and SQL Server will work just fine, now if you have extra memory of course you can use it, but this gives you a great parameter to start tunnig your database memory use.


    Adrian.
    Friday, October 21, 2011 9:22 PM