• Article

Server Properties (Advanced Page)

Use this page to view or modify your advanced server settings.

To view the Server Properties pages

Filestream

  • Filestream Access Level
    Shows the current level of FILESTREAM support on the instance of SQL Server. To change the access level, select one of the following values:

    • Disabled
      Binary large object (BLOB) data cannot be stored on the file system. This is the default value.

    • Transact-SQL Only
      FILESTREAM data is accessible by using Transact-SQL, but not through the file system.

    • Transact-SQL and file system (local client access only)
      FILESTREAM data is accessible by using Transact-SQL and through the file system.

    When you enable FILESTREAM for the first time, you might have to restart the computer to configure drivers.

Miscellaneous

  • Allow Triggers to Fire Others
    Allows triggers to fire other triggers. Triggers can be nested to a maximum of 32 levels. For more information, see the "Nested Triggers" section in CREATE TRIGGER (Transact-SQL).

  • Blocked Process Threshold
    The threshold, in seconds, at which blocked process reports are generated. The threshold can be set from 0 to 86,400. By default, no blocked process reports are produced. For more information, see blocked process threshold Option.

  • Cursor Threshold
    Specifies the number of rows in the cursor set at which cursor keysets are generated asynchronously. When cursors generate a keyset for a result set, the query optimizer estimates the number of rows that will be returned for that result set. If the query optimizer estimates that the number of returned rows is greater than this threshold, the cursor is generated asynchronously, allowing the user to fetch rows from the cursor while the cursor continues to be populated. Otherwise, the cursor is generated synchronously, and the query waits until all rows are returned.

    If set to -1, all keysets are generated synchronously; this benefits small cursor sets. If set to 0, all cursor keysets are generated asynchronously. With other values, the query optimizer compares the number of expected rows in the cursor set and builds the keyset asynchronously if it exceeds the number set. For more information, see cursor threshold Option.

  • Default Full Text Language
    Specifies a default language for full-text indexed columns. Linguistic analysis of full-text indexed data is dependent on the language of the data. The default value of this option is the language of the server. For the language that corresponds to the displayed setting, see sys.fulltext_languages (Transact-SQL).

  • Default Language
    The default language for all new logins, unless otherwise specified.

  • Full-Text Upgrade Option
    Controls how full-text indexes are migrated when upgrading a database from SQL Server 2000 or SQL Server 2005 to SQL Server 2008 or later version. This property applies to upgrading by attaching a database, restoring a database backup, restoring a file backup, or copying the database by using the Copy Database Wizard.

    The alternatives are as follows:

    • Import
      Full-text catalogs are imported. This operation is significantly faster than Rebuild. However, an imported full-text catalog does not use the new and enhanced word breakers that are introduced in SQL Server 2008. Therefore, you might want to rebuild your full-text catalogs eventually.

      If a full-text catalog is not available, the associated full-text indexes are rebuilt. This option is available for only SQL Server 2005 databases. 

    • Rebuild
      Full-text catalogs are rebuilt using the new and enhanced word breakers. Rebuilding indexes can take awhile, and a significant amount of CPU and memory might be required after the upgrade.

    • Reset
      Full-text catalogs are reset. SQL Server 2005 full-text catalog files are removed, but the metadata for full-text catalogs and full-text indexes is retained. After being upgraded, all full-text indexes are disabled for change tracking and crawls are not started automatically. The catalog will remain empty until you manually issue a full population, after the upgrade completes. 

    For information about how to choose the full-text upgrade option, see Full-Text Search Upgrade.

    Note

    The full-text upgrade option can also be set by using the sp_fulltext_serviceupgrade_option action.

    After you attach, restore, or copy a SQL Server 2005 or SQL Server 2000 database to SQL Server 2008, the database becomes available immediately and is then automatically upgraded. If the database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the Full-Text Upgrade Option server property. If the upgrade option is set to Import or Rebuild, the full-text indexes will be unavailable during the upgrade. Depending on the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. Note also that when the upgrade option is set to Import, if a full-text catalog is not available, the associated full-text indexes are rebuilt. For information about viewing or changing the setting of the Full-Text Upgrade Option property, see How to: View or Change Server Properties for Full-Text Search (SQL Server Management Studio).

  • Max Text Replication Size
    Specifies the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), xml, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. Changing the setting takes effect immediately. For more information, see max text repl size Option.

  • Open Objects
    Specifies the maximum number of database objects that can be open at one time on an instance of Microsoft SQL Server. Only available for SQL Server 2000.

  • Scan For Startup Procs
    Specifies that SQL Server will scan for automatic execution of stored procedures at startup. If set to True, SQL Server scans for and runs all automatically run stored procedures defined on the server. If set to False (the default), no scan is performed. For more information, see scan for startup procs Option.

  • Two Digit Year Cutoff
    Indicates the highest year number that can be entered as a two-digit year. The year listed and the previous 99 years can be entered as a two-digit year. All other years must be entered as a four-digit year.

    For example, the default setting of 2049 indicates that a date entered as '3/14/49' will be interpreted as March 14, 2049, and a date entered as '3/14/50' will be interpreted as March 14, 1950.

Network

  • Network Packet Size
    Sets the packet size (in bytes) used across the whole network. The default packet size is 4096 bytes. If an application does bulk-copy operations or sends or receives large amounts of text or image data, a packet size larger than the default may improve efficiency, because it results in fewer network reads and writes. If an application sends and receives small amounts of information, you can set the packet size to 512 bytes, which is sufficient for most data transfers. For more information, see network packet size Option.

    Note

    Do not change the packet size unless you are certain that it will improve performance. For most applications, the default packet size is best.

  • Remote Login Timeout
    Specifies the number of seconds SQL Server waits before returning from a failed remote login attempt. This setting affects connections to OLE DB providers made for heterogeneous queries. The default value is 20 seconds. A value of 0 allows for an infinite wait. For more information, see remote login timeout Option.

    Changing the setting takes effect immediately.

Parallelism:

  • Cost Threshold for Parallelism
    Specifies the threshold above which SQL Server creates and runs parallel plans for queries. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. Only set this option on symmetric multiprocessors. For more information, see cost threshold for parallelism Option.

  • Locks
    Sets the maximum number of available locks, thereby limiting the amount of memory SQL Server uses for them. The default setting is 0, which allows SQL Server to allocate and deallocate locks dynamically based on changing system requirements.

    Allowing SQL Server to use locks dynamically is the recommended configuration. For more information, see locks Option.

  • Max Degree of Parallelism
    Limits the number of processors (up to a maximum of 64) to use in parallel plan execution. The default value of 0 uses all available processors. A value of 1 suppresses parallel plan generation. A number greater than 1 restricts the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. For more information, see max degree of parallelism Option.

  • Query Wait
    Specifies the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, the time-out is calculated as 25 times of the estimated query cost. For more information, see query wait Option.