SQL Server Administration Guide

Memory Management

SQL Server's memory usage can be controlled using the "Minimum server memory" and "Maximum server memory" options. Setting these appropriately prevents SQL Server from consuming too much or too little memory, impacting overall system performance.

To configure:

-- Using Transact-SQL
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'min server memory (MB)', 4096; -- Example: 4GB minimum
EXEC sp_configure 'max server memory (MB)', 16384; -- Example: 16GB maximum
RECONFIGURE;
GO

Processor Affinity

Processor affinity allows you to bind SQL Server processes to specific CPU cores. This can be beneficial in highly concurrent environments to reduce context switching and improve cache utilization. Use with caution and monitor performance closely.

Configuration via SQL Server Management Studio (SSMS):

1. Right-click on the server instance in Object Explorer and select "Properties".
2. Navigate to the "Advanced" page.
3. Under the "Processor affinity" section, configure the desired settings.

Note: Automatic processor affinity is usually sufficient for most workloads. Manual configuration should be based on thorough performance analysis.

SQL Server Agent Configuration

The SQL Server Agent is responsible for executing scheduled jobs, alerts, and other administrative tasks. Ensure it's running and configured with appropriate service accounts and startup parameters.

Key settings include:

• Auto-restart: Set to "Yes" to automatically restart the Agent if it stops unexpectedly.
• Run automatically when SQL Server starts: Ensure this is enabled.
• Log file viewer: Configure retention policies for job history logs.

Protocols

SQL Server supports multiple network protocols, including TCP/IP, Named Pipes, and Shared Memory. TCP/IP is the most common for network-based connections.

To manage protocols: Use the SQL Server Configuration Manager. Enable only the protocols you need and disable unused ones for enhanced security.

Named Pipes: Typically used for local connections or within a local area network. It can be slower than TCP/IP over a network.

Shared Memory: The fastest protocol, used exclusively for connections made from the same machine as the SQL Server instance.

Port Configuration (TCP/IP)

By default, SQL Server listens on TCP port 1433. For security reasons, it's often recommended to change this default port. Ensure that the firewall on your server and network allows traffic on the configured port.

To change the default port:

1. Open SQL Server Configuration Manager.
2. Expand "SQL Server Network Configuration".
3. Select "Protocols for [YourInstanceName]".
4. Right-click on "TCP/IP" and select "Properties".
5. In the TCP/IP Properties window, go to the "IP Addresses" tab.
6. Scroll down to the "TCP Dynamic Ports" section. If you have a value here, remove it.
7. In the "TCP Port" field for the relevant IP address (e.g., IPAll), enter your desired port number (e.g., 1434).
8. Click "OK" and restart the SQL Server service for changes to take effect.

Data Files (.mdf, .ndf) and Log Files (.ldf)

Separation: It is a best practice to place data files and log files on separate physical drives or LUNs. This reduces I/O contention, as transaction log writes are sequential and data file reads/writes are random.

Drive Speed: Place transaction log files on the fastest available storage (e.g., SSDs) for optimal write performance.

Growth Settings: Configure appropriate file growth increments. Avoid very small increments that lead to frequent auto-growth events, and avoid excessively large increments that can cause performance stalls.

-- Example of configuring file growth
ALTER DATABASE YourDatabase
MODIFY FILE (
    NAME = YourDatabase_Data,
    FILEGROWTH = 256MB -- Or a percentage
);
GO

ALTER DATABASE YourDatabase
MODIFY FILE (
    NAME = YourDatabase_Log,
    FILEGROWTH = 128MB -- Or a percentage, often smaller than data files
);
GO