Cheatography
https://cheatography.com
Microsoft SQL Server Cheat Sheet Server-level Options dan database-level options
Configuration
-access check cache bucket count /0/16384/0
-access check cache quota /0/2147483647/0
-ad hoc distributed queries /0/1/0
-affinity I/O mask /-2147483648/214748647/0
-affinity64 I/O mask /-2147483648/214748647/0
-Agent XPs /0/1/0
-allow updates /0/1/0
-backup compression default /0/1/0
-blocked process threshold /0/86400/0
-audit mode /0/1/0
-clr enabled /0/1/0
-common criteria compliance enabled /0/1/0
-contained database authentication 0/-/0
-cost threshold for parallelism /0/32767/5
-cross db ownership chaining /0/1/0
-cursor threshold /-1/2147483647/-1
-Database Mail XPs /0/1/0
-default full-text language /0/2147483647/1033
-default language /0/9999/0
-default trace enabled /0/1/1
-disallow results from triggers /0/1/0
-EKM provider enabled /0/1/0
-filestream_access_level /0/2/0
-fill factor /0/100/0
-ft crawl bandwidth (max) /0/32767/100
-ft crawl bandwidth (min) /0/32767/0
-ft notify bandwidth (max) /0/32767/100
-ft notify bandwidth (min) /0/32767/0
-index create memory /704/2147483647/0
-in-doubt xact resolution /0/2/0
-lightweight pooling /0/1/0
-locks /5000/214783647/0
-max degree of parallelism /0/32767/0
-max full-text crawl range /0/256/4
-max server memory /16/2147483647
-max text repl size /0/2147483647/65536
-max worker threads /128/32767/0
-media retention /0/365/0
-min memory per query /512/2147483647/1024
-min server memory /0/2147483647/0
-nested triggers /0/1/1
-network packet size /512/32767/4096
-Ole Automation Procedures /0/1/0
-open objects /0/2147483647/0
-optimize for ad hoc workloads /0/1/0
-PH_timeout /0/3600/60
-precompute rank /0/1/0
-priority boost /0/1/0
-query governor cost limit /0/2147483647/0
-query wait /-1/2147483647/-1
-recovery interval /0/32767/0
-remote access /0/1/1
-remote admin connections /0/1/0
-remote login timeout /0/2147483647/10
-remote proc trans /0/1/0
-remote query timeout /0/2147483647/600
-Replication XPs Option /0/1/0
-scan for startup procs /0/1/0
-server trigger recursion /0/1/1
-set working set size /0/1/0
-show advanced options /0/1/0
-SMO and DMO XPs /0/1/1
-transform noise words /0/1/0
-two digit year cutoff /1753/9999/2049
-user connections /0/32767/0
-user options /0/32767/0
-xp_cmdshell /0/1/0 |
|
|
Database-Level Configuration
AUTO OPTIONS
AUTO_CLOSE/True
AUTO_CREATE_STATISTICS/True
AUTO_UPDATE_STATISTICS/True
AUTO_SHRINK /False
Auto_Update_Statistics_Asynchronously/False |
Database Mirroring Options
PARTNER / FAILOVER
PARTNER / FORCE_SERVICE_ALLOW_DATA_LOSS
PARTNER / OFF
PARTNER / RESUME
PARTNER / SAFETY OFF
PARTNER / SUSPEND
PARTNER / TIMEOUT 20
WITNESS / OFF |
Date Correlation Optimization Option
DATE_CORRELATION_OPTIMIZATION / ON |
Emergency Mode Option
PAGE_VERIFY / TORN_PAGE_DETECTION
PAGE_VERIFY / CHECKSUM |
PAGE VERIFY Option
PAGE_VERIFY / TORN_PAGE_DETECTION
PAGE_VERIFY / CHECKSUM |
PARAMETERIZATION
PARAMETERIZATION / FORCED
PARAMETERIZATION / SIMPLE |
Service Broker Options
SET NEW_BROKER
SET ENABLE_BROKER
SET ERROR_BROKER_CONVERSATIONS
SET TRUSTWORTHY ON
SET DISABLE_BROKER |
SQL Options
READ_COMMITTED_SNAPSHOT/OFF
ANSI_NULLS /OFF
ANSI_PADDING/OFF
ANSI_WARNINGS/OFF
ARITHABORT/OFF
CONCAT_NULL_YIELDS_NULL/OFF
QUOTED_IDENTIFIER/OFF
NUMERIC_ROUNDABORT /OFF
RECURSIVE_TRIGGERS /OFF |
CURSOR OPTIONS
CURSOR_CLOSE_ON_COMMIT/OFF
CURSOR_DEFAULT/GLOBAL |
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 Database-Level Options
ALTER DATABASE <database_name> SET <options> <statement>
|
|
Created By
Metadata
Favourited By
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets
More Cheat Sheets by danar124