SQL Server Default Configuration

  • Hi,

    We are running our sql server on default configuration as per below, I want to know whats changes we have to do for performance point of view on our Database server.

    Please give me some suggestion because its urgent requirement for our client.


    access check cache bucket count06553600

    access check cache quota0214748364700

    Ad Hoc Distributed Queries0100

    affinity I/O mask-2147483648214748364700

    affinity mask-2147483648214748364700

    affinity64 I/O mask-2147483648214748364700

    affinity64 mask-2147483648214748364700

    Agent XPs0111

    allow updates0100

    awe enabled0100

    blocked process threshold (s)08640000

    c2 audit mode0100

    clr enabled0100

    cost threshold for parallelism03276755

    cross db ownership chaining0100

    cursor threshold-12147483647-1-1

    Database Mail XPs0100

    default full-text language0214748364710331033

    default language0999900

    default trace enabled0111

    disallow results from triggers0100

    filestream access level0200

    fill factor (%)010000

    ft crawl bandwidth (max)032767100100

    ft crawl bandwidth (min)03276700

    ft notify bandwidth (max)032767100100

    ft notify bandwidth (min)03276700

    index create memory (KB)704214748364700

    in-doubt xact resolution0200

    lightweight pooling0100


    max degree of parallelism06400

    max full-text crawl range025644

    max server memory (MB)1621474836472662426624

    max text repl size (B)-121474836476553665536

    max worker threads1283276700

    media retention036500

    min memory per query (KB)512214748364710241024

    min server memory (MB)02147483647016

    nested triggers0111

    network packet size (B)5123276740964096

    Ole Automation Procedures0100

    open objects0214748364700

    optimize for ad hoc workloads0100

    PH timeout (s)136006060

    precompute rank0100

    priority boost0100

    query governor cost limit0214748364700

    query wait (s)-12147483647-1-1

    recovery interval (min)03276700

    remote access0111

    remote admin connections0100

    remote login timeout (s)021474836472020

    remote proc trans0100

    remote query timeout (s)02147483647600600

    Replication XPs0100

    scan for startup procs0100

    server trigger recursion0111

    set working set size0100

    show advanced options0111

    SMO and DMO XPs0111

    SQL Mail XPs0100

    transform noise words0100

    two digit year cutoff1753999920492049

    user connections03276700

    user options03276700


  • None.

    Seriously, don't change anything unless you have a specific reason to change it. Most of the defaults are good. You've already changed max server memory which is one of the very few settings that should be changed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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