SQL Server Performance Tuning series (5)-SQL Server Configuration

I. Preface

After the new installation of SQL Server 2005/2008, We need to configure SQL Server Based on the server's hardware and software facilities to achieve better performance. However, the default configuration is used in most cases.

 

Ii. Configuration

 

1. server attribute page configuration

 

(1). Security

• Server logon permission authentication: Generally, Hybrid Authentication (SQL Server and Windows identity) is used)

(2). Memory/processors

• Automatically Set processor affinity mask for all processors: you can set the number of processors automatically allocated by SQL server. If the server load is heavy, it is recommended that SQL Server not assign tasks to all processors, we recommend that you keep two CPUs for the OS.
• Automatically set I/O affinity mask for all processors: Automatic Io balancing

(3). Database setting

• Default index fill factor: it is very important to set the page fill percentage. If set to 80: indicates that when the index is created or re-built, the data page will retain 20% of the space for future insertion or modification. This will produce a small number of page splits and improve performance.
• Compress backup option: In server backup, compress is used to reduce Io, but CPU operations are increased.

(4). Connection

• Maximum number of concurrent connection: sets the maximum number of connections.
• Use query Governor prevent long-running queries: Use the query manager to block time-consuming queries.
• Allow remote connections to this server: selected by default

 

2: SQL Server Configuration Manage (SQL Server 2008 Enterprise Edition)

(1). SQL Server services

• Start, pause, restart, or stop the service
• Set the Service Startup method (automatic, disable, manual)
• You can also set an account for startup.
• Configure startup parameters

In addition, you can set the filestream attribute in SQL Server.

(2). SQL SERVER network configuration

• Shared Memory: it is the simplest protocol available for use and has no configurable settings. Because the client of the shared memory Protocol can only connect to the SQL server instance running on the same computer, it is useless for most database activities.
• TCP/IP: TCP/IP is a common protocol widely used on the Internet. It communicates with computers with different hardware structures and operating systems in the Interconnect Network. TCP/IP includes the network traffic standard and provides advanced security functions. It is currently the most commonly used protocol in business.
• Named Pipes: a protocol developed for LAN. A part of the memory is used by a process to transmit information to another process. Therefore, the output of a process is the input of another process. The second process can be local (on the same computer as the first process) or remote (on a networked computer ).
• Via: the virtual interface adapter (VIA) protocol is used together with the via hardware. The via protocol is not recommended. This function will be deleted in later versions of Microsoft SQL Server.

Suggestion: Generally, TCP/IP works better in a slow LAN, Wan, or dial-up network. When the network speed is not a problem, named pipes is a better choice, because it is more powerful, easier to use, and has more configuration options.

(3). SQL native Client 10.0 Configuration

• Client protocols
• Aliases

The Protocol is the same as (2). You can specify more configurations.

 

3. sp_configure/reconfigure

Displays or changes the global configuration settings of the current server. Many configurations need to be set through sp_configure.

Syntax:

sp_configure [ [ @configname = ] 'option_name'     [ , [ @configvalue = ] 'value' ] ] reconfigure

 

For example:

To configure advanced options with sp_configure, you must first run sp_configure when the "show advanced options" option is set to 1, and then run reconfigure:

sp_configure 'show advanced option', '1';reconfigure

 

Distributed Application query:

sp_configure 'Ad Hoc Distributed Queries','1'reconfigure

 

 

Iv. Summary

SQL configuration involves a lot of aspects. I can only talk a few words about my limited skill. I will find more in the future and share it with me.

 

>>> SQL server performance tuning portal address