Configuration Options and Performance
| This section explains essential information about most of the SQL Server configuration options and their impact on SQL Server performance. Many of the options don't have performance implications, but they will be discussed anyway. As part of each option's explanation, an indication of whether the option is Advanced or Basic will be given along with the option's default value and whether the option is self-configuring or not. Possible values will also be listed depending on the generalized application processing types that were identified earlier. Affinity Mask
SQL Server supports symmetric multiprocessing (SMP). SMP support means that a thread is not tied to a particular processor on the machine. This allows SQL Server to run multiple threads simultaneously , resulting in a high level of load balancing across processors. A value of (the default) allows Windows Scheduling algorithm to set the threads affinity. This qualifies the affinity mask as self-configuring by the operating system. However, when a server is experiencing a heavy load because of other applications running on the same server, it might be desirable to bind thread affinity to a processor. The affinity mask is a bitmapped field. Starting from the least significant digit, each bit that is set to 1 represents the processor on which SQL Server will spawn its threads. Processors are numbered from to 7 . An example of the bit mask values for the first seven processors of an eight-processor system follows ; decimal values are shown in parentheses:
You usually leave the eighth processor alone because many system processes ”such as domain controllers ”default to that processor. As an example, if you want to create the affinity for one SQL Server instance thread to use four processors of an eight-processor system, you could set this bit mask to be 15 (00001111). The result would be that SQL Server spawns its threads only on those processors, thus reducing overall reloading of the processor cache. This can be especially evident during heavy system loads. sp_configure 'affinity mask', 15 go RECONFIGURE Go In general, the default affinity value is able to provide ample load balancing across processors. Based on your particular processing load and application types, you will want to allocate CPUs accordingly . Below is a general recommendation of what to specify based on the different application types you are running:
From Enterprise Manager, SQL Server Properties, Processor tab, just select the targeted processors in the Processor Control section. Allow Update
By default, SQL Server does not allow updates to internal system tables. When you set the allow update value to 1 , any user with proper permissions can update system tables. Sometimes you might want to allow access to system tables. Following are a couple scenarios:
From Enterprise Manager, SQL Server Properties, Server Settings tab, select the Allow Modifications box, as shown in Figure 40.5. Figure 40.5. SQL Server Properties: Server Settings. AWE Enabled
SQL Server can use the Advanced Windowing Extensions (AWE) API to support large amounts of physical memory ”in fact, as much as 8GB of memory on Windows 2000 Advanced Server and up to 64GB on Windows 2000 Data Center. The default of tells SQL Server to use dynamic memory in standard 32-bit virtual address spaces. By enabling AWE, the SQL Server instance does not dynamically manage the size of the address space. The instance holds all memory acquired at startup until it is shut down, and memory pages come from the Windows nonpageable pool. This means that none of the memory of the instance can be swapped out. You end up with a great deal of all activity occurring in memory only. This is potentially a fast database engine. AWE enabled is usually used in conjunction with the max server memory option to control how much memory each SQL Server instance will use. sp_configure 'awe enabled', 1 go RECONFIGURE Go Setting of this option will vary according to the following application types:
Cost Threshold for Parallelism
SQL Server now supports parallel query execution. Before a query is executed, SQL Server's cost-based optimizer estimates the cost of execution for a serial plan, a plan that uses a single thread. The option to set the cost threshold for parallelism allows you to specify a threshold in seconds; if the cost of the serial execution plan (in seconds) is greater than the value specified by this parameter, SQL Server will consider a parallel query execution plan. A query will not become a candidate for parallel query execution simply based on this fact. Because parallel query execution is supported only on an SMP server, this value is ignored for non-SMP hardware. For an application that uses many complex queries, set this value to a lower number so that you can take advantage of the parallel query execution capabilities of SQL Server. sp_configure 'cost threshold for parallelism', 2 go RECONFIGURE Go Setting of this option will vary according to the following application types:
Cursor Threshold
This option allows you to specify when SQL Server should generate a cursor result set asynchronously. If the optimizer estimates that the number of rows returned by the cursor is greater than the value specified by this parameter, it will generate the result set asynchronously. The optimizer makes this decision based on the distribution statistics for each table that is participating in the join in the cursor. To determine the optimal value for this parameter, make sure that statistics are up to date (by running update statistics ) for the tables used in the cursors. By default, SQL Server generates a cursor result set synchronously. If you are using a fair amount of cursors that return a large number of result sets, setting this value to a higher value will result in better performance. Setting this value to will force SQL Server to always generate a cursor result set asynchronously. sp_configure 'cursor threshold', 100000 go RECONFIGURE go Setting of this option will vary according to the following application types:
Default Language
This option specifies the language ID currently in use by SQL Server. The default value is , which specifies the U.S. English system. As you add languages on the server, SQL Server assigns a new ID for each language. You can then use these IDs to specify the default language of your choice. You can add languages using the SQL Server setup program. Adding a language allows SQL Server to display error messages and date/time values in the format that is appropriate for that language. Set this option in the Server Settings tab of the SQL Server Properties dialog box. Fill Factor
The Fill Factor option allows you to define the percentage of free space on a data page or an index page when you create an index or a table. The value can range from 1 “100. Setting the value to 80 would mean each page would be 80 percent full at the time of the create index . SQL Server also allows you to specify the value of fill factor at the server level by providing a fill factor parameter. sp_configure 'fill factor', 90 go RECONFIGURE Go Setting of this option will vary according to the following application types:
As you can see in Figure 40.6, you set fill factor from the Database Settings tab in the SQL Server Properties dialog box. Just click on the Fixed option and slide the bar until you have the desired fullness of a page. Figure 40.6. SQL Server Properties: Database settings. Index Create Memory (KB)
The index create memory option is used to control the amount of memory used by index creation sorts. It is a self-configuring option and usually doesn't need to be adjusted. However, if you are having problems with the creation of large indexes, you might want to try specifying a KB value here that will contain the sort portion of the index create. sp_configure 'index create memory', 1000 go RECONFIGURE go Lightweight Pooling
Lightweight pooling is relevant to SMP environments that are having excessive context switching. By flipping this switch, you might get better throughput by performing the context switching inline, thus helping to reduce user/kernel ring transitions. Lightweight pooling causes SQL Server to switch to fiber mode scheduling. sp_configure 'lightweight pooling', 1 go RECONFIGURE go Setting of this option will vary according to the following application types:
Locks
In earlier versions of SQL Server, the DBA had to specify the number of locks available to SQL Server. If this parameter was set to a low value, a query requiring a large number of locks would fail at runtime. Setting it too high would result in wasting memory that otherwise could be used to cache data. SQL Server 2000 can handle locks dynamically if this parameter is set to the default value ( ). SQL Server initially allocates 2 percent of memory available to SQL Server. As lock resource structures are consumed, the lock manager allocates more lock resources to the pool to a maximum of 40 percent of the memory available on SQL Server. Unless you are certain of the overall lock consumption of your application, you probably don't need to change this value. sp_configure 'locks', 10000 go RECONFIGURE go Max Degree of Parallelism
This option specifies the number of threads to be used for parallel query execution. On a non-SMP server, this value is always ignored. For an SMP server, a default value of signifies that all the CPUs will be used for parallel query execution. If you set this value to 1 , all query plans will be serialized. If the affinity mask option is on, parallel query execution will take place only on the CPUs for which the affinity mask bit is turned on. In that way, the two options can be used in conjunction. The application types assessment will be the same as described in the affinity mask option. This option can be set up using the Processor tab of the SQL Server Configuration dialog box; then, in the Parallelism box, choose the number of processors to use for parallelism from the drop-down list box. sp_configure 'max degree of parallelism', 4 go RECONFIGURE go Max Server Memory and Min Server Memory
Max server memory specifies the maximum amount of memory (in terms of MB) that is available to SQL Server. It is used in conjunction with min server memory and essentially establishes an upper and lower bound for memory allocation. SQL Server uses this memory for user connections, locks, internal data structures, and caching the data. This is the memory pool described earlier. The default value of 2147483647 for the Max Server Memory option means that SQL Server will perform dynamic allocation of memory from the operating system based on available physical memory on the machine. The default value of for the Min Server Memory option means that SQL Server will start allocation memory as it is needed, and then never go below the minimum value after it is reached. The SQL Server lazywriter process is responsible for making sure that enough memory is available to SQL Server for the optimal number of buffers and Windows so that no excess paging occurs at the operating-system level. The lazywriter process frequently checks physical memory available on the machine. If the memory available is greater than 5MB, lazywriter assigns excess memory to the SQL Server buffer cache. In addition, watch the Working Set performance counter that shows the amount of memory used by a process (SQL Server in this case). If this number is consistently below the amount of memory for which SQL Server is configured, then SQL Server is configured for more memory than it needs. You can also adjust the Set Working Set Size configuration option. If SQL Server is the only application running on the machine, you might want to perform static memory allocation. Be careful when you allocate fixed memory to SQL Server. If you allocate more memory to SQL Server than the machine has, SQL Server will fail to start. Use the -f option during startup to bring up SQL Server with the default configuration. Change the value to the correct value, and restart SQL Server. sp_configure 'max server memory', 200 go RECONFIGURE Go sp_configure 'min server memory', 10 go RECONFIGURE go For a strict fixed allocation of memory for SQL Server, make the min and max values the desired allocation size the same (like 200MB). A fixed amount of memory will then be allocated for SQL Server. Figure 40.7 shows two possible settings for these configuration options. One shows the Dynamic Configure option set and a minimum and maximum value are established (other than the defaults). The other one shows the fixed memory specification. This fixed memory setting will result in the minimum and maximum values being set to the same desired value. Figure 40.7. SQL Server Properties: Memory settings. Setting of this option will vary according to the following application types:
Max Text Repl Size
This parameter specifies the maximum size of the text and image datatypes for columns that are participating in replication during single insert , update , writetext , and updatetext statements. You might need to raise this value if the image sizes with which your application deals are consistently large and the data is part of a replication configuration. sp_configure 'max text repl size', 131072 go RECONFIGURE go Max Worker Threads
SQL Server uses native operating system threads. This parameter specifies the maximum number of threads available for SQL Server processes. One or more threads are used for supporting each network protocol (such as TCP/IP and named pipes). SQL Server is configured to listen. The checkpoint and lazywriter processes also consume threads. A pool of threads is used to handle user connections. When the number of connections is lower than the max worker thread parameter value, a thread is created for each connection. When more connections are on the server than the value defined by the max worker thread parameter, SQL Server provides thread pooling for efficient resource utilization. More threads can create overhead on the system processors. Therefore, lowering this value might sometimes improve the performance of the system. For a system with a few hundred user connections, a reasonable value for this parameter is 125. You might want to experiment with various values to determine the appropriate setting for this parameter. An SMP environment can easily handle more threads, and you can increase the number of threads accordingly. sp_configure 'max worker threads', 125 go RECONFIGURE go Figure 40.8 shows the setting of the Max Worker Threads option from the Processor tab of the SQL Server Properties dialog box. Choose a value between 10 “ 1,024 for this option. Figure 40.8. SQL Server Properties: Processor settings. Setting of this option will vary according to the following application types:
Min Memory Per Query
Min memory per query specifies the minimum amount of memory that will be allocated for the execution of a query. Normally, the SQL Server query processor will attempt to determine the optimal amount of memory for a query. This option allows the sysadmin to specify this value instead. Increasing this value usually improves queries that handle hash and sort operations on a large volume of data. This option replaces the Sort Page option in SQL Server 7.0 and earlier. sp_configure 'min memory per query', 2048 go RECONFIGURE go Looking back at Figure 40.7, the Memory tab of the SQL Server Configuration dialog box, you can see the Minimum query memory value option set at 1024 (the default). Setting of this option will vary according to the following application types:
Nested Triggers
As the name suggests, nested triggers specifies whether a trigger event on a table will fire another trigger. The nesting level of triggers is 32 , and it used to be 16 . If you reach this limit of 32 , SQL Server will give an error and roll back the transaction. The default value of 1 means that a trigger on a table can cause another trigger to fire. Take a look again at Figure 40.5; you can see the Nested Trigger option checked under the Server Behavior box. Network Packet Size
This parameter specifies the default network packet size for SQL Server. Setting this value to a higher number (which should be divisible by 512) can improve the performance of applications that involve a large amount of data transfer from the server. Check your network configuration and set an appropriate value for this parameter. In this same regard, you can improve performance by lowering the size value for applications that are small in data transfer size. However, the usual scenario is to increase this size to accommodate large amounts of data transfer, as with Bulk Loads. sp_configure 'network packet size', 8192 go RECONFIGURE go
Setting of this option will vary according to the following application types:
Open Objects
This option is self-configuring by default (when 0 is the setting). SQL Server will increase or decrease the number of open object descriptors in memory based on the needs of the server. You will rarely have to change from the default. sp_configure 'open objects', 10000 go RECONFIGURE Go Priority Boost
This option is used to specify the process priority of SQL Server processes on the Windows NT or Windows 2000 operating system. The default value of means that SQL Server should run on the same priority level ”a priority base of 7 ”as other applications on the machine. Priority boost can be turned on if you have plenty of horsepower to deal with all other services on the box, as in an SMP environment. When turning on priority boost, the priority base of SQL Server is elevated to 13 . sp_configure 'priority boost', 1 go RECONFIGURE go
From Figure 40.8, you can see the Boost SQL Server Priority on Windows option in the Processor control function. Use care when applying this option. Query Governor Cost Limit
Queries are often the cause of major performance problems. SQL Server can handle the queries, but many are poorly written and don't restrict the search criteria enough. This can result in runaway queries that are returning large result sets and can adversely affect the entire server's performance. A method to control this is to cut the query off at the pass by specifying a maximum cost limit to queries. If any query's cost is greater than this maximum value, the query is not allowed to execute. This value is server-wide and cannot be applied to just one query. sp_configure 'query governor cost limit', 300 go RECONFIGURE Go Query governor cost limit can be set by going to Server Behavior options, SQL Server Configuration dialog box, Server Settings tab. Setting of this option will vary according to the following application types:
Query Wait
Queries that are memory intensive and involve huge sorts might take a long time to execute based on the available memory during execution. SQL Server internally calculates the timeout interval for such queries. Usually, this is quite a large number. You can override this value by specifying a value (in seconds) using the query wait parameter of SQL Server. If you set this value too low, you risk more frequent query timeouts when your system is under a heavy load and a highly concurrent environment. sp_configure 'query wait', 20 go RECONFIGURE go Recovery Interval
Recovery interval is used to specify the maximum time (in minutes) that SQL Server would require to recover a database during startup. During startup, SQL Server rolls forward all the changes that were committed during a SQL Server crash and rolls back the changes that were not committed. Based on the value specified by this parameter, SQL Server determines when to issue a checkpoint in every database of SQL Server so that in the event of a crash, SQL Server can recover the databases in a time specified by recovery interval. If the value of the recovery interval parameter is low, SQL Server will issue checkpoints more frequently to allow a recovery to be faster; however, frequent checkpoints can slow down the performance. Setting recovery interval too high will create a longer recovery time for databases in the event of a crash. The default value of leaves this option open to SQL Server to determine the best value. sp_configure 'recovery interval', 10 go RECONFIGURE go Figure 40.6 shows the Recovery Interval option setting of within the Recovery section. Values must be between 1 and 32,767 . Setting of this option will vary according to the following application types:
Remote Proc Trans
Remote proc trans allows remote procedures that are taking part in multiserver transactions to use MS-DTC so that transaction integrity is maintained across servers. The default value of means the remote procedure calls will not use MS-DTC. Data modification at the remote server will not be a part of transactions at the local server. If you set this parameter to 1 , SQL Server uses MS-DTC to preserve transaction integrity across servers. sp_configure 'remote proc trans', 1 go RECONFIGURE Go Figure 40.9 illustrates the Enforce Distributed Transactions (MTS) option being set in the Remote Server Connections section of the Connections tab. This will primarily be important in OLTP environments. Figure 40.9. SQL Server Properties: Connection settings. Setting of this option will vary according to the following application types:
Scan for Startup Procs
When this option is set to 1 , SQL Server will scan for and execute all automatically executed stored procedures on the server on startup. To set a stored procedure to become automatically executed, you use the sp_procoption system-stored procedure. Executing a stored procedure at startup time is typically done when you want to have certain processing occur that creates the proper working environment for all subsequent database processing on the server. Executing at startup can also be done when you want to make sure that certain stored procedures' execution plans (with proper optimizer decisions) are already in procedure cache before anyone else has requested their execution. sp_configure 'scan for startup procs', 1 go RECONFIGURE go Set Working Set Size
When set working set size is set to 1 , SQL Server allocates and locks the requested server fixed memory amount at startup. Min server memory and max server memory options must contain the same value, reflecting a fixed memory size request. This effectively guarantees you the memory for as long as SQL Server is up and running and doesn't ever get pages swapped out when SQL Server is idle. This option has no effect when the memory option is set to ; SQL Server is configured for dynamic memory allocation. sp_configure 'set working set size', 1 go RECONFIGURE Go Looking at Figure 40.7, you can request that SQL server use a fixed memory size and also reserve physical memory for SQL Server. Set working set size is activated when you check this Reserve Physical Memory for SQL Server box. You cannot use this option when you are allowing SQL Server to dynamically configure memory. Setting of this option will vary according to the following application types:
Show Advanced Options
By default, you will not see the advanced configuration parameters of SQL Server. By setting show advanced options to 1 , you will be able to see all the SQL Server parameters that can be set by the sp_configure command. User Connections
User connections specifies the number of concurrent users that are allowed on SQL Server. When the value is , SQL Server can configure the needed user connections dynamically as they are needed. If you specify a value, you will be limited to this maximum number of user connections until you specify a larger value. If you specify other than a value, the memory allocation for user connections will be allocated at SQL Server startup time and burn up portions of the memory pool. Each connection takes up 40KB of memory space. If you configure SQL Server for 100 connections, SQL Server will pre-allocate 4MB (40KBx100) for user connections. You can see that setting this value too high might eventually impact performance because the extra memory could have been used to cache data. In general, user connections are now best left to be self-configuring. sp_configure 'user connections', 200 go RECONFIGURE go Looking back at the SQL Server Properties dialog box and the Connections tab in Figure 40.9, you can set a value for the User Connections option by entering a value in the Maximum Concurrent User Connections box. This value must be between 5 and 32,767 . User Options
User options allows you to specify certain defaults for all the options allowed with the SET T-SQL command. Individual users can override these values by using the SET command. You are essentially able to establish these options for all users unless the users override them for their own needs. User options is a bit-mask field, and each bit represents a user option. Table 40.1 outlines the values that you can set with this parameter. Table 40.1. Specifying User Options
For a given user connection, you can use the @@options global variable to see the values that have been set. sp_configure 'user options', 256 go RECONFIGURE Go As you can see from the SQL Server Configuration dialog box and the Connections tab in Figure 40.9, the Default connection options can be checked or unchecked according to what you want to be in effect server-wide for all user connections. Again, a user can override these with his own SET command during a session. |
EAN: 2147483647
Pages: 503