Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2005
    Posts
    75

    Unanswered: 2005 x64 Cluster - memory settings

    So I'm in the middle of building SQL Server 2005 on my new cluster hardware. I've got all the goodies this time around -

    4x PowerEdge 6800
    4x dual core x64 procs in each box
    32GB ram in each box
    Windows Server 2k3 R2 Enterprise x64 SP2
    SQL 2005 Enterprise x64 SP2
    Active Active Active Passive cluster (4 boxes, 3 instances)
    dedicated data, log, and tempDB 4Gb 15k fiber channel SAN Volumes for each instance


    Now, this is the first x64 box I've configured, so I'm looking for some optimization tips for a couple things.

    First one's memory. On our 32bit systems, I always added the /PAE switch to the boot.ini file and enable AWE in SQL Server. From what I'm reading, that's no longer needed with the enhanced memory addressing. Is that the case? Is there an MS best practices KB doc in regards to configuration? I can't find one.

    Also, should I set anything special pagefile-wise? I know the old mantra is 1.5x system memory, but that would create a 48GB page file. Our current cluster has 6GB of ram dedicated to each instance (2k) and Perfmon tells me it's barely touching the pagefile. I'm thinking of just leaving it default, but I want to know if there is a best practice that I'm missing. Again, I can't find an MS doc that addresses this particular scenario.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    /PAE is not needed with x64.

    The paging file question is of interest to me. Mostly, I set it to a fixed size to avoid fragmentation. I might set it to something like 12 GB (fixed and distributed over 3 drive if you have them).

    Also note that SQL 2005 handles the expanded memory better than SQL 2000; you won't need to worry so much about dedicating memory to each instance (to prevent starvation issues when two instances are running on one node).

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    On a 64 bit systems (X64 or I64) you can't enable PAE in boot.ini because it makes no more sense than trying to enable EMM.SYS... PAE is for an earlier (32 bit) generation and it doesn't make sense in the 64 bit environment.

    AWE is not useful to a 64 bit application for the same reason that PAE isn't useful to the operating system. The application can address petabytes of RAM if your system can deliver it, so there is no benefit whatsoever to splitting the RAM up to simulate an older hardware workaround for not being able to address over four gigabytes.

    While I can't remember ever having seen anyone actually do it, the optimum answer for the paging files is to have one file (disk) for each CPU, and have the paging file be the only thing on that disk. This strikes me as grossly wasteful for the system that you've described, but it would give optimum performance.

    As another observation, the same reaonsing applies to database log files as to paging files. Optimum performance comes from having one log file for each CPU that could be working in a given database, and to have those log files on drives all by themselves. While this is often overkill, I actually have seen this done and the performance increases can be amazing when you find a system that is "log bound" to the point that the log is a bottleneck!

    -PatP

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    ... the optimum answer for the paging files is to have one file (disk) for each CPU
    In order to be able to properly troubleshoot issues that you may have in the future, always configure page file to reside on the boot partition. If something goes wrong, MS would not be able to adequately determine what went wrong, if they can't generate full kernel/process dump. The latter can only be done if pagefile is on the boot partition and is 1.5 times the size of physical RAM.

    Quote Originally Posted by Pat Phelan
    ... As another observation, the same reaonsing applies to database log files as to paging files
    I think you're referring to data devices of tempdb. Log devices are written sequantially, which means that no matter how many you have, only 1 is being written to, until it's full. Data devices, on the other hand, are written randomly, and have a much more complex structure. Introducing multiple data devices for tempdb reduces contention on GAM/SGAM pages by increasing their number (1 per device or 4GB of data).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by jeebustrain
    Also, should I set anything special pagefile-wise? I know the old mantra is 1.5x system memory, but that would create a 48GB page file.
    For database servers, I don't use a large pagefile. Sure, you want a page file to page out application memory, etc. But do you want to page out your database data? I pose this as a question to users who have experience in this area. About five years ago I had a system with 4GB of ram serving a database around 50GB. I tested the system with various pagefile sizes. I actually found the sweet spot to be around 1GB! My conclusion was that the database server could more efficiently retrieve data from the database files than from the OS swapfile. One thing I noted was the increased disk I/O (write bytes) with a larger swapfile. With a smaller swapfile, the database server would discard older data in its cache instead of writing to a swapfile.

    I have not tested this scenario on any configuration since my test 5 years ago.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's a very abstracted recollection that you posted here. 5 years ago many additional factors might have come to play for you to come up with such conslusion, such as version and build# (obvious), physical drives, device placements, pagefile location, to name a few. But mostly, when you were testing it, you grossly discarded concurrency factor while applauding the performance of re-reading the data vs. retrieving it from memory (even if it is in pagefile). De-serialization occurs in both cases, but it's more intense when when done from dirrectly from data devices, because before SQL sees the data, it has to put it into memory first. If you tested it with multiple users doing the same test, - your conclusion would have been different.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Aug 2005
    Posts
    75
    Thanks guys. It's still a bit of a work in progress.

    After I posted, I sat back and realized that the /PAE switch would be useless. I still wasn't sure about AWE though, because I've been reading some conflicting information about AWE on x64 around the net. A lot say don't do it, some say SQL Server will ignore it, and a few say that it might actually be a benefit. I wasn't sure, so I thought I'd ask.

    I've already split tempdb up on its drive. SQL Server sees 16 processors, so I set up 15 .ndf files (besides the .mdf) and set them at 2GB per file. I also hard set the .ldf at 8GB. It's way overkill (tempdb on the exisiting system rarely gets above 5GB), but they gave me an 80GB volume to work with, so I figured I'd use it.

    As far as splitting things like user DB logs up, that's going to be a bit tough. The environment is an ASP type environment, hosting multiple instances of the same application DB for different customers. We have close to 300 user databases on the system, ranging from 500MB to 70GB. I've got just under 1TB of total DB files, spread across 3 instances. I know you might have been just talking about an "ideal" scenario, but that's obviously not going to work (300x16= too many drives for me to deal with .

    So is there a consensus on the pagefile question? My boot partition is actually only about 20GB, so having a 49GB pagefile on boot is out of the question. I could move it to D:\ (where the SQL Server binaries are), but I don't want to create a pagefile that big if it's not going to actually buy me anything.

    Also, so you think that hard allocating memory on the boxes to SQL is a bad idea? I've always read otherwise. I've got another 2k5 cluster (32bit), albeit a lot less utilized, and I've never had any issues. Is there an MS KB that points to optimal memory settings for 2k5 x64? I can't find any (either in BOL or anywhere else). My original plan was to hard allocate 15GB memory to each instance, that way I could host a maximum of 2 instances on one box. I'm not anticipating a failure of 3 boxes in this cluster, but should such a scenario arise, I should be able to get back in to alter the memory settings manually before it would come to that.
    Last edited by jeebustrain; 04-18-08 at 11:12.

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by jeebustrain
    Thanks guys. It's still a bit of a work in progress.

    After I posted, I sat back and realized that the /PAE switch would be useless. I still wasn't sure about AWE though, because I've been reading some conflicting information about AWE on x64 around the net. A lot say don't do it, some say SQL Server will ignore it, and a few say that it might actually be a benefit. I wasn't sure, so I thought I'd ask.

    I've already split tempdb up on its drive. SQL Server sees 16 processors, so I set up 15 .ndf files (besides the .mdf) and set them at 2GB per file. I also hard set the .ldf at 8GB. It's way overkill (tempdb on the exisiting system rarely gets above 5GB), but they gave me an 80GB volume to work with, so I figured I'd use it.

    As far as splitting things like user DB logs up, that's going to be a bit tough. The environment is an ASP type environment, hosting multiple instances of the same application DB for different customers. We have close to 300 user databases on the system, ranging from 500MB to 70GB. I know you might have been just talking about an "ideal" scenario, but that's obviously not going to work (300x16= too many drives for me to deal with .

    So is there a consensus on the pagefile question? My boot partition is actually only about 20GB, so having a 49GB pagefile on boot is out of the question. I could move it to D:\ (where the SQL Server binaries are), but I don't want to create a pagefile that big if it's not going to actually buy me anything.

    Also, so you think that hard allocating memory on the boxes to SQL is a bad idea? I've always read otherwise. I've got another 2k5 cluster (32bit), albeit a lot less utilized, and I've never had any issues. Is there an MS KB that points to optimal memory settings for 2k5 x64? I can't find any (either in BOL or anywhere else). My original plan was to hard allocate 15GB memory to each instance, that way I could host a maximum of 2 instances on one box. I'm not anticipating a failure of 3 boxes in this cluster, but should such a scenario arise, I should be able to get back in to alter the memory settings manually before it would come to that.
    I've read to create tempdb devices as per "physical" processor, so 2XQuad processor would call for 2 devices, not 8. I just setup a similar environment for our LoanPerformance DWH, dude, lightning fast. Used partitioned table (for 1 Billion row table), with aligned indexes. Data loads to a staging table, that gets simply switched in to the partitioned table.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When you're saying "hard allocating memory", do you mean setting min and max to the same number? It used to be done in 2K only in situations where there were other services having tendency of fragmenting physical memory by allocating and deallocating small chunks of memory at fast rate. In 2K5x64 this (so far) is not the case yet, since fewer services can truly utilize 64-bit platform to affect memory fragmentation. 2K3 OS memory manager did get improved since. You DO want to limit the max server memory per instance to ensure that surviving instances "play nice" when found on the same physical node.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by hmscott
    /PAE is not needed with x64.

    The paging file question is of interest to me. Mostly, I set it to a fixed size to avoid fragmentation. I might set it to something like 12 GB (fixed and distributed over 3 drive if you have them).

    Also note that SQL 2005 handles the expanded memory better than SQL 2000; you won't need to worry so much about dedicating memory to each instance (to prevent starvation issues when two instances are running on one node).

    Regards,

    hmscott
    hm, question if 2 of my active nodes fail over to the same passive node (64GB RAM), should each SQL Instance be configured to use a max of 32GB RAM (in the event they do failover to same node), or will SQL2K5x64 handle this automatically ?

    Thx
    Last edited by PMASchmed; 04-18-08 at 11:11.

  11. #11
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by rdjabarov
    That's a very abstracted recollection that you posted here. 5 years ago many additional factors might have come to play for you to come up with such conslusion, such as version and build# (obvious), physical drives, device placements, pagefile location, to name a few. But mostly, when you were testing it, you grossly discarded concurrency factor while applauding the performance of re-reading the data vs. retrieving it from memory (even if it is in pagefile). De-serialization occurs in both cases, but it's more intense when when done from dirrectly from data devices, because before SQL sees the data, it has to put it into memory first. If you tested it with multiple users doing the same test, - your conclusion would have been different.

    I wish I still had the test documents instead of my abstracted collection. I can tell you I was using SQL Server 2000 in a OLTP environment with about 275 concurrent users at peak time. I simulated this load in my test environment. I don't remember where I had my swap file, but the database file existed on a very fast RAID 5 SAN partition.

  12. #12
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by PMASchmed
    I've read to create tempdb devices as per "physical" processor, so 2XQuad processor would call for 2 devices, not 8. I just setup a similar environment for our LoanPerformance DWH, dude, lightning fast. Used partitioned table (for 1 Billion row table), with aligned indexes. Data loads to a staging table, that gets simply switched in to the partitioned table.

    according to this doc, MS recommends you to create one for whatever the affinity mask is set to - therefore, if SQL Server sees 16 processors, then you should use 16 tempdb files.

  13. #13
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by PMASchmed
    hm, question if 2 of my active nodes fail over to the same passive node (64GB RAM), should each SQL Instance be configured to use a max of 32GB RAM (in the event they do failover to same node), or will SQL2K5x64 handle this automatically ?

    Thx

    I've always read (and done) that if you're going to hard set memory, leave 1GB for the OS on disk (if nothing else is running on the server), and divide the remaining memory to how many instances you want to run max on that box. So if you have 64GB of ram and 2 instances, give each Instance 31GB of ram and leave the OS the remaining.

    Granted this could be completely different with 2k5x64, but this is what I've always known to be best practice.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Ideally kernel needs 2GB or RAM, and the more memory you have on the box, the more critical kernel memory becomes. In 32-bit world leaving kernel with 1GB frequently led to drastic performance issues by turning on /3GB switch.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    What's a better option for me:

    I have a blade system with a 136GB internal drive, 10000RPM (currently contains O/S, Swap, and tempdb)

    and a SAN (15000 RPM Drives, Fibre channel, clearly faster than above internal drive), that contains user DB .mdf and ldf files.

    Would I be better off moving tempdb to the SAN storage, or leaving it on a the separate, slower drive ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •