Show Menu
Cheatography

SnDO SQL Server 2012 Cheat Sheet by

tugas MABD kelas D oleh MOH. HASAN BASRI

Server­-level Options

 

opt/mi­n/m­ax/def

 

Available Server­-level Options

-access check cache bucket count /0/1­6384/0
-access check cache quota­/0­/21­474­83647/0
-ad hoc distri­buted querie­s­/0/1/0
-aff­inity I/O mask /-21­474­836­48/­214­748­647/0
-aff­inity64 I/O mask /-21­474­836­48/­214­748­647/0
-Agent XPs/­0/1/0
-allow updates /0/1/0
-backup compre­ssion defaul­t­/0/1/0
-blocked process thresh­old­/­0/8­6400/0
-c2 audit mode /0/1/0
-clr enable­d­/0/1/0
-common criteria compliance enabled /0/1/0
-con­tained database authen­tic­ati­on­0/-/0
-cost threshold for parall­eli­sm­/0­/32­767/5
-cross db ownership chaini­ng­/0/1/0
-cursor thresh­old­/­-1/­214­748­3647/-1
-Dat­abase Mail XPs /0/1/0
-default full-text langua­ge­/0­/21­474­836­47/1033
-default langua­ge­/0­/9999/0
-default trace enable­d­/0/1/1
-dis­allow results from trigge­rs­/0/1/0
-EKM provider enable­d­/0/1/0
-fil­est­rea­m_a­cce­ss_­lev­el­/0/2/0
-fill factor /0/100/0
-ft crawl bandwidth (max)­/0­/32­767/100
-ft crawl bandwidth (min)­/0­/32­767/0
-ft notify bandwidth (max)­/0­/32­767/100
-ft notify bandwidth (min)­/0­/32­767/0
-index create memory­/­704­/21­474­83647/0
-in-­doubt xact resolu­tio­n­/0/2/0
-lig­htw­eight poolin­g­/0/1/0
-loc­ks­/5­000­/21­478­3647/0
-max degree of parall­eli­sm­/0­/32­767/0
-max full-text crawl range /0/256/4
-max server memory­/­16/­214­7483647
-max text repl size­/0/­214­748­364­7/65536
-max worker threads /128/3­2767/0
-media retention /0/365/0
-min memory per query /512­/21­474­836­47/1024
-min server memory­/­0/2­147­483­647/0
-nested trigge­rs­/0/1/1
-network packet size /512­/32­767­/4096
-Ole Automation Procedures /0/1/0
-open objects /0/2­147­483­647/0
-opt­imize for ad hoc workloads /0/1/0
-PH_­timeout /0/3­600/60
-pre­compute rank /0/1/0
-pri­ority boost­/0/1/0
-query governor cost limit­/0­/21­474­83647/0
-query wait­/-1­/21­474­836­47/-1
-rec­overy interv­al­/0­/32­767/0
-remote access /0/1/1
-remote admin connec­tio­ns­/0/1/0
-remote login timeou­t­/0/­214­748­3647/10
-remote proc trans­/0/1/0
-remote query timeou­t­/0/­214­748­364­7/600
-Rep­lic­ation XPs Option /0/1/0
-scan for startup procs­/0/1/0
-server trigger recurs­ion­/­0/1/1
-set working set size /0/1/0
-show advanced option­s­/0/1/0
-SMO and DMO XPs /0/1/1
-tra­nsform noise words /0/1/0
-two digit year cutoff /175­3/9­999­/2049
-user connec­tions /0/3­2767/0
-user option­s­/0/­32767/0
-xp_­cmd­shell /0/1/0
 

How to Change Server­-level Options

GUI (some options)
sp_Co­nfi­gure for basic options
sp_Co­nfi­gure for advanced options
First, turn on Show Advanced Options
Then list them
Then activate desired option(s)
RECO­NFI­GURE
Nota­ble: authen­tic­ation mode can’t be switched this way but xp_reg­write can be used

Databa­se-­level Options

 

option­s/d­efault

 

Auto options

AUTO_­CLO­SE­/­True
AUTO_­CRE­ATE­_ST­ATI­STI­CS­/­True
AUTO_­UPD­ATE­_ST­ATI­STI­CS­/­True
AUTO_­SHRINK /Fa­lse
Auto_­Upd­ate­_St­ati­sti­cs_­Asy­nch­ron­ous­ly­/­False

Cursor options

CURSO­R_C­LOS­E_O­N_C­OMM­IT­/­OFF
CURSO­R_D­EFA­ULT­/­GL­OBAL

Database availa­bility options

OFFLINE | ONLINE | EMERGE­NCY­/­ON­LINE
READ_ONLY | READ_W­RIT­ER­EAD­_WR­ITE
SINGL­E_USER | RESTRI­CTE­D_USER | MULTI_­USE­RM­ULT­I_U­SER

Date Correl­ation Optimi­zation Options

DATE_­COR­REL­ATI­ON_­OPT­IMI­ZAT­ION­/­OFF

External access options

DB_CH­AIN­ING­/­OFF
TRUST­WOR­THY­/­OFF

Parame­ter­ization options

PARAM­ETE­RIZ­ATI­ON­/­SIM­PLE

Recovery options

RECOV­ERY­/­FULL
PAGE_­VER­IFY­/­CH­ECK­SUM

Service Broker options

ENABL­E_B­ROKER | DISABL­E_B­ROKER | NEW_BROKER | ERROR_­BRO­KER­_CO­NVE­RSA­TIO­NS­/­ENA­BLE­_BR­OKER

Snapshot isolation options

ALLOW­_SN­APS­HOT­_IS­OLA­TIO­NOFF
READ_­COM­MIT­TED­_SN­APS­HOT­/­OFF

SQL Options

READ_­COM­MIT­TED­_SN­APS­HOT­/­OFF
ANSI_­NULLS /OFF
ANSI_­PAD­DIN­GOFF
ANSI_­WAR­NIN­GS­/­OFF
ARITH­ABO­RT­/­OFF
CONCA­T_N­ULL­_YI­ELD­S_N­ULL­/­OFF
QUOTE­D_I­DEN­TIF­IER­/­OFF
NUMER­IC_­ROU­NDABORT /OFF
RECUR­SIV­E_T­RIGGERS /OFF

How to Change Server and Database Options

Code
ALTER DATABASE DBx SET ____
GUI
Can’t change every option
               

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet
          SQL Server / Rahadiyan Danar A - 5212100124 Cheat Sheet
          SQL Server Danar124 Cheat Sheet