SQL Server Performance

Optimum Config Parameters

Discussion in 'Performance Tuning for Hardware Configurations' started by krajdba, Feb 7, 2005.

  1. krajdba New Member

    Hi Gurus,

    Please let me know for a Database of Size 4 GB(Complete), What should be the
    Ideal Configuration Parameters that I have to SET in a SQL Server.

    I would like to SET All those Parameters Values available for
    Maximum Performance.


    Thanks



    raj
    krajdba, Feb 7, 2005
    #2

  2. Luis Martin Moderator

    You have to provide more informations like:
    Hardware configuration (memory, cpu's, disks).
    Software: OS and SP, SQL server and SP.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



    Luis Martin, Feb 7, 2005
    #2

  3. satya Moderator

    And also it depends on how the application is developed and the clients usage.
    In general its suggested to leave the default SET settings in SQL server.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Feb 7, 2005
    #2

  4. mmarovic Active Member

    quote:In general its suggested to leave the default SET settings in SQL server.
    Agree about server settings. However, I would set db and log sizes equal to estimated max sizes.
    mmarovic, Feb 7, 2005
    #2

  5. krajdba New Member

    Hi Gurus,

    We have Pentium with single 2.4 Ghz CPU,Two SCSI Hard Disks, and 1 GB RAM.
    Windows-2000 server os and sql server 2000.

    We have a Informatica based Appication,Which runs at every half an hour.
    No of Database three.
    No of users 100+ on the same server.

    Questions:

    How can I allocate db Buffers,Redo Buffers,Kind of say Increase or Decrease
    those buffers. Like in Init Parameter file of the Oracle.

    Thanks


    raj
    krajdba, Feb 8, 2005
    #2

  6. alzdba Member

    unlike udb or oracle, sqlserver "selftunes" by default.
    Only change the settings if prooven the defaults don't perform to your needs.
    alzdba, Feb 8, 2005
    #2

  7. satya Moderator

    You need a tool to perform STRESS testing on the database when using the application, LOAD RUNNER or WINRUNNER are the suitable tools.

    And all those internal buffer settings must be tested before deploying on the production database server.

    One more thing, if you're not getting any performance problems then better to leave the settings AS IS and perform regular schedule of DBCC REINDEX and intermittent RECOMPILE of Stored PRocedures and UPDATE STATISTICS for the volatile tables for optimum performance.

    One golden rule is set DYNAMIC MEMORY settings to SQL Server.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Feb 8, 2005
    #2

  8. krajdba New Member

    Hi Gurus,


    Can you Pleas eloborate on the " DYNAMIC MEMORY Settings to SQL Server".

    Is it something that a DBA has to Set Or It happens by default
    when we Install the Sql Server.

    Thanks




    raj
    krajdba, Feb 9, 2005
    #2

  9. alzdba Member

    By default sqlserver is configured to use dynamic mamory allocation.
    It will grab as many ram as it needs, and will only free it if requested by others at the server.
    However, you can set limits (min and max) as to what volume of ram sqlserver can have at its service.
    With Enterprise manager, rightclick the servername and then select properties.
    Then you'll see a tab memory. Here you can manage these parameters.
    If not prooven it hurts, just leave it to the default settings.
    alzdba, Feb 9, 2005
    #2

  10. satya Moderator

    satya, Feb 9, 2005
    #2

Share This Page