Server tuning

  • grahamc

    SSCertifiable

    Points: 6762

    Ok, we all know that we can change SQL Server settings to improve performance and database tuning to improve specific databases.....

    Now what do we do with the servers and the OS themselves?

    Dedicated SQL Server (new box)

    Windows 2008 64bit Enterprise Edition

    SQL Server 2008 64bit Enterprise Edition

    SAN attached drives

    128GB memory

    going to be be around 600-700GBs of DBs transactional.

    My list so far is (all require researching and testing):

    pagefile - 1.5x physical memory still the base standard? obviously I dont want SQL paging

    power management

    background services

    hyperthreading

    limiting the max memory of SQL server

    What else should be added to my list?

  • Carl Federl

    One Orange Chip

    Points: 25384

    Windows:

    Insure that "Maximize data throughput for network applications" is set instead of the default of "Maximize data throughput for file sharing"

    Grant the SQL Server service account the following Windows privileges:

    Perform volume maintenance tasks

    Lock pages in memory

    Limit the size of the System File Cache because the default is half of the physical memory.

    Determine if the Windows Denial Of Service settings should be changed.

    SQL Server:

    Move TempDB to its own drive and add additional equal size data files.

    Set the Default Database Path

    Create alerts for severities 17 through 25.

    SQL = Scarcely Qualifies as a Language

  • grahamc

    SSCertifiable

    Points: 6762

    Carl Federl (10/19/2010)


    Windows:

    Insure that "Maximize data throughput for network applications" is set instead of the default of "Maximize data throughput for file sharing"

    Grant the SQL Server service account the following Windows privileges:

    Perform volume maintenance tasks

    Lock pages in memory

    Limit the size of the System File Cache because the default is half of the physical memory.

    Determine if the Windows Denial Of Service settings should be changed.

    SQL Server:

    Move TempDB to its own drive and add additional equal size data files.

    Set the Default Database Path

    Create alerts for severities 17 through 25.

    Will do some reading on the Windows items supplied... thanks

    Already doing all the SQL ones, except for the Severity messages.

  • grahamc

    SSCertifiable

    Points: 6762

    anything else?

  • jamankhan1016

    SSC Rookie

    Points: 41

    Data Tuning results from analyzing queries (using EXPLAIN or other similar tools) to identify bottlenecks and other issues slowing down processing. Tuning the database means tweaking the structure to try and speed up processing. Probably the most common method is to add additional indexes to often-used tables. Analysis and modification of logging files may also increase performance, as may replacing triggers and constraints with explicitly-invoked procedures. Less frequent methods may include denormalization of nonvolatile data to eliminate need for joins.

    Online shopping at Chinabuye for dropship electronics[/url] Service

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

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