And so it begins – Optober. It’s a month of talking about SQL Server configuration options. Why would I do such a thing? Well partly because there’s a bunch of them I want to learn more about and it gives me a chance to do some fun testing. Partly because I want to force myself to blog regularly this month and mostly because I have a bunch of study to do for my next exam, and this strikes me as an excellent way to procrastinate.
In SQL Server 2014 there are 70 SQL options available under sp_configure(If you only see 18 and don’t know what I’m talking about then proceed to lesson 1 – show advanced options), so we’ll probably take a little longer than a month to get through them, but lets see. The Options are listed below – as I address each one I’ll update this post with a hyperlink.
- access check cache bucket count
- access check cache quota
- Ad Hoc Distributed Queries
- affinity I/O mask
- affinity mask
- affinity64 I/O mask
- affinity64 mask
- Agent XPs
- allow updates
- backup checksum default
- backup compression default
- blocked process threshold (s)
- c2 audit mode
- clr enabled
- common criteria compliance enabled
- contained database authentication
- cost threshold for parallelism
- cross db ownership chaining
- cursor threshold
- Database Mail XPs
- default full-text language
- default language
- default trace enabled
- disallow results from triggers
- EKM provider enabled
- filestream access level
- fill factor (%)
- ft crawl bandwidth (max)
- ft crawl bandwidth (min)
- ft notify bandwidth (max)
- ft notify bandwidth (min)
- index create memory (KB)
- in-doubt xact resolution
- lightweight pooling
- locks
- max degree of parallelism
- max full-text crawl range
- max server memory (MB)
- max text repl size (B)
- max worker threads
- media retention
- min memory per query (KB)
- min server memory (MB)
- nested triggers
- network packet size (B)
- Ole Automation Procedures
- open objects
- optimize for ad hoc workloads
- PH timeout (s)
- precompute rank
- priority boost
- query governor cost limit
- query wait (s)
- recovery interval (min)
- remote access
- remote admin connection
- remote login timeout (s)
- remote proc trans
- remote query timeout (s)
- Replication XPs
- scan for startup procs
- server trigger recursion
- set working set size
- show advanced options
- SMO and DMO XPs
- transform noise words
- two digit year cutoff
- user connections
- user options
- xp_cmdshell