Lesson 21. Configuring, Maintaining, and Tuning SQL Server
The most attractive application can be extremely frustrating to use if its performance is less than acceptable. As a developer, you must take precautions to try to assure that the SQL Server is as lean and efficient as possible. This lesson explains:
How to select and tune your hardware
How to configure and tune SQL Server
How to maintain your databases
How to maintain your tables and other objects
Selecting and Tuning Hardware
Your choice of hardware can greatly affect the performance of your server. Areas of particular concern are the amount of memory, processor speed, and hard disk configuration. The network architecture is also an important factor. The sections that follow cover the details of each of these items.
Memory—The More RAM, the Better!
SQL Server uses memory (RAM) to hold all data pages, index pages, and log records. It also uses memory to hold compiled queries and stored procedures. Needless to say, that memory is vital to the performance of the server.
Clients are always asking me how much memory they should purchase for their server. My answer is, the more, the better. In fact, RAM is the best investment you can make for your SQL Server. That aside, here are some guidelines. A bare minimum amount of RAM for SQL Server Express is 512MB. Although all other editions require 1GB of RAM, at least 4GB is recommended. SQL Server 2014 Express can take advantage of 1GB. SQL Server 2014 Standard can utilize 128GB. Finally, SQL Server 2014 Enterprise can utilize the operating system maximum!
Processor
Although it’s not as important as RAM, more processing power never hurts. SQL Server 2014 Express can take advantage of one processor. SQL Server 2014 standard can take advantage of up to four processors, and SQL Server 2014 Enterprise can take advantage of the operating system maximum. Despite the advantages of multiple processors, if limited funds force a choice between more RAM and multiple processors, more RAM is the most cost-effective choice.
Storage
Because SQL Server stores all of your data on disk, the type, configuration, and throughput of the storage media you use are important factors. Today, there are three common interfaces for disk storage: Serial AT Attachment (SATA), Serial Attached Small Computer System Interface (SAS), and Fibre Channel (FC). SATA disks are prevalent on laptop and desktop computers because they are less expensive than SAS drives. Rotational speed on the platters within each disk also have a significant effect on the speed of data access. SATA drives commonly come in speeds of either 5,400RPM or 7,200RPM (Revolutions per Minute). SAS drives commonly come in speeds of either 10,000RPM or 15,000RPM, which is part of the reason why SAS drives can generally access data faster than SATA drives. Higher speeds are better.
SATA disks work well on individual computers because single users rarely need the advanced throughput of multiple Input/Output (I/O) paths for accessing data on the disk. Servers for small offices might also use SATA disks when there are not many users and those users do not constantly access very large files or continuously access numerous small files on the server. Generally speaking, you should only store SQL Server data on SATA disks for development, testing, or for a very small number of users.
SAS disks improve on the performance of the old SCSI standard. Simultaneous multiple I/O gives SAS disks a large performance advantage over SATA disks in addition to their higher rotational speeds. The FC standard is used primarily in enterprise environments for high capacity and highly available Storage Area Network (SANs). SANs employ multiple disks that are configured as storage volumes (drive letters). The multiple disks that comprise a SAN provide redundancy and resiliency from disk failures as part of a Redundant Array of Independent Disks (RAID) configuration.
A data protection RAID configuration can and should be used whenever downtime needs to be avoided due to disk failure. For example, RAID Level 1 consists of two disks where one disk mirrors its data onto the second disk. If one of the disks fail, the system can still operate normally using the other disk. You can also employ disk duplexing, which is the same concept as disk mirroring, but each disk has its own disk controller so that the disk controller is not a single point of failure. RAID Level 5 uses between 3 and 32 disks. The more disks, the higher capacity of the storage array. In a RAID Level 5 configuration, one disk can fail, and the system continues to operate normally. If more than one disk fails at the same time, data is lost. Ideally, SQL Server system and transaction log files should both be mirrored or duplexed. This means that they should be mirrored and on separate disk controllers wherever possible. At a minimum, all SQL Server database and transaction log files should be stored in a data protection RAID configuration, such as RAID Levels 1, 5, 6, or 10.
A new breed of data storage has emerged recently, which greatly speeds up data access over conventional “hard disks.” Solid State Disks (SSDs) are all electronic, similar to Random Access Memory (RAM) used in all computers. The main difference between RAM and SSDs is that SSDs do not lose data when powered off. Unlike conventional hard disks, SSDs have no mechanical parts, so data retrieval and recording is significantly faster, and SSDs have no moving parts to wear out. Eventually, SSDs will be the standard for data storage on all computers because the pricing on SSDs has come down dramatically from a few years ago. However, SSDs still cost significantly more than conventional drives. In the coming years, when SSDs will cost about the same as conventional disks, computer manufacturers will install SSDs as standard equipment on all machines. SSDs come in the same variety of interfaces as conventional drives, including SATA, SAS, and FC. SSDs can also be used in RAID configurations just like conventional disks. Besides much faster performance, SSDs are manufactured in the smaller 2.5-inch form factor, the same form factor as laptop drives. SSDs generally do not come in the 3.5-inch desktop drive size; you can purchase adapters so that a 2.5-inch SSD will fit into a 3.5-inch hard disk slot on a desktop or server computer.
Network
Both the network card in the SQL Server machine and the network bandwidth are important factors in the performance of your application. The server should contain a 64-bit network card, which is usually standard in today’s computers that run Microsoft’s 64-bit operating systems, such as Windows 7/8/8.1/10 and Windows Server 2008/2012/2016. You should use Gigabit (1GB) Ethernet network adapters versus legacy 10MB/100Mb Ethernet network adapters whenever possible. Use 10GB network adapters if your local area network (LAN) supports it! The bottom line is that the best hardware and the best designed database do you no good on a slow, overtaxed network.
Configuring and Tuning SQL Server
If you do not configure the server properly, all of the hardware in the world and the best designed database mean nothing. Processor options, memory options, I/O options, and query and index options all allow you to designate the most appropriate server configuration for you. Although most of the time you will leave these options at their default values, it is useful to know what configuration options are available to you.
Memory Options
You can designate the minimum and maximum memory used by SQL Server. You can also specify the minimum memory allocated to each user for each query run. You should generally not modify any of the memory options. This is because SQL Server does an excellent job of allocating and deallocating memory on its own. The only time you would want to modify these options is if you are running SQL Server on the same machine as another highly memory-intensive application, such as Microsoft Exchange Server. I recommend running SQL Server on a dedicated machine where this is not a problem. If you must modify the SQL Server memory options, here’s the process:
1. Right-click the server you want to affect and select Properties. The Server Properties dialog box opens (see Figure 21.1).
FIGURE 21.1 The Server Properties dialog box contains pages that enable you to modify various properties of your SQL Server.
2. Click to select the Memory page (see Figure 21.2).
FIGURE 21.2 The Memory page of the SQL Server Properties dialog box enables you to set memory options.
3. Modify the options as desired.
Processor Options
You can configure the way in which SQL Server uses your server’s processors. The configuration options available include the following:
You can configure the relative priority of the SQL Server process.
You can determine which of the server’s CPUs SQL Server will use.
You can designate the total number of operating system threads that SQL Server can use on your computer.
You can set the maximum number of CPUs used by parallel query operations.
To modify the processor-related properties:
1. Right-click the server you want to affect and select Properties.
2. The Server Properties dialog box appears.
3. Click to select the Processors page. The dialog box appears as in Figure 21.3.
FIGURE 21.3 The Processors page of the Server Properties dialog box enables you to modify processor-related properties of your SQL Server.
4. Modify the options as desired.
Security Options
You will usually work with Security options when establishing a SQL Server. Options available include the type of authentication you want on the server (Windows Authentication or SQL Server and Windows Authentication) and the type of login auditing you want to perform when user logins are unsuccessful.
Use the following steps to modify Security options for the server:
1. Right-click the server you want to affect and select Properties.
2. The Server Properties dialog box appears.
3. Click to select the Security page. The dialog box appears as in Figure 21.4.
FIGURE 21.4 The Security page of the Server Properties dialog box enables you to modify security-related properties of your SQL Server.
4. Modify the options as desired.
Connections Options
The Connections options enable you to determine the maximum number of concurrent connections that the server will allow, as well as whether the server will allow remote connections. Other options are available as well.
Use the following steps to modify Connections options for the server:
1. Right-click the server you want to affect and select Properties.
2. The Server Properties dialog box appears.
3. Click to select the Connections page. The dialog box opens as in Figure 21.5.
FIGURE 21.5 The Connections page of the Server Properties dialog box enables you to modify connection-related properties of your SQL Server.
4. Modify the options as desired.
Database Settings Options
The Database Settings page of the Server Properties dialog box enables you to alter settings that affect the databases on that server. For example, you can set the default locations for database and log files, and you can set the default index file factor.
To modify Database Settings options for the server:
1. Right-click the server you want to affect and select Properties.
2. The Server Properties dialog box opens.
3. Click to select the Database Settings page. The dialog box appears as shown in Figure 21.6.
FIGURE 21.6 The Database Settings page of the Server Properties dialog box enables you to modify database-related properties of your SQL Server.
4. Modify the options as desired.
Advanced Options
The Advanced page of the Server Properties dialog box enables you to configure various server options. These include where triggers can fire other triggers, the two-digit year cutoff, and much more.
Use the following steps to modify Advanced options for the server:
1. Right-click the server you want to affect and select Properties.
2. The Server Properties dialog box appears.
3. Click to select the Advanced page. You see the dialog box in Figure 21.7.
FIGURE 21.7 The Advanced page of the Server Properties dialog box enables you to modify various properties of your SQL Server.
4. Modify the options as desired.
Permissions Options
The Permissions page of the Server Properties dialog box enables you to modify permissions that relate to the server (not to a particular database). Here you can add and remove logins and roles to and from the server. You can also Grant, With Grant, and Deny server-related rights to logins and roles. Server-related rights include whether the user or role can alter any database, whether the user or role can alter any login, and much more.
Use the following steps to modify Permissions options for the server:
1. Right-click the server you want to affect and select Properties.
2. The Server Properties dialog box appears.
3. Click to select the Permissions page. You see the dialog box in Figure 21.8.
FIGURE 21.8 The Permissions page of the Server Properties dialog box enables you to modify permission-related properties of your SQL Server.
4. Modify the options as desired.
Summary
The best-designed system will fail to meet users’ needs if it performs poorly. The first line of attack when attempting to optimize performance is to ensure that the hardware on which SQL Server runs is adequate for the job at hand. After all the proper hardware is in place, you must determine that you have properly configured the SQL Server software. After you configure your hardware and the SQL Server software, you must make sure you take all of the steps to properly maintain the server. In case things go awry, you must implement and test a backup and restore procedure.
Q&A
Q. Why is memory so important to a SQL Server?
A. SQL Server uses memory to hold all data pages, index pages, and log records. It also uses memory to hold compiled queries and stored procedures.
Q. Explain some of the security options available.
A. Using the Security page of the Server Properties dialog box, you can change the type of authentication you want to allow (Windows only versus Windows and SQL Server). You can also determine what type of logging SQL Server will perform when logins are unsuccessful.
Workshop
Quiz
1. Name four things you can do to your hardware to improve performance of your SQL Server.
2. Using the Memory options page of the Server Properties dialog box, you can configure the relative priority of the SQL Server process (true/false).
3. Using the Processor options page of the Server Properties dialog box, you can determine which of the server’s CPUs SQL Server will use (true/false).
4. The Permissions page of the Server Properties dialog box enables you to modify database permissions (true/false).
Quiz Answers
1. More memory, more processors, RAID array, faster network.
2. False. Using the Processor options page of the Server Properties dialog box, you can configure the relative priority of the SQL Server process.
3. True.
4. False. The Permissions page of the Server Properties dialog box enables you to modify server-related permissions.
Activities
View the various pages of the Server Properties dialog box. When you are finished, back up the AdventureWorks database and then restore the database.