Post reply

SQL 2005 std x64 tuneing and page file... HOW TO BUILD THE BEST SQL SERVER?

  • January 31, 2007 at 12:53 pm

    #172106

    OK help me out here.  If your reading a new server and planning it to be running 3 databases, One that's 2gb one that's 5gb and another that's 2bg  How much RAM should that system have? 

     

    Then if you have X RAM in the system... I'll guess at 12gb 10 for SQL 2005 and 2gb for windwos 2003 rc2 std x64, what should you page file access look like?  Should it be small? 

     

    What steps would one have or should do to run best?  Other then the obvious keep your server just a SQL server.  Anything else you want to add?  Tips and the lot? 

     

    Again if this is in the wrong spot let me know... I'm new on here.

     

    Thanks,

    Sean~

  • January 31, 2007 at 7:47 pm

    #687013

    Memory is not dependent on database size...

    I have some servers which supports 200+ GB database with only 2GB RAM...

    Read the following articles..

    http://www.sql-server-performance.com/awe_memory.asp

    http://support.microsoft.com/kb/274750

    http://www.sqlmag.com/Article/ArticleID/44557/sql_server_44557.html

     

     

    MohammedU
    Microsoft SQL Server MVP

  • February 1, 2007 at 8:59 am

    #687123

    Two things... One I know SQL doesn't NEED more memory but will it perform better with more?

    Second all your links were related to a non 64 bit OS and APP.  So again your using the AWE and there's a lot of talk about that not giving a return on investment compaired to the OS and APP being able to directly address the memory above 4gb.  There is a hit when your going in and out of the upper memory swap. 

    Also I would love to note that I'm not a subscriber to SQL mag so I coun't read the story that was in the 3rd link.

     

  • February 2, 2007 at 5:53 am

    #687382

    We can all spend our days posting links and showing we don't read the questions !!

    Well to be honest I get as much memory as I can - if I can make the two values match then great as it will reduce physical io and improve read performance.

    64 bit has a flat memory model so more memory is even better than in 32 bit where largely only the data cache benefited. Page file you'll probaly need to make the size of the physcial memory. Depends upon your setup, generally in the organisations i work in there is an infrastructure team so they work to the book of page file = memory. Up to 8gb I think that's probaly fair, I'd usually stop at 8gb and turn off all that memory dump stuff and make the page file fixed in size on one drive ( ideally a raid 10 for performance ) I ran with 16gb page file with 32gb ram and have a 24gb ram server with 18gb page file ( most is never ever used ) these are 32 bit systems - the 64bit isn't on line yet but I expect the infrastructure team will make page file = memory.

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

  • February 5, 2007 at 9:29 am

    #687810

    SQL 2005 x64 will definately perform better with more memory.  If your databases add up to 9GB and you put them on a system with 16GB RAM, it can hold all the databases in RAM and still have plenty of room left over to work.

    You probably want to keep the page file small.  SQL does a lot of complicated memory management, you really don't want the OS screwing it up by swapping out buffer pages.  A SQL Server system with 32GB RAM will happily grab over 30GB for buffer pool, procedure cache, and other SQL Server-managed uses (unless you limit the max server memory), and would probably work fine with a 4GB page file for the OS and the SQL executables.  If you have other large executables on the server you might want to go larger, but don't include SQL data memory usage in your estimation of page file requirements.

  • May 8, 2007 at 9:22 am

    #704968

    We have 6 instances running on a single server.

    SQL 2005 x64 Standard. Win 2003 Standard.

    8 gig ram. 170 gig raid 5 HD array

    This is an upgrade from SQL 2000 x32 2 gig.Win 2k.

    What would be the optimum way to set the memory for each instance?

    Would a large page file also give addition performance improvement?

    We are in the process of redesigning the database structure so that the instances can be resolved to a single instance with multiple databases. When that happens, what would be the changes to the menory and page file steeing that you recommended above to make the single instance more effecient?

    Thanks in advance,

    Robert

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

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