Minimize disk I/O contention in SQL Server

Minimize disk I/O contention in SQL Server
Release 9.3

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

• Give the data files a large initial size, then use the Enterprise Manager or SQL Server Management Studio to increase the autogrowth increment of both the database and transaction log file. (Make an initial backup before increasing the autogrowth increment.)
• Store all your data files, transaction log files, and tempdb away from the paging file unless you are sure your server will never page. Separate your data files from your transaction log files and tempdb.

NOTE: Employ data segregation strategies (keeping tables from indexes or certain types of tables from other tables) only if you are certain it will improve performance or alleviate administrative burdens.

• Leave AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS enabled. Disable AUTOSHRINK and AUTOCLOSE.
• Employ a hardware-striping solution, favoring RAID over file groups and files.
• Increase the size of the network packet size setting.
ArcSDE for Microsoft SQL Server stores geometry in an image data type column of the feature table. Several geodatabase network tables use image type columns. Microsoft recommends increasing the size of the network packet size setting when employing image data type columns.
Network packet size is the size of the tabular data scheme (TDS) packets used to communicate between applications and the relational database engine. The default packet size is 4 KB and is controlled by the network packet size configuration option.
ArcSDE, by default, sets this to 8,192, double its default setting of 4,096K. You can also make this setting global to your Microsoft SQL Server by using the sp_configure statement's network packet size setting.

sp_configure 'show advanced options',1
reconfigure with override
"go"
sp_configure 'network packet size',8192
reconfigure with override
"go"



You can control the network packet size setting with the environment variable SDEPACKETSIZE, although it is recommended that you stay with the default setting.
• Determine an optimal table text_in_row setting.
SQL Server allows up to 7 KB of image, text, or n text data to be stored directly in a row on a data page. Text-in-row permits you to place image type data directly in the data page, and anything exceeding the setting is placed in a traditional image page.
This setting is enabled on tables using the command sp_tableoption. ArcSDE does this automatically for you through the SDE_dbtune table. The SDE_dbtune table sets a default of 256 bytes for many settings.
This may not be the best size for all your data. You do not want to allow too much text_in_row space on your data pages if you cannot fill it. For example, it would be unwise to set this option to 7,000 because you would create many more data pages and extents, thus lengthening the distance a query must travel to retrieve data. Likewise, underestimating this number would create more image data pages and could produce the same problem.
One way to determine an optimal table text_in_row setting is to estimate the 80th percent size of all your image type records and store up to that size in the *_TEXT_IN_ROW setting. First you'll have to load your data, then run this query:

select top 20 percent(datalength(points)) from boris.f12 order by datalength(points) desc



This query will list the top 20 percent largest data records in the points column of a feature class's feature table. You then pick the smallest value from its result set and use that for a SDE_dbtune entry and reload your data, referencing that keyword. In this case, the 80th percent value equaled 455, so you would make this entry in the SDE_dbtune table:



keyword	parameter_name	config_string
CARTO	F_TEXT_IN_ROW	455

Now reload this data referencing this keyword to pick up the new F_TEXT_IN_ROW setting. You could also put this value in the DEFAULTS keyword, but then all data loads would use this size.

shp2sde –o create –l silos,shape –f silos –a all –D carto –s bigboy –u boris –p badinoff –k CARTO



You may want to check if a particular table has been enabled for text in row and what its current size is set to. Use the OBJECTPROPERTY Transact-SQL metadata function:

select objectproperty(object_id('boris.f12'),'tabletextinrowlimit')



Text_in_row for rasters
A raster stored in ArcSDE is composed of four tables. Of those, the sde_blk_<n> table holds the largest image type column. You can enable text_in_row for this table with the SDE_dbtune entry BLK_TEXT_IN_ROW. For example:

RASTER	BLK_TEXT_IN_ROW	2000



This will entail changing the raster tile size as well. We recommend that you stay with the defaults, as setting a large text_in_row will create too many data pages and extents, eventually slowing your queries.