Optimize SQL Server 2000 settings

Optimize SQL Server 2000 settings

SQL Server has been well configured to optimize its own performance, which is much better than other relational databases on the market today. However, you still have several settings that need to be modified so that your database can process more transactions (TPM) per minute ). This articleArticleThe purpose is to discuss these settings. We ignore the performance that can be improved through hardware configuration or table or index design, because this is beyond the scope of this article.

Broken page Detection

Before we start to discuss the server configuration switch, let's take a quick look at your model database ?? Or as a basic template for building a new database. By default, you can create stored procedures, functions, and other similar things in the database, and then they will be added to the newly created database.

To optimize the performance, you may want to disable the broken page detection in the model database. When a page is successfully written to a disk, the broken page is detected for recognition. If it is activated, you can see every small impact on the performance of each write operation. Most modern disk arrays have on-board batteries that allow the array to complete all write operations when a sudden power failure occurs ?? The most frequent cause of page breakage.

Follow these steps to disable broken page Detection:

Exec sp_dboption 'model', 'torn page detail', 'false'

This basic knowledge resource provides you with more information about this setting.

Server Configuration

Most configurations are completed through the system storage process sp_configure. To display the list of all settings of the server for customization, enter the following command:

Sp_configure 'show advanced options', 1

Go

Reconfigure with override

The number of options you can configure depends on your SQL Server version, service package, and number of digits (64-bit SQL Server is more than 32-bit options. I will discuss directly the options that best affect SQL server performance optimization.

Affinity mask: affinity mask allows you to control which processor SQL server uses. In most cases, you should not touch this setting to allow the operating system to control the processor relationship. However, you may want to use this option to apply a processor to another process (for example, MSSEARCH or SQL server disk Io, and SQL Server balance ). Refer to basic knowledge resources for more information about this setting.

Awe enabled: awe enables SQL Server Enterprise to run on Windows 2000 and later advanced servers, or Windows 2003 enterprise and later versions to use more than 4 GB of memory. If your server meets these conditions, activate this setting.

Concurrency cost limit: When the query requires parallel processing, the parallel cost limit is settled. The default value is five seconds. Changing this value to a lower value allows more queries to be processed in parallel, but this also causes CPU bottlenecks. This setting takes effect only on machines with multiple processors.

Fill factor: the fill factor is used to automatically fill in the cluster index when it is created. In a frequently inserted Table, set the value from the default array0 % to a lower value, and you will get the benefit.

Lightweight Buffer Pool: This setting starts the optical fiber mode. Use this option on eight or more servers with high CPU utilization. This allows the optical fiber to provide services for each thread at the same time and run on each processor by default. Some tasks can take advantage of these optical fibers.

Maximum Parallelism: this setting is determined when the server can use parallelism or cannot use parallelism, or when a certain number of processors can be used for parallel operations. Parallel processing occurs on multiple processors. For example, parallel query operations can be processed simultaneously on different processors.

Maximum server memory (MB): If you run other processing operations on SQL Server and have enough memory, you may want to set aside MB of memory for the operating system and these processes. For example, you can set MSSEARCH to 512 when running a large number of proxies locally.

Maximum working thread: the maximum working thread setting is similar to the connection pool in ado.net. With this setting, any user connection that exceeds the limit (255 users) can wait in the thread pool until the thread serving a connection is released, it is like sharing connections with connection pools in ado.net. If you have a lot of connections and a lot of memory, you can increase this value.

Network Package Size (B): This setting controls the size of packages transmitted to your client over the network. In a lossy network (such as a telephone line), you may want to set this parameter to a relatively low value, and the mo-man value is 40array6. In a well-connected network, you can improve this setting, especially for large batch processing operations involving blob.

Priority: This setting provides SQL server with a processor boost. In Task Manager, click the process tag, locate the SQL Server location, and right-click it. Select "set priority ". Note that SQL Server should run at the normal priority level. Run the following command:

Sp_configure 'priority boost ', 1

Reconfigure with override

Then restart your SQL Server. In the task manager, check the priority of SQL Server. It should be of high priority. SQL Server should have a higher priority than other user processes. Use this setting on a server dedicated to SQL Server.

Summary

This article discusses the most common SQL Server optimization settings. It is important to determine the baseline in the test environment before and after the change. You can evaluate the impact of the change on your system under a typical load.