SQL Server DBTUNE configuration parameters

SQL Server DBTUNE configuration parameters
Release 9.3

NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only

• 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.

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_USER	Index type for any user defined indexes on a business table; 0 = nonclustered, 1 = clustered
B_CLUSTER_XML	Index type for the XML doc type column of a business table; 0 = nonclustered, 1 = clustered
B_CLUSTER_RASTER	Index type for the raster column in a business table; 0 = nonclustered, 1 = clustered
B_INDEX_ROWID	FILLFACTOR and location (file group) for the rowid 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	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	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	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	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 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. ArcSDE 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. In the dbtune.sde file, this setting would look like the following: B_MS_SPINDEX "GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM), CELLS_PER_OBJECT = 16"
B_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
B_STORAGE	File group location for a business table Use ON to control location; for example: B_STORAGE "ON ADDS_FG"

For 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 ObjectID 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 ArcSDE 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 multiversioned table that stores edits made against a feature class in a multiversioned 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 rowid column on an adds table; 0 = nonclustered index, 1 = clustered index
A_CLUSTER_SHAPE	Index type for shape column of an adds table; 0 = nonclustered index; 1 = clustered index
A_CLUSTER_STATEID	Index type for the stated column of an adds table; 0 = nonclustered index, 1 = clustered index This parameter does not have a corresponding business table parameter.
A_CLUSTER_USER	Index type for any user defined indexes on an adds table; 0 = nonclustered, 1 = clustered
A_CLUSTER_XML	Index type for the XML doc type column of an adds table; 0 = nonclustered, 1 = clustered
A_CLUSTER_RASTER	Index type for a raster column in an adds table; 0 = nonclustered, 1 = clustered
A_INDEX_ROWID	FILLFACTOR and location (file group) for the ROWID 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	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	FILLFACTOR and location (file group) for the stated 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" This parameter does not have a corresponding business table parameter.
A_INDEX_USER	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	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	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. ArcSDE 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. The setting for this parameter in the dbtune.sde file would look like following: A_MS_SPINDEX "GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM), CELLS_PER_OBJECT = 16"
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
A_STORAGE	File group location for an adds table Use ON to control location; for example: A_STORAGE "ON ADDS_FG"

Nonspatial tables have no shape column, so you should cluster another index.
• Deletes table parameters
The deletes table is used to track updates and deletes against multiversioned tables. They work in the same fashion as adds table parameters. All deletes table parameters begin with D. They are as follows:



Parameter	Description
D_CLUSTER_ALL	Index type for SDE_STATES_ID, SDE_DELETES_ROW_ID, and DELETED_AT column; 0 = nonclustered, 1 = clustered
D_CLUSTER_DELETED_AT	Index type for DELETED_AT column; 0 = nonclustered, 1 = clustered
D_INDEX_ALL	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	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	File group location for a deletes table 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 multiversioned inserts and updates. All feature table parameters begin with F.



Parameter	Description
F_CLUSTER_FID	Index type for the fid column; 0 = nonclustered, 1 = clustered
F_INDEX_AREA	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	FILLFACTOR and location (file group) for the index on the fid column; for example: F_INDEX_FID "WITH FILLFACTOR = 90 ON F_IDX"
F_INDEX_LEN	FILLFACTOR and location (file group) for the index on the len 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_TEXT_IN_ROW	Amount, in bytes, of image (SE_blob) data type (vector or raster) to store directly in the data page of the f table

• Raster table parameters
Rasters in ArcSDE 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. Rasters can be stored as embedded catalogs or columns to ArcSDE 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. Make certain the BND_CLUSTER_COMPOSITE config_string is set to 1 to ensure a clustered index is generated for this table.
The raster table parameters are as follows:



Parameter	Description
AUX_CLUSTER_COMPOSITE	Index type for the primary key of the auxiliary table; 1 = clustered, 0 = nonclustered
AUX_INDEX_COMPOSITE	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	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; 1 = clustered, 0 = nonclustered.
BLK_INDEX_COMPOSITE	File group location for the block table Use ON keyword to specify location; for example: BLK_INDEX_COMPOSITE "WITH FILLFACTOR = 95 ON BLK_FG"
BLK_STORAGE	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; 1 = clustered, 0 = nonclustered
BND_CLUSTER_ID	Index type for the raster_id and sequence_nbr columns of the band table; 1 = clustered, 0 = nonclustered
BND_INDEX_COMPOSITE	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	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	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; 1 = clustered, 0 = nonclustered.
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. These tables store 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 in the "Image and raster data management" section of the help. To learn how to create custom configuration keywords, see DBTUNE 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 incur sporadic updates? 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 their 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 config_string column above.
Alter table features.dbo.f5 add constraint f5_pk primary key clustered (fid) with FILLFACTOR=75
• Spatial index parameters
The spatial index 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 for datasets that use binary storage (SDEBINARY or OGCWKB) begin with S.



Parameter	Description
S_CLUSTER_ALL	Index type for primary key (all columns of table); 1 = clustered, 0 = nonclustered
S_CLUSTER_SP_FID	Index type for sp_fid column; 1 = clustered, 0 = nonclustered.
S_INDEX_ALL	FILLFACTOR and location (file group) for the primary key index; for example: S_INDEX_ALL "With FILLFACTOR = 90 on S_IDX"
S_INDEX_SP_FID	FILLFACTOR and location (file group) for sp_fid column index; for example: S_INDEX_SP_FID "WITH FILLFACTOR = 85 on S_IDX"
S_STORAGE	File group location for 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 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 in row parameters
SQL Server 2000 and 2005 support the ability to store image, text, and n text values directly in a data row. Previous versions of SQL Server would store the images types in image data pages. The advantage of storing some image types inline in a data row, is that you can speed fetching of your data since queries will traverse fewer pages to find your data. ArcSDE uses an image type column to store its geometry in SQL Server databases.
The SQL Server and ArcSDE default for text in a row is 256 bytes but can span from 24 to 7,000 bytes. Be forewarned that the more image data you store in a row, the larger your table will become, eventually slowing your queries as more pages must be traversed. Raster data should not be stored in a row unless you can shrink the raster tile size to 83 x 83 to fit an image within the 7 KB page limit. This is only realistic if you store small 1-bit images or 16 x 16 byte icons. Points do not need to be stored in a row because spatial queries against them reconstruct the features from their envelopes. By default, all ArcSDE objects are enabled for text in a row up to 256 bytes.
If you want to change this default setting, try to store up to 80 percent of your vector data inline up to 1 KB. You can ascertain this size by running the following query:

select top 20 percent(datalength(points)) from <feature table> 
order by datalength(points) desc



Pick the smallest value from this result set. Raster data is usually much larger. If you attempt to store too much raster or vector data in line, you'll end up creating too many extents and data pages.
These parameters end in _TEXT_IN_ROW; for example, B_TEXT_IN_ROW is the text in row parameter for the business table, A_TEXT_IN_ROW is the text in row parameter for the Adds table, and XML_DOC_TEXT_IN_ROW is the parameter for storing XML documents.
• 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 (table, feature class, etc.) 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()



The COLLATION_NAME parameter is especially important for Turkish collation users. To avoid uppercasing and lowercasing issues with the letter "i" in the Turkish alphabet, Turkish language users should set the COLLATION_NAME parameter to indicate they are using a Turkish collation.

Parameters that affect spatial storage

• GEOMETRY_STORAGE
ArcSDE for SQL Server provides three 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:
• ArcSDE compressed binary format (SDEBINARY)—This is the default spatial storage method of ArcSDE for SQL Server. Keep the GEOMETRY_STORAGE parameter set to SDEBINARY if you want to store your spatial data in this format. If the GEOMETRY_STORAGE parameter is not set, the SDEBINARY format is assumed.
• 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.
• 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. If you want to make this format the default storage type for your geodatabase, set the GEOMETRY_STORAGE parameter to GEOMETRY under the DEFAULTS configuration keyword parameter list.
• 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.
If all of the feature classes in your database use the same geometry storage method, set the GEOMETRY_STORAGE parameter once in the DEFAULTS configuration keyword.
For more information on geometry storage, see About geometry storage types. You can also read the topics ArcSDE Compressed Binary storage, The OGC Well-Known Binary representation, and The Microsoft SQL Server spatial types in the "Geodatabase data storeage and schema" section of the help.
• GEOM_SRID_CHECK
If you create feature classes in ArcGIS that uses the SQL Server geometry type or you create a spatial table with a SQL Server geometry column using SQL then register the table with ArcSDE, 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 ArcSDE, 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 will ensure 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 will be 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 ArcCatalog 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 8000 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 of 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 don't need to configure these parameters.

XML_DOC_INDEX
XML_DOC_STORAGE
XML_DOC_TEXT_IN_ROW

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_TEXT_IN_ROW

XML_IDX_FULLTEXT_CAT
XML_IDX_FULLTEXT_LANGUAGE
XML_IDX_FULLTEXT_TIMESTAMP
XML_IDX_FULLTEXT_UPDATE_METHOD

Parameters that affect ArcSDE log file tables and indexes

Additional configuration parameters

• User interface parameters
User interface parameters begin with UI and indicate whether or not their associated configuration keyword will be available through the ArcGIS user interface.
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.
• 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 SDE and geodatabase metadata 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 multidatabase 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 comprised 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 multidatabase to single database geodatabase. In a single database model geodatabase, CROSS_DB_QUERY_FILTER is not used. See the SQL Server sections of Using multiple geodatabases within a DBMS for more information on the multidatabase model for SQL Server.
• 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 will 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 ArcCatalog 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, ArcSDE 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 1000 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 SQL Server 2005 and 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.
• Comments
You can add a COMMENT parameter in the dbtune.sde file if you like by adding a line beginning with a single pound sign (#). You might do this if you create your own custom keywords and want to add comments on how or when the keyword should be used.
For example, you could add a comment to a user's log file keyword:

#COMMENT		"This keyword is used by ArcSDE to create log file tables for all users logged in as editor"