Show Menu

SnDO SQL Server 2012 Cheat Sheet by

tugas MABD kelas D oleh MOH. HASAN BASRI
sql     server     options     2012     serverdatabase

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

Download the SnDO SQL Server 2012 Cheat Sheet

2 Pages
//media.cheatography.com/storage/thumb/abas060_sndo-sql-server-2012.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 

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 Danar124 Cheat Sheet
          SQL Server / Rahadiyan Danar A - 5212100124 Cheat Sheet