Show Menu

SQL Server / Rahadiyan Danar A - 5212100124 Cheat Sheet by

Microsoft SQL Server Cheat Sheet Server­-level Options dan database-level options
sql     server     options     options     database-level     server-level

Server­­-level Options

opt­/mi­­n/­m­a­x/d­ef

Config­uration

-access check cache bucket count /0/1­6­384/0
-access check cache quota­ /0­/21­­47­4­8­3647/0
-ad hoc distri­­buted querie­s ­/0/1/0
-aff­­inity I/O mask /-21­4­74­­836­­48­/­2­14­­748­­647/0
-aff­­in­ity64 I/O mask /-21­4­74­­836­­48­/­2­14­­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
-audit mode /0/1/0
-clr enable­­d­ /0/1/0
-common criteria compliance enabled /0/1/0
-con­­tained database authen­­ti­c­a­ti­­on­ 0/-/0
-cost threshold for parall­­el­i­sm­ /0­/32­­767/5
-cross db ownership chaini­­ng­ /0/1/0
-cursor thresh­­old­ /­-1/­­214­­74­8­3­647/-1
-Dat­­abase Mail XPs /0/1/0
-default full-text langua­­ge­ /0­/21­­47­4­8­36­­47/1033
-default langua­­ge ­/0­/9­999/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­­es­t­r­ea­­m_a­­cc­e­s­s_­­lev­­el­ /0/2/0
-fill factor /0/100/0
-ft crawl bandwidth (max)­ /0­/32­­76­7/100
-ft crawl bandwidth (min)­ /0­/32­­767/0
-ft notify bandwidth (max)­ /0­/32­­76­7/100
-ft notify bandwidth (min)­ /0­/32­­767/0
-index create memory ­/­704­­/2­1­4­74­­83647/0
-in-­­doubt xact resolu­­ti­o­n­ /0/2/0
-lig­­ht­w­eight poolin­­g­ /0/1/0
-loc­­ks­ /5­000­­/2­1­4­78­­3647/0
-max degree of parall­­el­i­sm­ /0­/32­­767/0
-max full-text crawl range /0/256/4
-max server memory­ /­16/­­214­­74­83647
-max text repl size­ /0/­21­4­7­48­­364­­7/­65536
-max worker threads /128/3­­2767/0
-media retent­ion /0/365/0
-min memory per query /512­/­21­­474­­83­6­4­7/1024
-min server memory­ /­0/2­­147­­48­3­647/0
-nested trigge­­rs­ /0/1/1
-network packet size /512­/­32­­767­­/4096
-Ole Automation Proced­ures /0/1/0
-open objects /0/2­1­47­­483­­647/0
-opt­­imize for ad hoc worklo­ads /0/1/0
-PH_­­ti­meout /0/3­6­00/60
-pre­­co­mpute rank /0/1/0
-pri­­ority boost­ /0/1/0
-query governor cost limit­ /0­/21­­47­4­8­3647/0
-query wait­ /-1­/2­1­4­74­­836­­47/-1
-rec­­overy interv­­al­ /0­/32­­767/0
-remote access /0/1/1
-remote admin connec­­ti­o­ns­ /0/1/0
-remote login timeou­­t­ /0/­21­4­7­48­­3647/10
-remote proc trans­ /0/1/0
-remote query timeou­­t­ /0/­21­4­7­48­­364­­7/600
-Rep­­li­c­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­­ti­ons /0/3­2­767/0
-user option­­s­ /0/­32­767/0
-xp_­­cm­d­s­hell /0/1/0
 

Databa­se-­Level Config­uration

opt­/st­ate­ment

AUTO OPTIONS

AUTO_­­CLO­­SE­­/­True
AUTO_­­CRE­­AT­E­_­ST­­ATI­­ST­I­C­S­/­­True
AUTO_­­UPD­­AT­E­_­ST­­ATI­­ST­I­C­S­/­­True
AUTO_­­SHRINK /Fa­lse
Auto_­­Upd­­at­e­_­St­­ati­­st­i­c­s_­­Asy­­nc­h­r­on­­ous­­ly­­/­­False

Database Mirroring Options

PARTNER / FAILOVER
PARTNER / FORCE_­SER­VIC­E_A­LLO­W_D­ATA­_LOSS
PARTNER / OFF
PARTNER / RESUME
PARTNER / SAFETY OFF
PARTNER / SUSPEND
PARTNER / TIMEOUT 20
WITNESS / OFF

Date Correl­ation Optimi­zation Option

DATE_C­ORR­ELA­TIO­N_O­PTI­MIZ­ATION / ON

Emergency Mode Option

PAGE_V­ERIFY / TORN_P­AGE­_DE­TECTION
PAGE_V­ERIFY / CHECKSUM

PAGE VERIFY Option

PAGE_V­ERIFY / TORN_P­AGE­_DE­TECTION
PAGE_V­ERIFY / CHECKSUM

PARAME­TER­IZATION

PARAME­TER­IZATION / FORCED
PARAME­TER­IZATION / SIMPLE

Service Broker Options

SET NEW_BROKER
SET ENABLE­_BROKER
SET ERROR_­BRO­KER­_CO­NVE­RSA­TIONS
SET TRUSTW­ORTHY ON
SET DISABL­E_B­ROKER

SQL Options

READ_­­COM­­MI­T­T­ED­­_SN­­AP­S­H­OT­­/­OFF
ANSI_­­NULLS /OFF
ANSI_­­PAD­­DI­N­G­/­OFF
ANSI_­­WAR­­NI­N­G­S­/­OFF
ARITH­­ABO­­RT­­/­OFF
CONCA­­T_N­­UL­L­_­YI­­ELD­­S_­N­U­LL­­/­OFF
QUOTE­­D_I­­DE­N­T­IF­­IER­­/­OFF
NUMER­­IC_­­RO­U­N­DABORT /OFF
RECUR­­SIV­­E_­T­R­IGGERS /OFF

CURSOR OPTIONS

CURSO­­R_C­­LO­S­E­_O­­N_C­­OM­M­I­T­/­OFF
CURSO­­R_D­­EF­A­U­LT­­/­G­L­OBAL

Change Server­-Level Options

- New query Using Transact-SQL (sp-configure)

Use AdventureWorks2012;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'fill factor', 100;
GO
RECONFIGURE;
GO

Change Databa­se-­Level Options

ALTER DATABASE <database_name> SET <options> <statement>

Download the SQL Server / Rahadiyan Danar A - 5212100124 Cheat Sheet

2 Pages
//media.cheatography.com/storage/thumb/danar124_sql-server-rahadiyan-danar-a-5212100124.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

          More Cheat Sheets by danar124

          SQL Server Danar124 Cheat Sheet