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 2000 and 2005 allow up to 7 KB of image, text, or n text data to be stored directly in a row on a data page. SQL Server 7.0 stored these data types in separate image pages, located with a 16-byte pointer in the table's data page. Text-in-row at SQL Server 2000 and 2005 permits you to place image type data directly in the data page, and anything exceeding the setting is placed in a traditional image page. All ArcSDE geometry is stored in an image type column in the feature table. ArcSDE raster layers and geodatabase network tables use image data types.
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 don't want to allow too much text_in_row space on your data pages if you can't 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')



You may not want to reload your data. In this case, you can enable a table for text_in_row with sp_tableoption and write a query to copy all the existing image data types into a new column:

/*
  FeatureTable_Text_in_Row.sql
  script to copy existing points data into new column
  with new text in row value enabled

  SQL Server 2000 Only

  Script Unsupported by ESRI
*/

sp_tableoption 'boris.f12','text in row',455
"go"
alter table boris.f12
add newpoints image
"go"
update boris.f12
set newpoints=points
"go"
alter table boris.f12
drop column points
"go"
sp_rename 'boris.f12.newpoints','points','column'
go



If you have a lot of existing data in SQL Server 7.0 and desire to move this data into SQL Server 2000 or 2005, you'll want to move this data into the new text_in_row format. How you perform your upgrade will determine how your data is moved from a traditional image page into text_in_row.
Text_in_row is enabled with the sp_tableoption command, and ArcSDE sets a text_in_row default (in the SDE_dbtune table) for all feature tables to 255 bytes. This means that any layer or feature class creation in ArcSDE on SQL Server 2000 will use the text_in_row option. To upgrade an existing SQL Server 7.0 database to SQL Server 2000 and push your image data into text_in_row format, you can
• Perform a geodatabase copy and paste between two ArcSDE servers, one running SQL Server 7.0 and the other SQL Server 2000 or 2005.
• Reload all your data into ArcSDE on SQL Server 2000 or 2005.
• Run the SQL Server 2000 upgrade wizard or restore your SQL Server 7.0 databases to a SQL Server 2000 or 2005 instance. Once you have done this, run the FeatureTable_Text_in_Row.sql listed above.
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.