SQL Server DBTUNE configuration parameters
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_name column are fixed; you cannot invent new parameter names. Likewise, the config_string column accepts only certain numeric values or SQL strings. In most cases, these strings are appended to SQL CREATE TABLE and CREATE INDEX statements, so they must match the SQL syntax necessary for the given DBMS.
In geodatabases stored in Microsoft SQL Server, sde_dbtune parameters and their corresponding configuration strings are used by ArcGIS to define how data is stored. The following lists storage settings available with different parameters:
- 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 (OUT_OF_ROW)
- How character data is stored
- How XML documents are stored and indexed
- What type of ArcSDE log file tables are used
- Make keywords available for users in the ArcGIS interface.
- 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, 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 |
|
|
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 (Geometry or Geography) only |
|
A_OUT_OF_ROW |
Determines whether or not data will be stored in row or out of row for varbinary(max) columns in the adds table If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row. |
0 or 1 |
Only used in ArcGIS 10 or later releases |
|
A_STORAGE |
Adds table storage definition |
See the SQL Server Books Online for CREATE TABLE parameters. |
|
|
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_TO_DATE | Specifies the index type on the to_date,archive_rowid column in the history table of a table that is enabled for archiving. | 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 raster 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. |
|
|
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 index creation. |
SQL Server spatial types (Geometry or Geography) only |
|
B_OUT_OF_ROW |
Determines whether or not data will be stored in row or out of row for varbinary(max) columns in a business table If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row. |
0 or 1 |
Only used in ArcGIS 10 or later releases |
|
B_STORAGE |
Business table and raster attribute table storage definition |
See the SQL Server Books Online for CREATE TABLE parameters. |
|
|
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_OUT_OF_ROW |
Determines whether or not data will be stored in row or out of row for varbinary(max) columns in the raster blocks table If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row. |
0 or 1 |
Only used in ArcGIS 10 or later releases |
|
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 0 = nonclustered |
|
|
BND_CLUSTER_ID |
Index type for RASTER_ID, SEQUENCE_NBR columns |
1 or 0; 1 = clustered 0 = nonclustered |
|
|
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 2,048 characters |
|
|
CROSS_DB_QUERY_FILTER |
Controls whether or not a connecting user can view rasters or feature classes across database boundaries; used only with the multispatial 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 |
|
|
D_CLUSTER_ALL |
Index type for SDE_STATES_ID, SDE_DELETES_ROW_ID, and DELETED_AT columns |
1 or 0; 1 = clustered 0 = nonclustered |
|
|
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 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 (SDEBINARY and SDELOB) |
|
F_INDEX_FID |
Feature table FID column index storage definition |
See the SQL Server Books Online for CREATE INDEX parameters. |
Binary geometry storage only (SDEBINARY and SDELOB) |
|
F_INDEX_LEN |
Feature table length column index storage definition |
See the SQL Server Books Online for CREATE INDEX parameters. |
Binary geometry storage only (SDEBINARY and SDELOB) |
|
F_OUT_OF_ROW |
Determines whether or not data will be stored in row or out of row for varbinary(max) columns in a feature (f) table If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row. |
0 or 1 |
Only used in ArcGIS 10 or later releases |
|
F_STORAGE |
Feature table storage definition |
See the SQL Server Books Online for CREATE TABLE parameters. |
Binary geometry storage only (SDEBINARY and SDELOB) |
|
GEOMETRY_STORAGE |
Indicates storage type for spatial column Set this storage parameter to SDEBINARY, OGCWKB, GEOGRAPHY, or GEOMETRY for SQL Server. |
GEOMETRY, SDEBINARY, OGCWKB, or GEOGRAPHY |
|
|
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 |
1 or 0 1 = Stored out of row, 0 = Stored in row |
CAD side tables only created if feature class uses GEOMETRY or GEOGRAPHY storage and is enabled to store CAD entities |
|
GEOMTAB_PK |
Storage definition for the primary key index on the CAD table |
See the SQL Server Books Online for CREATE INDEX parameters. |
CAD side tables only created if feature class uses GEOMETRY or GEOGRAPHY storage and is enabled to store CAD entities |
|
GEOMTAB_STORAGE |
CAD table storage definition |
See the SQL Server Books Online for CREATE TABLE parameters. |
CAD side tables only created if feature class uses GEOMETRY or GEOGRAPHY storage and is enabled to store CAD entities |
I_STORAGE | Defines storage for the i tables, which are used to generate IDs. | See the SQL Server Books Online for CREATE TABLE parameters. | |
|
LD_INDEX_ALL |
SDE_logfile_data and SDE_logpool 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 For all other values, the SQL Server Query Optimizer compares the number of expected rows in the cursor set to the number set in the cursor threshold, then builds the keyset asynchronously if it exceeds the cursor threshold number. |
|
|
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. |
|
|
RASTER_STORAGE |
Defines the raster data storage type |
binary or ST_RASTER |
|
|
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, represents the spatial index table storage definition; in Informix databases, 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 If set to 1, the table is created in tempdb. If set to 0, table not created 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 2,048 characters |
|
|
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 2,048 characters |
|
|
UI_TEXT |
User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of associated noncomposite configuration keyword |
Description up to 2,048 characters |
|
|
UI_TOPOLOGY_TEXT |
User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of topology configuration |
Description up to 2,048 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_COLUMN_PATH_IDX |
Determines if an XML path index is created |
0 or 1 0 = No path index created, 1 = Path index is created. |
|
|
XML_COLUMN_PRIMARY_IDX |
Determines if XML primary index is created |
0 or 1 0 = No primary index created, 1 = Primary index created |
|
|
XML_COLUMN_PROPERTY_IDX |
Determines if XML property index is created |
0 or 1 0 = No property index created, 1 = Property index created |
|
|
XML_COLUMN_SCHEMA |
Specifies the XML schema to be used for validation |
XML schema collection name, up to 128 characters |
|
|
XML_COLUMN_STORAGE |
Specifies the type of XML columns to create: either ArcSDE XML or native DBMS XML |
DB_XML or SDE_XML DB_XML is the default value under the DEFAULTS keyword. SDE_XML is the default value under the IMS_GAZETTEER keyword. |
|
|
XML_COLUMN_TYPE |
Specifies the XML document type that a column can store |
CONTENT or DOCUMENT |
Change this parameter to DOCUMENT if every XML instance has only one top-level element |
|
XML_COLUMN_VALUE_IDX |
Determines if XML value index is created |
0 or 1 0 = No value index created, 1 = Value index is created. |
|
|
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_OUT_OF_ROW |
Determines whether or not XML document BLOB data will be stored in row or out of row; only used for varbinary(max) columns If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row. |
0 or 1 |
Only used in ArcGIS 10 or later releases |
|
XML_DOC_STORAGE |
Storage clause for sde_xml_doc<n> table |
See the SQL Server Books Online for CREATE TABLE parameters. |
|
|
XML_DOC_UNCOMPRESSED_TYPE |
When the XML_DOC_MODE parameter is set to UNCOMPRESSED, the XML_DOC_UNCOMPRESSED_TYPE parameter determines the storage format for XML documents |
Since XML_DOC_MODE is set to COMPRESSED by default, the XML_DOC_UNCOMPRESSED_TYPE 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 |
|
|
XML_IDX_CLUSTER_PK |
Storage clause indicating if the xmlix<n>_pk index on the xml_key_column identity column of the sde_xml_idx<n> table is clustered |
1 or 0; 1 = clustered 0 = nonclustered |
|
|
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 time stamp column will be added to the sde_xml_idx<n> table |
1 or 0 1 = timestamp column will be added, 0 = time stamp 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_OUT_OF_ROW |
Determines whether or not 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 row or out of row; only used for varbinary(max) columns If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row. |
0 or 1 |
Only used in ArcGIS 10 or later releases |
|
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. |
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 row ID (object ID) column on the business table; 0 = nonclustered index, 1 = clustered index |
|
B_CLUSTER_SHAPE |
Index type for shape column of a business table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
B_CLUSTER_USER |
Index type for any user-defined indexes on a business table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
B_CLUSTER_XML |
Index type for the XML type column of a business table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
B_CLUSTER_RASTER |
Index type for the raster column in a business table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
B_INDEX_ROWID |
Defines the fillfactor and location (file group) for the row ID column index of a business table To specify a file group, use the SQL ON statement, for example: B_INDEX_ROWID "with fillfactor=99 ON IDXfg" |
|
B_INDEX_SHAPE |
Defines the fillfactor and location (file group) for the shape column index of a business table To specify a file group, use the SQL ON statement, for example: B_INDEX_SHAPE "with fillfactor=99 ON SHAPEfg" |
|
B_INDEX_USER |
Defines the fillfactor and location (file group) for any user defined indexes on a business table To specify a file group, use the SQL ON statement, for example: B_INDEX_USER "with fillfactor=99 ON IDXfg" |
|
B_INDEX_XML |
Defines the fillfactor and location (file group) for XML index on a business table To specify a file group, use the SQL ON statement, for example: B_INDEX_XML "with fillfactor=99 ON XMLfg" |
|
B_INDEX_RASTER |
Defines the fillfactor and location (file group) for the raster column index on a business table To specify a file group, use the SQL ON statement, for example: B_INDEX_RASTER "with fillfactor=99 ON RASfg" |
|
B_INDEX_TO_DATE |
Storage information for creating the index R<registration_id>_sde_todate, which is used when updating the history table during an archive operation  Note:This parameter does not have a corresponding adds table parameter. |
|
B_MS_SPINDEX |
Specifies the spatial index of the business table of a feature class that is using the SQL Server Geometry type for vector storage ArcGIS automatically calculates the bounding box of the feature class (also known as the layer extent), but the remaining options for the CREATE SPATIAL INDEX command are supplied using this parameter and the corresponding adds table parameter, A_MS_SPINDEX. The default setting for this parameter is the same as the SQL Server default. When exported to a dbtune.sde file, this setting would look like the following: B_MS_SPINDEX "GRIDS = (MEDIUM, MEDIUM,MEDIUM, MEDIUM), CELLS_PER_OBJECT = 16" |
|
B_OUT_OF_ROW |
A value of either 0 or 1 If set to 0, up to 8,000 bytes are stored directly in the data page of the table. If set to 1, data is always stored out of row. |
|
B_STORAGE |
File group location for a business table Use ON to control location, for example: B_STORAGE "ON ADDS_FG" |
For a nonspatial business table, do one of the following:
- Change the B_CLUSTER_ROWID parameter's config_string to 1 and the B_CLUSTER_SHAPE parameter's config_string to 0. This will create a clustered index on the object ID field. Any subsequent user-defined indexes you create will be nonclustered.
- Change the B_CLUSTER_USER parameter's config_string to 1. The first user-defined index created by ArcGIS will be clustered. Change B_CLUSTER_SHAPE to 0.
- Create the data and change whatever index (or composite indexes) you would like to be clustered.
Adds table parameters
An adds table is a table that stores insert and update edits made against a feature class in a versioned geodatabase. It is almost identical in structure to the business table but has additional columns to track state IDs. Adds table parameters begin with A. The adds table parameters are as follows:
|
Parameter |
Description |
|---|---|
|
A_CLUSTER_ROWID |
Index type for row ID column on an adds table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
A_CLUSTER_SHAPE |
Index type for shape column of an adds table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
A_CLUSTER_STATEID |
Index type for the stated column of an adds table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. Note:This parameter does not have a corresponding business table parameter. |
|
A_CLUSTER_USER |
Index type for any user-defined indexes on an adds table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
A_CLUSTER_XML |
Index type for the XML type column of an adds table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
A_CLUSTER_RASTER |
Index type for a raster column in an adds table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
A_INDEX_ROWID |
Specifies the fillfactor and location (file group) for the row ID column index of an adds table To specify a file group, use the SQL ON statement, for example: A_INDEX_ROWID "with fillfactor=99 ON IDXfg" |
|
A_INDEX_SHAPE |
Specifies the fillfactor and location (file group) for the shape column index of an adds table To specify a file group, use the SQL ON statement, for example: A_INDEX_SHAPE "with fillfactor=99 ON SHAPEfg" |
|
A_INDEX_STATEID |
Specifies the fillfactor and location (file group) for the state ID column index of an adds table To specify a file group, use the SQL ON statement, for example: A_INDEX_STATEID "with fillfactor=99 ON STATEIDXfg" Note:This parameter does not have a corresponding business table parameter. |
|
A_INDEX_USER |
Specifies the fillfactor and location (file group) for any user-defined indexes on an adds table To specify a file group, use the SQL ON statement, for example: A_INDEX_USER "with fillfactor=99 ON IDXfg" |
|
A_INDEX_XML |
Specifies the fillfactor and location (file group) for an XML index on an adds table To specify a file group, use the SQL ON statement, for example:
A_INDEX_XML "with fillfactor=99 ON XMLfg" |
|
A_INDEX_RASTER |
Specifies the fillfactor and location (file group) for a raster column index on an adds table To specify a file group, use the SQL ON statement, for example: A_INDEX_RASTER "with fillfactor=99 ON RASfg" |
|
A_MS_SPINDEX |
Specifies the spatial index of the adds table on a versioned feature class when the feature class is using the SQL Server Geometry type for vector storage ArcGIS automatically calculates the bounding box of the feature class (also known as the layer extent), but the remaining options for the CREATE SPATIAL INDEX command are supplied using this parameter and the corresponding business table parameter, B_MS_SPINDEX. The default setting for this parameter is the same as the SQL Server default. When exported to a dbtune.sde file, this setting would look like following:
A_MS_SPINDEX "GRIDS = (MEDIUM, MEDIUM,MEDIUM, MEDIUM), CELLS_PER_OBJECT = 16" |
|
A_OUT_OF_ROW |
A value of either 0 or 1 If set to 0, up to 8,000 bytes are stored directly in the data page of the table. If set to 1, data is always stored out of row. |
|
A_STORAGE |
Specifies in which file group adds tables will be created when datasets are registered as versioned Use ON to control location, for example: A_STORAGE "ON ADDS_FG" |
Nonspatial tables have no shape column, so cluster one of the other indexes.
Deletes table parameters
The deletes table is used to track updates and deletes made to versioned tables. The deletes table parameters work the same way as adds table parameters. All deletes table parameters begin with D. They are as follows:
|
Parameter |
Description |
|---|---|
|
D_CLUSTER_ALL |
Index type for the index created on the SDE_STATES_ID, SDE_DELETES_ROW_ID, and DELETED_AT columns If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
D_CLUSTER_DELETED_AT |
Index type for the index on the DELETED_AT column If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
D_INDEX_ALL |
Specifies the fillfactor and location (file group) for a composite index on the SDE_STATE_ID, SDE_DELETES_ROW_ID, and DELETED_AT columns, for example:
D_INDEX_ALL "with fillfactor=99 ON Deletes_fg" |
|
D_INDEX_DELETED_AT |
Specifies the fillfactor and location (file group) for the index on the deleted_at column, for example:
D_INDEX_DELETED_AT "with fillfactor=80 ON Deletes_fg" |
|
D_STORAGE |
Specifies in which file group deletes tables will be created when datasets are registered as versioned Use ON to control location, for example: D_STORAGE "ON Deletes_fg" |
Feature table parameters
Feature tables are only used with feature classes using binary storage (SDEBINARY or OGCWKB). The feature table stores each shape's extent and geometry. It will also contain records from versioned inserts and updates. All feature table parameters begin with F.
|
Parameter |
Description |
|---|---|
|
F_CLUSTER_FID |
Index type for the feature ID column If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
F_INDEX_AREA |
Specifies the fillfactor and location (file group) for the index on the area column, for example: F_INDEX_AREA "WITH FILLFACTOR = 90 ON F_IDX" |
|
F_INDEX_FID |
Specifies the fillfactor and location (file group) for the index on the feature ID column, for example: F_INDEX_FID "WITH FILLFACTOR = 90 ON F_IDX" |
|
F_INDEX_LEN |
Specifies the fillfactor and location (file group) for the index on the length column, for example: F_INDEX_LEN "With FILLFACTOR = 90 ON F_IDX" |
|
F_STORAGE |
File group location for the f table Use ON to control location, for example: F_STORAGE "WITH FILLFACTOR=90 ON F_IDX" |
|
F_OUT_OF_ROW |
A value of either 0 or 1 If set to 0, up to 8,000 bytes are stored directly in the data page of the table. If set to 1, data is always stored out of row. |
Raster table parameters
Binary rasters in ArcGIS are stored as five separate tables: a band table (SDE_bnd_#), a block table (SDE_blk_#), a raster table (SDE_ras_#), an auxiliary table (SDE_aux_#), and a business table. ST_Raster columns are stored in an ST_RASTER type column in the business table and two supporting tables: an auxiliary and a block table.
Rasters can be stored as embedded catalogs or columns in feature classes or can be stand-alone datasets.
Raster table parameters begin with AUX, BLK, BND, and RAS, which correspond to the raster tables. The parameters that define storage for the business table of the raster are defined by business table parameters.
Of all the raster tables, only the block table will get large.
If using binary rasters, make certain the BND_CLUSTER_COMPOSITE config_string is set to 1 to ensure that a clustered index is generated for the band table.
The raster table parameters are as follows:
|
Parameter |
Description |
|---|---|
|
AUX_CLUSTER_COMPOSITE |
Index type for the primary key of the auxiliary table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
AUX_INDEX_COMPOSITE |
Specifies the fillfactor and file group location for the primary key index of the auxiliary table, for example:
AUX_INDEX_COMPOSITE "WITH FILLFACTOR= 90 ON AUX_FG" |
|
AUX_STORAGE |
Specifies the file group location for the auxiliary table Use ON to specify location, for example: AUX_STORAGE "ON AUX_FG" |
|
BLK_CLUSTER_COMPOSITE |
Index type for the primary key of the block table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
BLK_INDEX_COMPOSITE |
Specifies the file group location for the composite index on the block table Use ON keyword to specify location, for example:
BLK_INDEX_COMPOSITE "WITH FILLFACTOR = 95 ON BLK_FG" |
|
BLK_STORAGE |
Specifies the file group location for the block table Use ON to specify location, for example: BLK_STORAGE "ON BLK_FG" |
|
BND_CLUSTER_COMPOSITE |
Index type for the primary key index of the band table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
BND_CLUSTER_ID |
Index type for the raster_id and sequence_nbr columns of the band table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
BND_INDEX_COMPOSITE |
Specifies the fillfactor and file group location for the primary key index of the band table, for example:
BND_INDEX_COMPOSITE "WITH FILLFACTOR =90 ON BND_FG" |
|
BND_INDEX_ID |
Specifies the fillfactor and file group location for the raster_id and sequence_nbr column index of the band table, for example:
BND_INDEX_ID "WITH FILLFACTOR = 90 ON BND_FG" |
|
BND_STORAGE |
The file group location for the band table Use ON to control location, for example: BND_STORAGE " ON BND_FG" |
|
RAS_CLUSTER_ID |
Index type for the primary key of the raster table If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
RAS_INDEX_ID |
Fillfactor and location (file group) for the primary key index of the raster table Use ON to control location, for example:
RAS_INDEX_ID "WITH FILLFACTOR = 85 ON RAS_FG" |
|
RAS_STORAGE |
File group location for the raster table Use ON to control location, for example: RAS_STORAGE " ON RAS_FG" |
There is an additional type of raster table—the raster attribute table. This table (or tables; there can be multiple such tables) stores attribute values based on cell values in the raster. The B_STORAGE parameter defines the storage of these tables. If you need to define a different storage location for these tables than you do for other feature class business tables, be sure to create a raster keyword you can use when creating raster datasets and raster catalogs that specifies different storage information for the raster attribute tables.
To learn more about raster attribute tables, see Raster dataset attribute tables. To learn about custom configuration keywords, see Custom configuration keywords.
Parameters specific to indexes
Fill factor parameters
These parameters are structured as *_INDEX_*. They allow you to specify the FILLFACTOR argument for that index. The FILLFACTOR argument specifies how full each page in the leaf level of an index should be. SQL Server uses a default value of 0, which means that the leaf pages of an index are almost full, but the nonleaf pages have room for at least two more rows. User-defined fill factors can be between 1 and 100. If the fill factor is 100, all pages are completely full. With a fill factor of 75, each clustered index page starts 75 percent full. Subsequent inserts and updates to that data add to the index page. When the page hits 100 percent capacity, it is full. Any subsequent insert or update to data in that page will split the page. Use FILLFACTOR to balance full index pages and page splits. When a page is split, SQL Server moves approximately 50 percent of the data in the split page to a new page, most likely allocated from a different extent. Page splits will fragment your tables and compromise performance. Setting FILLFACTOR too low creates too many data pages and extents to traverse in a query, thus negatively impacting performance.The following are decision criteria for choosing a fill factor:
- Is your data read-only? Will it never be edited? If yes, set all fill factors on your data to 100.
- Will your data be updated frequently? Use the defaults.
- Will your data be updated occasionally? Pick a range between 75 and 95 percent based on how often you want to defragment your tables.
Monitor fragmented tables and page splits with sys.dm_db_index_physical_stats.
Clustered index parameters
Note:You cannot separate a clustered index from its table; therefore, for the table parameters described in previous sections, make sure you specify a storage location that is the same for the table's associated clustered indexes.
These parameters are structured as *_CLUSTER_*, and they indicate whether or not a particular index should be clustered (1 = cluster; 0 = nonclustered). Clustered indexes store tabular data at their leaf nodes. The data pages at the clustered index leaf level derive their order from the clustered index key value. This has one important consequence with regard to the SDE_dbtune table: you cannot separate a table from its clustered index. For example, you specify that a feature table's feature ID (FID) index be created on the FeatIdx file group while the feature table should be stored on the Feat file group. The FID index is created as clustered. SDE_dbtune might look like this:
|
keyword |
parameter_name |
config_string |
|---|---|---|
|
DEFAULTS |
F_INDEX_FID |
WITH FILLFACTOR=90 ON FEATIDX |
|
DEFAULTS |
F_STORAGE |
ON FEAT |
In the preceding example, both the feature table and feature table's index will reside on the FeatIdx file group. The feature table is created first, then a primary key constraint is applied to the FID column. The constraint creates a clustered index on the FID column and references the FEATIDX file group in this statement:
ALTER TABLE features.dbo.f4
ADD CONSTRAINT f4_pk PRIMARY KEY CLUSTERED (fid)
WITH FILLFACTOR=75
ON FEATIDX
Therefore, the ON FEAT configuration string is redundant, as the index is created after the table, and the F_INDEX_FID configuration string will overwrite that of F_STORAGE.The next example specifies a file group for the feature table but not for the feature table's index.
|
keyword |
parameter_name |
config_string |
|---|---|---|
|
DEFAULTS |
F_INDEX_FID |
WITH FILLFACTOR=90 |
|
DEFAULTS |
F_STORAGE |
ON FEAT |
In this case, both the feature table and clustered index on the FID column will reside on the Feat file group. This occurs because the table is created first, and when the ALTER TABLE statement is applied, no ON statement is appended because no such string is listed in the preceding config_string column.
ALTER TABLE features.dbo.f5
ADD CONSTRAINT f5_pk PRIMARY KEY CLUSTERED (fid)
WITH FILLFACTOR=75
Spatial index parameters
The spatial index on binary data is a grid that overlays features and is used to identify features to fetch. The bounding box of a spatial query is overlaid against the spatial index table to select candidate shapes satisfying the query. Spatial index table parameters are only used for datasets that use binary storage (SDEBINARY or OGCWKB). These parameters begin with S.
|
Parameter |
Description |
|---|---|
|
S_CLUSTER_ALL |
Index type for primary key (all columns of table) If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
S_CLUSTER_SP_FID |
Index type for sp_fid column If set to 0, a nonclustered index is created. If set to 1, a clustered index is created. |
|
S_INDEX_ALL |
Specifies the filllfactor and location (file group) for the primary key index, for example:
S_INDEX_ALL "With FILLFACTOR = 90 ON S_IDX" |
|
S_INDEX_SP_FID |
Specifies the filllfactor and location (file group) for sp_fid column index, for example:
S_INDEX_SP_FID "WITH FILLFACTOR = 85 ON S_IDX" |
|
S_STORAGE |
Specifies the file group location for the S table Use the ON keyword to control location, for example:
S_STORAGE "WITH FILLFACTOR=95 ON S_IDX" |
Spatial index parameters for datasets that use SQL Server geometry or geography storage use the Microsoft spatial index. The parameters to define this index are B_MS_SPINDEX and A_MS_SPINDEX. They were described in the business table and adds table parameter sections, respectively.
Parameters for storing text
Text out of row parameters
New binary spatial or raster columns created in ArcSDE 10 or later release geodatabases use varbinary(max) data types. You can use the _OUT_OF_ROW parameters to specify whether or not the first 8,000 bytes of varbinary(max) data is stored in line or whether all of it is stored out of line. By default, these parameters are set to 0, meaning all the data will be stored out of line (row).
Text column storage parameters
There are two parameters that affect how text data is stored in the database: UNICODE_STRING and COLLATION_NAME. The UNICODE_STRING parameter determines whether or not text columns use Unicode encoding. By default, this parameter is set to TRUE, meaning all text data created using the DEFAULTS configuration keyword will be stored in Unicode format (UTF-8). If this parameter is set to FALSE, text is stored using the encoding set for the database. If UNICODE_STRING is set to TRUE, the COLLATION_NAME parameter can be used to specify a collation for user-defined text columns that differs from the collation of the database. By default, all character data columns use the default database collation. When a new dataset (such as a table or feature class) is created, the collation specified in COLLATION_NAME is applied to every character data column. If COLLATION_NAME is blank, the database collation is used. If the UNICODE_STRING parameter is set to FALSE, the COLLATION_NAME parameter is ignored.
For the value of the COLLATION_NAME parameter, choose the case-sensitive version of your database collation. This usually means altering the CI in the collation name to CS. If you are unsure, check the SQL Server Books Online or execute the following query for a list of collation names:
SELECT * FROM ::fn_helpcollations()
Parameters that affect spatial storage
Spatial index parameters were discussed under the section "Parameters specific to indexes". Other parameters that affect spatial data storage are GEOMETRY_STORAGE and GEOM_SRID_CHECK.
GEOMETRY_STORAGE
Geodatabases in SQL Server can use four spatial data storage formats, and the GEOMETRY_STORAGE parameter indicates which geometry storage method is to be used. The GEOMETRY_STORAGE parameter has the following values:
- Microsoft SQL Server Geometry type—This is Microsoft's spatial type for managing spatial data defined by coordinates on an arbitrary plane and for which the curvature of the Earth is not a consideration. This is the default spatial storage method of geodatabases in SQL Server beginning with ArcGIS 10.1. Keep the GEOMETRY_STORAGE parameter set to GEOMETRY if you want to store your spatial data in this format. If the GEOMETRY_STORAGE parameter is not set, the GEOMETRY type is assumed.
- Microsoft SQL Server Geography type—This is Microsoft's spatial type for managing spatial data defined by lat/long coordinates. You use this type when your features span large areas and need to take into consideration the curvature of the Earth. If you want to make this format the default storage type for your geodatabase, set the GEOMETRY_STORAGE parameter to GEOGRAPHY under the DEFAULTS configuration keyword parameter list.
- ArcSDE compressed binary format (SDEBINARY)—This storage type uses a binary storage mechanism for storing feature geometry. If you want to make this format the default storage type for your geodatabase, set the GEOMETRY_STORAGE parameter to SDEBINARY under the DEFAULTS configuration keyword parameter list.
- OGC Well-known binary geometry type (OGCWKB)—This type provides a portable representation of geometry as a contiguous stream of bytes. Set the GEOMETRY_STORAGE parameter to OGCWKB if you want to store your spatial data in this format. If you want to make this format the default, set the GEOMETRY_STORAGE parameter to OGCWKB in the DEFAULTS configuration keyword. Note that the OGC well-known binary representation supports only simple 2D geometries.
If all the feature classes in your database use the same geometry storage method, set the GEOMETRY_STORAGE parameter once in the DEFAULTS configuration keyword.
GEOM_SRID_CHECK
If you create feature classes in ArcGIS that use the SQL Server geometry type, or you create a spatial table with a SQL Server geometry column using SQL and register the table with the geodatabase, all records in the table must use the same spatial reference ID (SRID).
The DBMS does not enforce having a single SRID for all records in a table. Therefore, if you plan to use SQL to edit tables that have a SQL Server geometry column but are registered with the geodatabase, you may want to set the GEOM_SRID_CHECK parameter to TRUE. When this parameter is set to TRUE, ArcSDE adds a check constraint on the geometry column for a SRID value. This ensures that users editing outside of ArcGIS do not add multiple SRIDs to the same table. Be aware, though, that setting this parameter to TRUE can impact performance. For that reason, do not set this parameter to TRUE if none of your users are using SQL (or third-party software) to edit spatial tables registered with ArcSDE.
GEOMTAB_OUT_OF_ROW, GEOMTAB_PK, and GEOMTAB_STORAGE
These three parameters affect the storage of the CAD side tables that can be used with feature classes that use SQL Server geometry or geography storage types. These side tables are created for feature classes that are enabled to store CAD entities, such as curves. All feature classes created through ArcGIS for Desktop are set to store CAD entities. If data is imported or registered with ArcSDE using ArcSDE administration commands, you specify whether or not CAD data can be stored in the resultant feature class. The name of the CAD side table follows the convention SDE_geometry<ID>, where the ID is the associated feature class's layer_id from the SDE_layers table.
The GEOMTAB_OUT_OF_ROW parameter denotes if the data in the CAD column of an SDE_geometry<ID> table can be stored in the data row. Since data rows can store a maximum of 8,000 bytes in row, only data smaller than that can be stored in row, and only if the GEOMTAB_OUT_OF_ROW parameter is set to 0 (off). If the GEOMTAB_OUT_OF_ROW parameter is set to 1 (on), the values are always stored outside the data row and a 16-byte pointer to the external page is stored in the data row.
The GEOMTAB_PK parameter specifies the fill factor for the primary key index on an SDE_geometry<ID> table. By default, this is set to WITH FILLFACTOR=75.
The GEOMTAB_STORAGE parameter specifies the file group location for the SDE_geometry<ID> tables. Use the ON keyword to control location, for example, ON cad_fg.
Parameters for XML document storage
Note:If you do not use XML columns and XML documents in your geodatabase, you do not need to configure these parameters.
Tables that contain an ArcSDE XML (SE_XML_TYPE ) column will employ two side tables to store the XML document and the content of individual elements in those documents that have been indexed. No side tables are used when storing native SQL Server XML columns.
The XML_COLUMN_STORAGE parameter determines whether XML columns are created as ArcSDE XML or native SQL Server XML. The default setting is to use SQL Server XML (DB_XML).
If the storage type used is DB_XML, the following parameters can be set:
XML_COLUMN_SCHEMA XML_COLUMN_TYPE XML_COLUMN_PRIMARY_IDX XML_COLUMN_PATH_IDX XML_COLUMN_PROPERTY_IDX XML_COLUMN_VALUE_IDX
XML_COLUMN_SCHEMA specifies a schema collection to be used when adding or altering XML data. XML schema collections enforce schema constraints on XML data.
XML data that is associated with an XML schema collection is referred to as typed XML. The XML_COLUMN_TYPE parameter specifies what type of XML document the column stores; either CONTENT or DOCUMENT. CONTENT is the default value. DOCUMENT should only be used if the XML data has only one top-level element.
XML_COLUMN_PRIMARY_IDX, XML_COLUMN_PATH_IDX, XML_COLUMN_PROPERTY_IDX, and XML_COLUMN_VALUE_IDX determine whether or not primary, path, property, or value indexes will be created on the XML column.
If searches typically examine the entire content of XML documents to see if they contain specific words, or if you don't search XML documents at all, the XML document table will be more heavily used. XML document tables will have three parameters:
XML_DOC_INDEX XML_DOC_STORAGE XML_DOC_OUT_OF_ROW
XML_DOC_STORAGE provides the storage string for the table's creation statement. XML_DOC_INDEX has the index fill factor and storage parameters, while XML_DOC_OUT_OF_ROW pertains to storing BLOB data out of line. See the section in this topic, "Text in row parameters", for more information.
If individual elements are frequently searched, the XML document index table will be the most heavily accessed of the XML tables. It has more SDE_dbtune parameters; they all begin with XML_IDX_.
XML_IDX_CLUSTER_DOUBLE XML_IDX_CLUSTER_ID XML_IDX_CLUSTER_PK XML_IDX_CLUSTER_TAG XML_IDX_INDEX_DOUBLE XML_IDX_INDEX_ID XML_IDX_INDEX_PK XML_IDX_INDEX_TAG XML_IDX_STORAGE XML_IDX_OUT_OF_ROW
The XML_IDX_CLUSTER_* parameters dictate which index of the XML document index table should be clustered. By default, the primary key's index (on the xml_key_column) is clustered.
The following parameters affect both the XML document table and the XML document index table for an XML column. They control how and when the document content is indexed.
XML_IDX_FULLTEXT_CAT XML_IDX_FULLTEXT_LANGUAGE XML_IDX_FULLTEXT_TIMESTAMP XML_IDX_FULLTEXT_UPDATE_METHOD
XML_IDX_FULLTEXT_CAT contains the name of the full-text catalog you created. The default is SDE_DEFAULT_CAT. If you name your full-text catalog something other than SDE_DEFAULT_CAT, you must update the config_string for this parameter.
XML_IDX_FULLTEXT_LANGUAGE represents the language to be used for linguistic analysis when building the text indexes on the XML document's content. A default value is not provided; therefore, the language defined in SQL Server's default full-text language setting is used. If a value is provided, this language will be used for linguistic analysis instead.
XML_IDX_FULLTEXT_TIMESTAMP and XML_IDX_FULLTEXT_UPDATE_METHOD control full-text index maintenance. The update_method parameter dictates how changes made to the document table are propagated to the full-text index. The time stamp parameter, by default (1), will add a time stamp column to the SDE_xml_idx<xml_column_id> table. If set to 0, no such column is added.
If update_method is set to 0 and time stamp is set to 0, no index maintenance is performed, and whenever ArcGIS is instructed to update the full-text index (through SE_xmlindex_update_text_index), the index will be fully populated.
If update_method is set to 0 and time stamp is set to 1, no index maintenance is performed, and ArcGIS will perform an incremental index population of whatever has changed since the last incremental update.
If update_method is set to CHANGE_TRACKING MANUAL, the database maintains a list of changed rows but does not update the index.
If update_method is set to CHANGE_TRACKING BACKGROUND, the database tracks changes and automatically updates the index.
It is recommended that you use the default settings provided in the SDE_dbtune table. If your server is unable to service its workload and your only recourse is to change indexing behavior, set change tracking to manual (CHANGE_TRACKING MANUAL).
The next parameters, XML_IDX_INDEX_*, control index fill factor and storage on the SDE_xml_idx<xml_column_id> table. The XML_IDX_TEXT_IN_ROW controls how much of the XML document BLOB can be in-line. As with most text in row settings, it is recommended that you do not change the defaults.
Parameters that affect ArcSDE log file tables and indexes
Log file tables are used by ArcGIS to maintain temporary and persistent sets of selected records.
Log file parameters affect log file data tables and indexes. Most of these parameters begin with the letter L. The parameters are as follows:
|
Parameter |
Description |
|---|---|
|
LD_INDEX_ALL |
Defines SDE_logfile_data and SDE_logpool tables primary key storage |
|
LD_STORAGE |
Defines configuration for the SDE_logfile_data and SDE_logpool_<sde_id> tables |
|
LF_CLUSTER_ID |
The index type for SDE_logfiles primary key |
|
LF_CLUSTER_NAME |
The index type for unique index on the log file_name column of the SDE_logfiles table. |
|
LF_INDEX_ID |
Defines SDE_logfiles primary key storage. |
|
LF_INDEX_NAME |
Defines storage for the SDE_logfiles unique index. |
|
LF_STORAGE |
Defines the configuration for the SDE_logfiles table |
|
SESSION_TEMP_TABLE |
Controls whether or not session and stand-alone log file tables are created in the tempdb database; by default, it is set to 1, meaning session and stand-alone log files are created in tempdb. Creating session log file tables in the tempdb database is the recommended log file configuration for SQL Server. No special CREATE TABLE permission is required because every login has permission to create objects in tempdb. Having log file tables as temporary tables offloads the burden placed on the transaction log to the tempdb database. Tempdb logs transactions against tables more efficiently than regular databases do because its transaction log is only required for rolling back transactions and not for recovery. Because temporary tables are deleted when their session disconnects, there is no permanent overhead associated with their storage. If you have several active geodatabases, they may all be using tempdb for log files. While you can configure each instance to use log files differently, you should watch tempdb activity to see if the database becomes hot and causes an I/O bottleneck. The tempdb database can be easily moved to a different disk volume and does not need to be managed for recovery in the same way that other databases are; for instance, it does not need to reside on a RAID volume. See SQL Server Books Online for more information on managing the tempdb database. |
For information on ArcSDE log file tables, see ArcSDE log file table configuration options for SQL Server.
Additional configuration parameters
Some parameters do not fit well in a particular category. These are described in this section.
CROSS_DB_QUERY_FILTER parameter
The CROSS_DB_QUERY_FILTER parameter has two possible settings, 0 and 1. It only applies to multidatabase models (in which an SDE database holds the geodatabase repository and additional databases hold user-defined data). By default, CROSS_DB_QUERY_FILTER is set to 0. CROSS_DB_QUERY_FILTER controls whether or not a connecting user can view rasters or feature classes across database boundaries. In a multiple spatial database geodatabase, you can access rasters and feature classes in any database that participates in the geodatabase, regardless of the database to which you connect. By setting CROSS_DB_QUERY_FILTER to 1, you can only view and access rasters and feature classes in the database to which you have explicitly connected. For example, given a multidatabase geodatabase composed of SDE, fisheries, watershed, and coasts databases, if CROSS_DB_QUERY_FILTER is set to 1, a user that connects to the fisheries database cannot view rasters or feature classes in the watershed database. In this case, it is recommended that you migrate the data from a multiple spatial database to single database geodatabase. In a single database model geodatabase, CROSS_DB_QUERY_FILTER is not used.
NUM_DEFAULT_CURSORS parameter
The NUM_DEFAULT_CURSORS parameter controls the SQL Server cursor threshold. It specifies the number of rows in a cursor set for which cursor keysets will be generated asynchronously. The default value of -1 means all keysets are generated synchronously, which is better for smaller cursor sets. If you set it to 0, all cursor keysets are generated asynchronously. If you use a value other than 0 or -1, the 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. Asynchronous population means that you can access rows already in the cursor while the cursor is being populated. With synchronous population, all rows are put in the cursor before any are accessible.
Note:It is best not to alter the default value; changes made to this value affect the entire server. It is very difficult to determine how big your average cursor keyset is going to be. Unless you know for certain that changing this value is going to help performance, it is best not to change it.
PERMISSION_CACHE_THRESHOLD
When you make a connection to the geodatabase from the Catalog window or when you hit the Add data button in ArcMap, a list must be generated that indicates which datasets you have permission to access. To get this list, ArcGIS queries SQL Server system information. In databases with a large number of objects and users, it can be considerably faster to build a temporary copy of this information specific to the connecting user.
The PERMISSION_CACHE_THRESHOLD automatically builds a temporary table if the initial query of object permission information exceeds the threshold. (The default threshold is 250 milliseconds, the maximum setting is 1,000 milliseconds.)
PERMISSION_CACHE_THRESHOLD can be used only in the DEFAULTS parameter group. The temporary table persists for the duration of the connection, so if your user's permissions change during the session, changes won't be seen until the user disconnects and reconnects to the database.
If you are using the ArcSDE 9.2 Service Pack 2 or greater release, you may see improved performance by disabling the PERMISSION_CACHE_THRESHOLD. The query to obtain permission information has been modified and has made the temporary cache unnecessary. To disable creation of this temporary table, set PERMISSION_CACHE_THRESHOLD to -1 in the SDE_dbtune table.
User interface parameters
User interface parameters begin with UI and indicate whether their associated configuration keyword will be available through the ArcGIS user interface and ArcObjects. UI_TEXT is used for noncomposite configuration keywords. UI_TOPOLOGY_TEXT is used for topology keywords. UI_TERRAIN_TEXT is used for terrain keywords. UI_NETWORK_TEXT is used for network keywords. See Making configuration keywords available in ArcGIS for more information on how to use UI parameters.