Minimize disk I/O contention in SQL Server

The following are recommendations on ways to avoid disk I/O contention in an ArcSDE for Microsoft SQL Server database. For a discussion of the SQL Server concepts mentioned below, such as tempdb and file groups, consult your SQL Server Books Online documentation.

• Give the data files a large initial size, then use 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 database options 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 a varbinary(max) data type column of the feature table. Several geodatabase network tables use varbinary(max) type columns. Microsoft recommends increasing the network packet size setting when employing varbinary(max) 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 SQL Server instance 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.