SQL Server Default Configuration

  • D.K.Mishra

    SSChasing Mays

    Points: 601

    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.

    name minimum maximum config_value run_value

    access check cache bucket count 0 65536 0 0

    access check cache quota 0 2147483647 0 0

    Ad Hoc Distributed Queries 0 1 0 0

    affinity I/O mask -2147483648 2147483647 0 0

    affinity mask -2147483648 2147483647 0 0

    affinity64 I/O mask -2147483648 2147483647 0 0

    affinity64 mask -2147483648 2147483647 0 0

    Agent XPs 0 1 1 1

    allow updates 0 1 0 0

    awe enabled 0 1 0 0

    blocked process threshold (s) 0 86400 0 0

    c2 audit mode 0 1 0 0

    clr enabled 0 1 0 0

    cost threshold for parallelism 0 32767 5 5

    cross db ownership chaining 0 1 0 0

    cursor threshold -1 2147483647 -1 -1

    Database Mail XPs 0 1 0 0

    default full-text language 0 2147483647 1033 1033

    default language 0 9999 0 0

    default trace enabled 0 1 1 1

    disallow results from triggers 0 1 0 0

    filestream access level 0 2 0 0

    fill factor (%) 0 100 0 0

    ft crawl bandwidth (max) 0 32767 100 100

    ft crawl bandwidth (min) 0 32767 0 0

    ft notify bandwidth (max) 0 32767 100 100

    ft notify bandwidth (min) 0 32767 0 0

    index create memory (KB) 704 2147483647 0 0

    in-doubt xact resolution 0 2 0 0

    lightweight pooling 0 1 0 0

    locks 5000 2147483647 0 0

    max degree of parallelism 0 64 0 0

    max full-text crawl range 0 256 4 4

    max server memory (MB) 16 2147483647 26624 26624

    max text repl size (B) -1 2147483647 65536 65536

    max worker threads 128 32767 0 0

    media retention 0 365 0 0

    min memory per query (KB) 512 2147483647 1024 1024

    min server memory (MB) 0 2147483647 0 16

    nested triggers 0 1 1 1

    network packet size (B) 512 32767 4096 4096

    Ole Automation Procedures 0 1 0 0

    open objects 0 2147483647 0 0

    optimize for ad hoc workloads 0 1 0 0

    PH timeout (s) 1 3600 60 60

    precompute rank 0 1 0 0

    priority boost 0 1 0 0

    query governor cost limit 0 2147483647 0 0

    query wait (s) -1 2147483647 -1 -1

    recovery interval (min) 0 32767 0 0

    remote access 0 1 1 1

    remote admin connections 0 1 0 0

    remote login timeout (s) 0 2147483647 20 20

    remote proc trans 0 1 0 0

    remote query timeout (s) 0 2147483647 600 600

    Replication XPs 0 1 0 0

    scan for startup procs 0 1 0 0

    server trigger recursion 0 1 1 1

    set working set size 0 1 0 0

    show advanced options 0 1 1 1

    SMO and DMO XPs 0 1 1 1

    SQL Mail XPs 0 1 0 0

    transform noise words 0 1 0 0

    two digit year cutoff 1753 9999 2049 2049

    user connections 0 32767 0 0

    user options 0 32767 0 0

    xp_cmdshell 0 1 0 0

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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 2 (of 2 total)

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