Home » Blog » The Server Configuration Page

The Server Configuration Page

2014-06-11 - DMVs & CVs, General, Series, SQL Server Internals, Storage Wednesday

Introduction

There is one page type that is quite unique. It is page type 14. Page type 14 is in fact so unique, that there is only one page of this type per SQL Server instance. There seems to be no official name known for this page type, so I will just call it the "Server Configuration Page".

The Server Configuration Page

The Server Configuration Page is always stored in page 10 of the first data file in the master database. Before we talk about what is stored in this page, let us take a quick peek at one.

[sql] USE master;
GO
DBCC TRACEON(3604);
DBCC PAGE(0,1,10,3);
[/sql]

As with the other page types, using DBCC PAGE with mode 3 returns a quite readable output:

The complete output on my demo instance looks like this:

[sourcecode] cconfsz = 8 cmajor = 10 cminor = 0
crevision = 16 cbootsource = 2 crecinterval = 0
ccatalogupdates = 0 cmbSrvMemMin = 16 cmbSrvMemMax = 2147483647
cusrconnections = 0 cnetworkpcksize = 4096 ckbIndexMem = 0
cfillfactor = 0 ctapreten = 0 cwritestatus = 0
cfgpriorityboost = 0x0 cfgexposeadvparm = 0x1 cfglogintime = 10
cfgpss = 0 cfgpad = 4096 cfgxdes = 16
cfgaffinitymask = 0 cfgaffinitymask64 = 0 cfgIOAffinityMask = 0
cfgIOAffinity64Mask = 0 cfgbuf = 4362 cfgdes = 0
cfglocks = 0 cfgquerytime = 600 cfgcursorthrsh = -1
cfgrmttimeout = 10 cfg_dtc_rpcs = 0 cclkrate = 31250
cfg_max_text_repl_size = 65536 cfgupddate = 41782 cfgupdtime = 23522824
fRemoteAccess = 1 cfgbufminfree = 331 cnestedtriggers = 0x1
cdeflang = 0 cfgTransformNoiseWords = 0x0 cfgPrecomputeRank = 0x0
crossdbownchain = 1 cidCfg = 0x3400d008 cidCfgOld = 0x3400d008
cfgCutoffYear = 2049 cfgLangNeutralFT = 1033 maxworkthreads = 0
minworkthreads = 32 minnetworkthreads = 32 threadtimeout = 15
connectsperthread = 0 cusroptions = 0 exchcostthreshold = 5
maxdop = 0 cpwdpolicyupgrade = 0x1 cfServerTriggerRecursion = 1
cfDisableResultsetsFromTriggers = 0 cfgPHConnectTimeout = 60 CLREnabled = 1
cfgmaxcrawlrange = 4 ftSmallBufMin = 0 ftSmallBufMax = 100
ftLargeBufMin = 0 ftLargeBufMax = 100 RemoteDacEnabled = 0
CommCritComplianceEnabled = 0 EkmEnabled = 0 cUserInstanceTimeout = 0x3c
cfgEnableUserInstances = 0x1 m_BackupCompressionDefault = 0x0 FilestreamAccessLevel = 1
OptimizeForAdhocWorkloads = 0 cchecksum = 1152
[/sourcecode]

If you look closely at the names, you might notice some that look familiar. In fact, these values are the configuration values you can set with sp_configure.

sp_configure and sys.configurations

SQL Server comes with a collection of user-settable configuration values. These values range from the max memory setting to the processor affinity mask. To change any of these values you can use the sp_configure stored procedure. To see the current values you can use the following query against the sys.configurations DMV:

[sql] SELECT C.configuration_id,
C.name,
C.value,
C.minimum,
C.maximum,
C.value_in_use,
C.description,
C.is_dynamic,
C.is_advanced
FROM sys.configurations AS C;
[/sql]

Below is the output of the name and value columns on the same demo instance:

[sourcecode] name value
----------------------------------- ----------
access check cache bucket count 0
access check cache quota 0
Ad Hoc Distributed Queries 0
affinity I/O mask 0
affinity mask 0
affinity64 I/O mask 0
affinity64 mask 0
Agent XPs 0
allow updates 0
backup compression default 0
blocked process threshold (s) 0
c2 audit mode 0
clr enabled 1
common criteria compliance enabled 0
contained database authentication 1
cost threshold for parallelism 5
cross db ownership chaining 0
cursor threshold -1
Database Mail XPs 0
default full-text language 1033
default language 0
default trace enabled 1
disallow results from triggers 0
EKM provider enabled 0
filestream access level 1
fill factor (%) 0
ft crawl bandwidth (max) 100
ft crawl bandwidth (min) 0
ft notify bandwidth (max) 100
ft notify bandwidth (min) 0
index create memory (KB) 0
in-doubt xact resolution 0
lightweight pooling 0
locks 0
max degree of parallelism 0
max full-text crawl range 4
max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 0
media retention 0
min memory per query (KB) 1024
min server memory (MB) 0
nested triggers 1
network packet size (B) 4096
Ole Automation Procedures 0
open objects 0
optimize for ad hoc workloads 0
PH timeout (s) 60
precompute rank 0
priority boost 0
query governor cost limit 0
query wait (s) -1
recovery interval (min) 0
remote access 1
remote admin connections 0
remote login timeout (s) 10
remote proc trans 0
remote query timeout (s) 600
Replication XPs 0
scan for startup procs 0
server trigger recursion 1
set working set size 0
show advanced options 1
SMO and DMO XPs 1
transform noise words 0
two digit year cutoff 2049
user connections 0
user options 0
xp_cmdshell 1

(69 row(s) affected)
[/sourcecode]

(Do not look at these values for best practice advice. In fact some are set against all recommendations.)

The output contains 69 different configuration values. The Server Configuration Page on the other hand contains 74 values. So there are a few in there that you cannot change through T-SQL. cclkrate = 31250 for example is one of those values. It looks like it has something to do with a clock rate. However, I do not know any more details about this or any of the other values that are not exposed through sys.configurations.

Summary

SQL Server comes with 69 configuration values. These values are stored in the Server Configuration Page which is always located in page 10 of the first data file in the master database. That page also stores a handful of additional configuration values but their functionality is not documented.