Post reply

performance problem - sql server 2005 x64 version - memory allocation - need more information

  • July 23, 2007 at 12:20 am

    #721433

    With 5 GB memory on the server (that's a rather weird amount) I'd probably set min to 3.5 and max to 4, providing there are no other apps that run on the server.

    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

  • July 23, 2007 at 4:40 am

    #721477

    We had a similar issue and in short what was happening was that virtual memory was killing the server and eventually not allowing memory to be allocated and the server would stop all connections until it freed up memory.  See this post:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1783523&SiteID=1 

    The setting for Max memory is 2TB which is basically unlimited as far as Windows is concerned. 

    What we did after consulting with MS about our issue was to set the MAX memory for that instance to 1GB less than the total RAM for the machine.  Our box had 4GB so we set MAX Memory in SQL to 3GB and left the OS with 1GB.  Since then no more errors.  (We run SQL 2005 x64 on Windows 2003 Std x64).  We are planning on increasing the RAM significantly. 

    This assumes that there is only one instance running on the box and only used for SQL.  If you have other instances running on the box, you need to allocate accordingly for each instance so that all instance have sufficient RAM.   

    What was difficult for us is that errors weren't always generated in the log files.  Sometimes we would see the error, othertimes nothing but it would stop accepting connections for about 2 minutes and then start again. 

    SJ

    http://blog.mathomsolutions.com

    Accepting all invites

  • July 23, 2007 at 4:50 am

    #721479

    One more thing that I forgot to post.  Since you rebuilt this server and I am assuming restored your databases, it would be wise to make sure to reindex and update your db statistics.  This was also a recommendation from MS. 

    SJ

    http://blog.mathomsolutions.com

    Accepting all invites

  • July 23, 2007 at 5:34 am

    #721491

    Hi All,

    Thank you for your technical advice.

    We will implement the change this week.

    Regards,

    Parthipan

  • August 1, 2007 at 11:37 am

    #723967

    Can someone please tell me if "lock pages in memory" is supported in SQL 2005 Standard, 64 bit.  From from I can tell - it is not supported and even though you can set it up, it will not be used.  Is it only supported in Enterprise?  I am in the process of testing SQL 2005 standard 64 bit.  Its got 16GB memory and I have let SQL dynamically manage memory.  I was running some load testing this morning and basically SQL Server ran away with all the memory leaving nothing to the OS, causing the server to hang. 

     

     

     

     

     

  • August 1, 2007 at 12:31 pm

    #723978

    Vik

    It is ignored on 64 bit OS/SQL.  You can set it but it is ignored.  Doesn't matter which version -- standard or enterprise.

    Also, don't let SQL dynamically manage your RAM otherwise it grabs everything it sees and doesn't play nicely.  In the DB properties set the MAX Server Memory to something less that your total RAM. 

    Shawn Johnson

    http://blog.mathomsolutions.com

     

    Accepting all invites

  • August 1, 2007 at 12:36 pm

    #723979

    Shawn - thats what  I thought, thanks for clarifying that.

    yeah - I have left 500MB for the OS.  Will run some more tests and see how things look

     

    Have a good one.

     

    Vik.

     

  • August 1, 2007 at 4:41 pm

    #724022

    I have 1Gb for my server and it still thrashes a bit at peak periods.  Since you have 16GB, you might want to go higher for the OS.

    Shawn

    Accepting all invites

  • August 2, 2007 at 10:46 pm

    #724380

    Hi All,

    After enabling lock pages in memory and reindexing all user databases, the user commented the performance of the server is fine. As an administrator we cannot find any difference. But we can find the duration of execution of the user defined jobs is less and improved.

    Thank you all for technical advice.

    Please advice me how we have to enable 'lock pages in memory' option in 2 node active/passive custer server.

    We need to enalble 'lock pages in memory' option for sql account in both nodes of the server?

    BR,

    Parthipan

  • August 3, 2007 at 1:18 am

    #724395

    Also I found that the service account should have enough rights to lock the pages in memory on both the nodes. How can I check this permission on windows login and what rights it should have..

    Please confirm that we have to enable 'lock pages in memory' option for sql account on individual nodes of the cluster server?

    BR,

    Parthipan

  • August 3, 2007 at 2:25 am

    #724411

    As asked earlier, please confirm that lock pages in memory will work on x64bit sql server 2005 standard edition installed on active/passive server as I can find in the below msdn page that the option is not supported..

    http://msdn2.microsoft.com/en-us/library/ms190730.aspx

     

    Please clarify me.

    BR,

    Parthipan

  • August 6, 2007 at 9:50 am

    #724894

    with 64 bit sql you the MUST set the max memory for sql server, I'd suggest 1.5 to 2gb below the installed memory.

    ANY version on 64bit if you do not set max memory your server will thrash. you can largely ignore minimum memory, why would you want to decrease memory from SQL Server ? and to what? assuming it's a dedicated server.

    sql server should not use the page file - set a fixed size and forget about it.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 12 posts - 16 through 26 (of 26 total)

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