Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Question Unanswered: Best SQL Server properties

    Hi,

    I want to know what are the best settings for my SQL server.
    The Server has 4 Intex XEON CPU with 2,66 GHZ and 32 GB RAM.
    OS: Windows Server 2003 R2 x64
    Database: SQL Server 2005 (9.00.4053.00)

    Memory settings:
    I think I will use 30 GB for maximum server memory, to reserver 2GB for the OS, is this okay?
    Other memory options I leave to default

    Processors:
    Can I use the default settings (Automatically set I/O affinity and processer affinity)? Or should I change something here?


    Advanced:
    Actual the setting for Max Degree of Parallelism is set to 1. Should this value be changed?

    Are there any other settings I should make?

    Thanks for your help,

    Best regards,
    Peter

  2. #2
    Join Date
    Oct 2011
    Posts
    29
    For 32 GB memory, I'd keep atleast 4 GB for OS.
    Processor affinity and IO affinity should be left to defaults.

    Maxdop - You can leave it to default (0). check if CXPACKET wait type is the topmost wait type. If yes, you can reduce the maxdop to (number of physical cores/2) or even lower.

  3. #3
    Join Date
    Nov 2011
    Posts
    3
    Quote Originally Posted by sqlserverdba1 View Post
    For 32 GB memory, I'd keep atleast 4 GB for OS.
    Processor affinity and IO affinity should be left to defaults.

    Maxdop - You can leave it to default (0). check if CXPACKET wait type is the topmost wait type. If yes, you can reduce the maxdop to (number of physical cores/2) or even lower.
    Hi,

    thanks for your answer.
    Do I have to restart the server when I change the maxdop value?

  4. #4
    Join Date
    Oct 2011
    Posts
    29
    No. This does not require restart of the services. Make sure to run RECONFIGURE after changing the setting.

    you can also set maxdop setting at query level that overrides server level setting.

    Code:
    option maxdop=1

  5. #5
    Join Date
    Nov 2011
    Posts
    3

    Thumbs up

    HI,

    thanks for your help.

    Have a nice weekend.

Posting Permissions

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