Section 13
SQL Server DBTUNE configuration parameters
Note: This topic was updated for 9.3.1.
NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only
Configuration parameters, which are stored in the parameter_name column of the SDE_dbtune table, identify the database object to be configured or denote a specific setting. Their corresponding values, which are stored in the config_string column of SDE_dbtune, identify how the object or setting will be configured. The parameters and their configuration strings are grouped together in the SDE_dbtune table by configuration keywords. Keyword/
Parameter_name combinations are unique, but most parameter_names are not and are reused under a number of different keywords throughout the SDE_dbtune table.
Valid values for the parameter_names column are fixed; you cannot invent new parameter_names. Likewise, the config_strings accept only certain numeric values or SQL strings. In most cases, these strings are appended to Transact-SQL CREATE TABLE and CREATE INDEX statements.
In geodatabases stored in SQL Server, DBTUNE parameters and their corresponding configuration strings are used by ArcSDE to identify the following:
◦ Where a table or index is created (file group)
◦ Whether or not to cluster an index
◦ How much to fill each index page (FILLFACTOR)
◦ How much binary data should be stored in-line to a data page (TEXT_IN_ROW)
◦ How character data is stored
◦ How XML documents are stored and indexed
◦ What type of ArcSDE log file tables are used
◦ Whether or not a configuration keyword is available to client applications
◦ In what format spatial data is stored
◦ Provide comments that describe the configuration keyword.
There are a number of ways to categorize configuration parameters. Often times, a parameter fits into more than one category. For example, the B_CLUSTER_RASTER parameter can be categorized as a business table parameter, a raster parameter, or a clustered index parameter.
The following table is an alphabetic list of all the possible configuration parameters that can be used in a
geodatabase in SQL Server. Following that is a more in-depth explanation of the parameters roughly grouped by related functionality.
Parameter name Description Values Notes
A_CLUSTER_RASTER Index type for raster column in Adds table
1 or 0 1 = clustered 0 = nonclustered A_CLUSTER_ROWID Index type for rowid column on Adds
table
1 or 0 1 = clustered 0 = nonclustered A_CLUSTER_SHAPE Index type for Adds table shape column 1 or 0
1 = clustered 0 = nonclustered A_CLUSTER_STATEID Index type for Adds table stated column 1 or 0
1 = clustered 0 = nonclustered A_CLUSTER_USER Index type for any user defined indexes
on Adds table
1 or 0 1 = clustered 0 = nonclustered A_CLUSTER_XML Index type for xml doc type column of
Adds table
1 or 0 1 = clustered 0 = nonclustered A_INDEX_RASTER Index type for raster column in Adds
table
1 or 0 1 = clustered 0 = nonclustered
Parameter name Description Values Notes
A_INDEX_ROWID Adds table object ID column index
storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
A_INDEX_SHAPE Adds table spatial column index storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
A_INDEX_STATEID Adds table sde_state_id column index storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
A_INDEX_USER Adds table index storage definition See the SQL Server Books Online for CREATE INDEX parameters.
A_INDEX_XML Adds table XML column index table
storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
A_MS_SPINDEX Defines the spatial index for the Adds table of a versioned feature class that uses SQL Server 2008 Geometry storage
GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM),
CELLS_PER_OBJECT = 16 For other possible values, consult the SQL Server documentation on spatial index creation.
SQL Server spatial types only
A_STORAGE Adds table storage definition See the SQL Server Books Online for CREATE TABLE parameters.
A_TEXT_IN_ROW Amount, in bytes, of image (SE_blob)
data type (vector or raster) to store directly in the data page of the table
24–7,000 256
AUX_CLUSTER_COMPOSITE Index type for primary key 1 or 0
1 = clustered 0 = nonclustered AUX_INDEX_COMPOSITE Raster AUX table composite column
index storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
AUX_STORAGE Raster AUX table storage definition See the SQL Server Books Online for CREATE TABLE parameters.
B_CLUSTER_RASTER Index type for raster column in business table
1 or 0 1 = clustered 0 = nonclustered
B_CLUSTER_ROWID Index type for rowid column on
business table
1 or 0 1 = clustered 0 = nonclustered B_CLUSTER_SHAPE Index type for business table shape
column
1 or 0 1 = clustered 0 = nonclustered B_CLUSTER_USER Index type for any user defined indexes
on business table
1 or 0 1 = clustered 0 = nonclustered B_CLUSTER_XML Index type for xml doc type column of
business table 1 or 0
1 = clustered 0 = nonclustered B_INDEX_RASTER Business table raster column index
storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
B_INDEX_ROWID Business table object ID column index aster rowid index
R<N>_SDE_ROWID_UK storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
B_INDEX_SHAPE Business table spatial column index
storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
Parameter name Description Values Notes B_INDEX_TO_DATE Storage parameter info for creating the
index R<registration_id>_sde_todate, which is used when updating the history table during an archive operation
See the SQL Server Books Online for CREATE INDEX parameters.
B_INDEX_USER Business table user index storage
definition See the SQL Server Books Online for
CREATE INDEX parameters.
B_INDEX_XML Business table XML column index table
storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
B_MS_SPINDEX Defines the spatial index for the
business table of a feature class that uses SQL Server 2008 Geometry or
Geography storage
GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM),
CELLS_PER_OBJECT = 16 For other possible values, consult the SQL Server documentation on spatial
B_STORAGE Business table and raster attribute table
storage definition See the SQL Server Books Online for CREATE TABLE parameters.
B_TEXT_IN_ROW Amount, in bytes, of BLOB data stored
in line
24–7,000 256
BLK_CLUSTER_COMPOSITE Index type for primary key 1 or 0
1 = clustered 0 = nonclustered BLK_INDEX_COMPOSITE Raster BLK table composite column
index storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
BLK_STORAGE Raster BLK table storage definition See the SQL Server Books Online for CREATE TABLE parameters.
BND_CLUSTER_COMPOSITE Index type for primary key 1 or 0
1 = clustered BND_INDEX_COMPOSITE Raster BND table composite column
index storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
BND_INDEX_ID Raster BND table RID column index
storage definition See the SQL Server Books Online for CREATE INDEX parameters.
BND_STORAGE Raster BND table storage definition See the SQL Server Books Online for CREATE TABLE parameters.
COLLATION_NAME Collation of user-defined text columns Uses the database collation by default, unless other collation is specified
COMMENT Line used for comments Description up to 2048 characters
CROSS_DB_QUERY_FILTER Controls whether or not a connecting user can view rasters or feature classes across database boundaries; used only withmultispatial database model
1 or 0
1 = Can only view and access data in the database to which you have explicitly connected
0 = Can access data in other databases in the multispatial database
Parameter name Description Values Notes D_CLUSTER_DELETED_AT Index type for DELETED_AT column 1 or 0
1 = clustered 0 = nonclustered
D_INDEX_ALL FILLFACTOR and location (file group)
for composite index on SDE_STATES_ID,
SDE_DELETES_ROW_ID, and DELETED_AT columns
See the SQL Server Books Online for CREATE INDEX parameters.
D_INDEX_DELETED_AT Deletes table sde_deleted_at column
index storage definition See the SQL Server Books Online for CREATE INDEX parameters.
D_STORAGE Deletes table storage definition See the SQL Server Books Online for CREATE TABLE parameters.
F_CLUSTER_FID Index type for FID column 1 or 0
1 = clustered 0 = nonclustered F_INDEX_AREA Feature table area column index storage
definition
See the SQL Server Books Online for CREATE INDEX parameters.
Binary geometry storage only
F_INDEX_FID Feature table FID column index storage
definition
See the SQL Server Books Online for CREATE INDEX parameters.
Binary geometry storage only
F_INDEX_LEN Feature table length column index
storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
Binary geometry storage only F_STORAGE Feature table storage definition See the SQL Server Books Online for
CREATE TABLE parameters.
Binary geometry storage only
F_TEXT_IN_ROW Amount, in bytes, of image (SE_blob)
data type (vector or raster) to store directly in the data page of the table
24–7000 256 GEOMETRY_STORAGE Indicates storage type for spatial
column; set this storage parameter to SDEBINARY, OGCWKB,
GEOM_SRID_CHECK Adds a check constraint on the geometry column for an SRID value
TRUE or FALSE GEOMTAB_OUT_OF_ROW Specifies whether or not varchar(max),
nvarchar(max), varbinary(max) are xml columns smaller than 8000 bytes are stored in the data row of the CAD side table
Parameter name Description Values Notes
GEOMTAB_PK Storage definition for the primary key
index on the CAD table
See the SQL Server Books Online for CREATE INDEX parameters. GEOMTAB_STORAGE CAD table storage definition See the SQL Server Books Online for
CREATE TABLE parameters.
tables primary key storage definition See the SQL Server Books Online for CREATE INDEX parameters.
LD_STORAGE SDE_logfile_data and SDE_logpool
tables storage definition
See the SQL Server Books Online for CREATE TABLE parameters.
LF_CLUSTER_ID Index type for SDE_logfiles primary
key
1 or 0 1 = clustered 0 = nonclustered LF_CLUSTER_NAME Index type for unique index on table
SDE_logfiles column log file_name
1 or 0 1 = clustered 0 = nonclustered
LF_INDEX_ID SDE_logfiles primary key storage
definition See the SQL Server Books Online for
CREATE INDEX parameters.
LF_INDEX_NAME Storage definition for SDE_logfiles
unique index
See the SQL Server Books Online for CREATE INDEX parameters.
LF_STORAGE SDE_logfiles table storage definition See the SQL Server Books Online for CREATE TABLE parameters.
MVTABLES_MODIFIED_INDEX Mvtables_modified index storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
MVTABLES_MODIFIED_TABLE Mvtables_modified table storage definition
See the SQL Server Books Online for CREATE TABLE parameters.
NUM_DEFAULT_CURSORS Controls the SQL Server cursor
threshold -1 = All keysets are generated
synchronously
0 = All cursor keysets are generated asynchronously
All other values = SQL Server Query Optimizer compares the number of expected rows in the cursor set to the number set in cursor threshold, then builds the keyset asynchronously if it exceeds the cursor threshold number.
Parameter name Description Values Notes PERMISSION_CACHE_THRESHOLD Controls amount of time (in
milliseconds) the database can take to query the sysprotects table
If the
PERMISSION_CACHE_THRESHOLD value is exceeded, a temporary table (cache) is created to store a user's permission and is used from then on. As long as
PERMISSION_CACHE_THRESHOLD
> 0,
the permission will be cached. If PERMISSION_CACHE_THRESHOLD
= 0, it will not.
0–1,000 250
RAS_CLUSTER_ID Index type for primary key of RAS table 1 or 0 1 = clustered 0 = nonclustered
RAS_INDEX_ID Raster RAS table RID index storage
definition
See the SQL Server Books Online for CREATE INDEX parameters.
RAS_STORAGE Raster RAS table storage definition See the SQL Server Books Online for CREATE TABLE parameters.
S_CLUSTER_ALL Index type for primary key (all columns of table)
1 or 0 1 = clustered 0 = nonclustered S_CLUSTER_SP_FID Fill factor and location (file group) for
sp_fid column index See the SQL Server Books Online for CREATE INDEX parameters.
S_INDEX_ALL Spatial index table first index storage definition when using binary geometry storage
See the SQL Server Books Online for CREATE INDEX parameters.
S_INDEX_SP_FID Spatial index table second index storage definition
See the SQL Server Books Online for CREATE INDEX parameters.
S_STORAGE In Oracle and SQL Server databases,
this parameter represents the spatial index table storage definition; in Informix databases, this parameter represents a "smart blob sbspace"
See the SQL Server Books Online for CREATE TABLE parameters.
SESSION_TEMP_TABLE Controls whether or not log files get created in tempdb
1 or 0
1 = create in tempdb 0 = don't create in tempdb
STATES_INDEX States table storage definition See the SQL Server Books Online for CREATE INDEX parameters.
STATES_LINEAGES_INDEX Controls the storage of the index on the SDE_state_lineages table's primary key
See the SQL Server Books Online for CREATE INDEX parameters.
STATES_LINEAGES_TABLE State_lineages table storage definition See the SQL Server Books Online for CREATE TABLE parameters.
STATES_TABLE States table storage definition See the SQL Server Books Online for CREATE TABLE parameters.
UI_NETWORK_TEXT User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of network configuration
Description up to 2048 characters
Parameter name Description Values Notes UI_TERRAIN_TEXT User interface parameter, which
indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of terrain configuration
Description up to 2048 characters
UI_TEXT User interface parameter, which
indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of keyword will appear in the ArcGIS user interface; contains description of topoology configuration
Description up to 2048 characters
UNICODE_STRING Determines whether Unicode text types will be used or not; If set to TRUE, character fields will be stored in UNICODE compliant data types.
For example, if the
UNICODE_STRING parameter is set to FALSE a string data type would be VARCHAR. If UNICODE_STRING is set to TRUE, the data type of the field would be NVARCHAR.
TRUE or FALSE
VERSIONS_INDEX Version index storage definition See the SQL Server Books Online for CREATE INDEX parameters.
VERSIONS_TABLE Versions table storage definition See the SQL Server Books Online for CREATE TABLE parameters.
XML_DOC_INDEX Storage clause for xmldoc<n>_pk and xml_doc<n>_ix indexes on the sde_xml_doc<n> table
See the SQL Server Books Online for CREATE INDEX parameters.
XML_DOC_MODE Storage type for XML documents COMPRESSED or UNCOMPRESSED
XML_DOC_STORAGE Storage clause for sde_xml_doc<n>
table
See the SQL Server Books Online for CREATE TABLE parameters.
XML_DOC_TEXT_IN_ROW Storage clause for storing XML document BLOB data in line; values in bytes parameter is not present by default.
Possible values:
BINARY, TEXT, or UNICODE XML_IDX_CLUSTER_DOUBLE Storage clause indicating if the
xmlix<n>_db index on the double_tag column of the sde_xml_idx<n> table is clustered
1 or 0 1 = clustered 0 = nonclustered XML_IDX_CLUSTER_ID Storage clause indicating if the
xmlix<n>_id index on the id column of the sde_xml_idx<n> table is clustered
1 or 0 1 = clustered 0 = nonclustered
Parameter name Description Values Notes XML_IDX_CLUSTER_PK Storage clause indicating if the
xmlix<n>_pk index on the XML_IDX_CLUSTER_STRING Storage clause indicating if the
xmlix<n>_st index on the string_tag column of the sde_xml_idx<n> table is clustered
1 or 0 1 = clustered 0 = nonclustered XML_IDX_CLUSTER_TAG Storage clause indicating if the
xmlix<n>_tg index on the tag_id column of the sde_xml_idx<n> table is clustered
1 or 0 1 = clustered 0 = nonclustered XML_IDX_FULLTEXT_CAT Name of the full-text catalog in which
the contents of the xml_doc_val column in the sde_xml_doc<n> table and the text_tag column of the sde_xml_idx<n>
table are indexed
The name you gave the full-text catalog when it was created; the default is SDE_DEFAULT_CAT .
If you gave the catalog any other name, you must change the value of this parameter to match it.
XML_IDX_FULLTEXT_LANGUAGE The language used for linguistic analysis when building the text index on the contents of the xml_doc_val column in the sde_xml_doc<n> table and the text_tag column of the sde_xml_idx<n>
table
There is no default value set for this parameter. Consult your DBMS documentation for valid language settings.
XML_IDX_FULLTEXT_TIMESTAMP Determines whether or not a timestamp column will be added to the
sde_xml_idx<n> table
1 or 0
1 = timestamp column will be added 0 = timestamp column will not be added XML_IDX_FULLTEXT_UPDATE_METHOD Dictates how changes made to the
xml_doc_val column in the sde_xml_doc<n> table (the XML document table) and the text_tag column of the sde_xml_idx<n> table (the index table of an XML column) are propagated to the full-text index
CHANGE_TRACKING BACKGROUND or
CHANGE_TRACKING MANUAL
XML_IDX_INDEX_DOUBLE Storage clause for the xmlix<n>_db index on the double_tag column of the sde_xml_idx<n> table
See the SQL Server Books Online for CREATE INDEX parameters.
XML_IDX_INDEX_ID Storage clause for the xmlix<n>_id index on the ID column of the xml_idx<n> table
See the SQL Server Books Online for CREATE INDEX parameters.
XML_IDX_INDEX_PK Storage clause for xmlix<n>_pk index on the xml_key_column identity column of the sde_xml_idx<n> table
See the SQL Server Books Online for CREATE INDEX parameters.
XML_IDX_INDEX_STRING Storage clause for xmlix<n>_st index on the string_tag column of the
sde_xml_idx<n> table
See the SQL Server Books Online for CREATE INDEX parameters.
XML_IDX_INDEX_TAG Storage clause for the xmlix<n>_tg index on the tag_id column of the sde_xml_idx<n> table
See the SQL Server Books Online for CREATE INDEX parameters.
XML_IDX_STORAGE Storage clause for sde_xml_idx<n>
table (the index table of an XML column)
See the SQL Server Books Online for CREATE TABLE parameters.
Parameter name Description Values Notes XML_IDX_TEXT_IN_ROW Controls how much of the contents of
the text_tag column in the
sde_xml_idx<n> table (the index table of an XML column) can be stored in line; value in bytes
24–7000 128
NOTE: For the XML parameters, <n> refers to the xml_column_id associated with a specific XML column.
Parameters specific to feature class and raster storage
◦ Business table parameters
The business table is the attribute table of a feature class or nonspatial table. Business table parameters begin with B and define storage for the business table and its indexes. The parameters are as follows:
Parameter Description
B_CLUSTER_ROWID Index type for rowid column on the business table; 0 = nonclustered index, 1 = clustered index
B_CLUSTER_SHAPE Index type for shape column of a business table; 0 = nonclustered index; 1 = clustered index
B_CLUSTER_SHAPE Index type for shape column of a business table; 0 = nonclustered index; 1 = clustered index