Server characteristics affecting global optimization

When you create or alter a server definition, some of the options that you choose can affect query performance.

You provide the query optimizer with information about the data source server characteristics through the server option settings. The server option settings are part of the data source server definition. You can set server options in the CREATE SERVER statement, when you initially establish the server definition. Use the ALTER SERVER statement to add server options to an existing server definition. The server option settings are stored in the federated database global catalog.

These options are classified as location options (such as the data source computer name), security options (such as authentication information), and performance options (such as the CPU ratio).

The performance options help the optimizer determine if the evaluation of an operation can be done at a data source and if the evaluation of an operation on the data source makes execution faster. The server options affecting performance that might require your tuning are:
  • CPU_RATIO
  • IO_RATIO
  • COMM_RATE
  • COLLATING_SEQUENCE
  • PLAN_HINTS
  • VARCHAR_NO_TRAILING_BLANKS
  • NO_EMPTY_STRING

Use caution when tuning the CPU_RATIO, IO_RATIO, or COMM_RATE server options as you can get unexpected errors if the cost calculation for a query causes overflows or underflows. In most cases, the default values for these options are sufficient. Typically, ensuring that the statistics about the objects referenced in your queries are correct is more important than tuning the values of these server options.