Performance Tuning in SQL Server: Factors That Affect Performance

Factors affecting performance fall into three general categories:

  • Database applications
  • Database system
  • System resources

These factors in turn can be affected by several other factors, as discussed in the following sections.

1. Database Applications and Performance

The following factors can affect the performance of database applications:

  • Application-code efficiency
  • Physical design

1.1. Application-Code Efficiency

Applications introduce their own load on the system software and on the Database Engine.

For this reason, they can contribute to performance problems if you make poor use of system resources. Most performance problems in application programs are caused by the improper choice of Transact-SQL statements and their sequence in an application program.

The following list gives some of the ways you can improve overall performance by modifying code in an application:

  • Use clustered indices
  • Do not use the NOT IN predicate

Clustered indices generally improve performance. Performance of a range query is the best if you use a clustered index for the column in the filter. When you retrieve only a few rows, there is no significant difference between the use of a nonclustered index and a clustered index.

The NOT IN predicate is not optimizable; in other words, the query optimizer cannot use it as a search argument (the part of a query that restricts the intermediate result set of the query). Therefore, the expression with the NOT IN predicate always results in a table scan.

NOTE More hints on how code modification can improve overall performance are given in Chapter 19.

1.2. Physical Design

During physical database design, you choose the specific storage structures and access paths for the database files. In this design step, it is sometimes recommended that you denormalize some of the tables in the database to achieve good performance for various database applications. Denormalizing tables means coupling together two or more normalized tables, resulting in some redundant data.

To demonstrate the process of denormalization, consider this example: Table 20-1 shows two tables from the sample database, department and employee, that are normalized. (For more information on data normalization, see Chapter 1.) Data in those two tables can be specified using just one table, dept_emp (see Table 20-2), which shows the denormalized form of data stored in the tables department and employee. In contrast to the tables department and employee, which do not contain any data redundancies, the dept_emp table contains a lot of redundancies, because two columns of this table (dept_name and location) are dependent on the dept_no column.

Data denormalization has two benefits and two disadvantages. First the benefits: If you have a column that is dependent on another column of the table (such as the dept_name column in the dept_emp table, which is dependent on the dept_no column) for data often required by queries, you can avoid the use of the join operation, which would affect the performance of applications. Second, denormalized data requires fewer tables than are required to store the same amount of normalized data.

On the other hand, a denormalized table requires additional amounts of disk space, and data modification is difficult because of data redundancy.

Another option in the physical database design that contributes to good performance is the creation of indices. Chapter 10 gives several guidelines for the creation of indices, and examples are given later in this chapter.

2. The Database Engine and Performance

The Database Engine can substantially affect the performance of an entire system. The two most important components of the Database Engine that affect performance are

  • Query optimizer
  • Locks

2.1. Query Optimizer

The optimizer formulates several execution plans for fetching the data rows that are required to process a query and then decides which plan should be used. The decision concerning the selection of the most appropriate execution plan includes which indices should be used, how to access tables, and the order of joining tables. All of these decisions can significantly affect the performance of database applications. The optimizer is discussed in detail in Chapter 19.

2.2. Locks

The database system uses locks as the mechanism for protecting one user’s work from another’s. Therefore, locks are used to control the access of data by all users at the same time and to prevent possible errors that can arise from the concurrent access of the same data.

Locking affects the performance of the system through its granularity—that is, the size of the object that is being locked and the isolation level. Row-level locking provides the best system performance, because it leaves all but one row on the page unlocked and hence allows more concurrency than page- or table-level locking.

Isolation levels affect the duration of the lock for SELECT statements. By using the lower isolation levels, such as READ UNCOMMITTED and READ COMMITTED, the data availability, and hence the concurrency, of the data can be improved. (Locking and isolation levels are explained in detail in Chapter 13.)

3. System Resources and Performance

The Database Engine runs on an operating system, which itself uses underlying system resources. These resources have a significant impact on the performance of both the operating system and the database system. Performance of any database system depends on four main system resources:

  • Central processing unit (CPU)
  • Memory
  • Disk I/O
  • Network

The CPU, together with memory, is the key component for marking the speed of a computer. It is also the key to the performance of a system, because it manages other resources of the system and executes all applications. It executes user processes and interacts with other resources of your system. Performance problems in relation to the CPU can occur when the operating system and user programs are making too many requests on it. Generally, the more CPU power available for your computer, the better the overall system is likely to perform.

The Database Engine dynamically acquires and frees memory as needed. Performance problems concerning memory can occur only if there is not enough of it to do the required work. When this occurs, many memory pages are written to a pagefile. (The notion of a pagefile is explained in detail later in this chapter.) If the process of writing to a pagefile happens very often, the performance of the system can degrade. Therefore, similar to the CPU rule, the more memory available for your computer, the better the system is likely to perform.

There are two issues concerning disk I/O: disk speed and disk transfer rate. The disk speed determines how fast read and write operations to disk are executed. The disk transfer rate specifies how much data can be written to disk during a time unit (usually measured in seconds). Obviously, the faster the disk, the larger the amount of data being processed. Also, more disks are generally better than a single disk when many users are using the database system concurrently. (In this case, access to data is usually spread across many disks, thus improving the overall performance of the system.)

For a client/server configuration, a database system sometimes performs poorly if there are many client connections. In that case, the amount of data that needs to be transferred across the network possibly exceeds the network capacity. To avoid such a performance bottleneck, the following general recommendations should be taken into account:

  • If a database server sends any rows to an application, only the rows needed by the application should be sent.
  • If a long-lasting user application executes strictly on the client side, move it to the server side (by executing it as a stored procedure, for example).

All four of these system resources are dependent on each other. This means that performance problems in one resource can cause performance problems in the other resources. Similarly, an improvement concerning one resource can significantly increase performance of some other (or even all) resources. For example:

  • If you increase the number of CPUs, each CPU can share the load evenly and therefore can remedy the disk I/O bottleneck. On the other hand, the inefficient use of the CPU is often the result of a preexisting heavy load on disk I/O and/or memory.
  • If more memory is available, there is a better chance of finding in memory a page needed by the application (rather than reading the page from disk), which results in a performance gain. By contrast, reading from the disk drive instead of drawing from the immensely faster memory slows the system down considerably, especially if there are many concurrent processes.

The following sections describe in detail disk I/O and memory.

3.1. Disk I/O

One purpose of a database is to store, retrieve, and modify data. Therefore, the Database Engine, like any other database system, must perform a lot of disk activity. In contrast to other system resources, a disk subsystem has two moving parts: the disk itself and the disk head. The rotation of the disk and the movement of the disk head need a great deal of time; therefore, disk reads and writes are two of the highest-cost operations that a database system performs. (For instance, access to a disk is significantly slower than memory access.)

The Database Engine stores the data in 8KB pages. The buffer cache of RAM is also divided into 8KB pages. The system reads data in units of pages. Reads occur not only for data retrieval, but also for any modification operations such as UPDATE and DELETE because the database system must read the data before it can be modified.

If the needed page is in the buffer cache, it will be read from memory. This I/O operation is called logical I/O or logical read. If it is not in memory, the page is read from disk and put in the buffer cache. This I/O operation is called physical I/O or physical read. The buffer cache is shared because the Database Engine uses the architecture with only one memory address space. Therefore, many users can access the same page. A logical write occurs when data is modified in the buffer cache. Similarly, a physical write occurs when the page is written from the buffer cache to disk. Therefore, more logical write operations can be made on one page before it is written to disk.

The Database Engine has a few components that have great impact on performance because they significantly consume the I/O resources:

  • Read ahead
  • Checkpoint

Read ahead is described in the following section, while the checkpoint is explained in Chapter 16.

Read Ahead The optimal behavior of a database system would be to read data into memory and never have to wait for a disk read request. The best way to perform this task is to know the next several pages that the user will need and to read them from the disk into the buffer pool before they are requested by the user process. This mechanism is called read ahead, and it allows the system to optimize performance by processing large amounts of data effectively.

The component of the Database Engine called Read Ahead Manager manages the read-ahead processes completely internally, so a user has no way to influence this process. Instead of using the usual 8KB pages, the Database Engine uses 64KB blocks of data as the unit for read-ahead reads. That way, the throughput for I/O requests is significantly increased. The read-ahead mechanism is used by the Database Engine to perform large table scans and index range scans. Table scans are performed using the information that is stored in Index Allocation Map (IAM) pages to build a serial list of the disk addresses that must be read. (IAM pages are allocation pages containing information about the extents that a table or index uses.) This allows the database system to optimize its I/O as large, sequential reads in disk order. Read Ahead Manager reads up to 2MB of data at a time. Each extent is read with a single operation.

NOTE The Database Engine provides multiple serial read-ahead operations at once for each file involved in the table scan. This feature can take advantage of striped disk sets.

For index ranges, the Database Engine uses the information in the intermediate level of index pages immediately above the leaf level to determine which pages to read. The system scans all these pages and builds a list of the leaf pages that must be read. During this operation, the contiguous pages are recognized and read in one operation. When there are many pages to be retrieved, the Database Engine schedules a block of reads at a time.

The read-ahead mechanism can also have negative impacts on performance if too many pages for a process are read and the buffer cache is unnecessarily filled up. The only thing you can do in this case is create the indices you will actually need.

If you want to know whether the Database Engine is using read ahead to read database pages for a query, you can use the following methods:

  • Turn on SET STATISTICS IO in the session where the particular query is executed (for the description of SET STATISTICS IO, see Chapter 19).
  • Use an extended event to track the event file_read completed (Example 20.14, later in the chapter, shows how you can programmatically create such an extended event).

3.2. Memory

Memory is a crucial resource component, not only for the running applications but also for the operating system. When an application is executed, it is loaded into memory and a certain amount of memory is allocated to the application. (In Microsoft terminology, the total amount of memory available for an application is called its address space.)

Operating systems generally support virtual memory. This means that the total amount of memory available to applications is the amount of physical memory (or RAM) in the computer plus the size of the specific file on the disk drive called pagefile. (The name of the pagefile on Windows is pagefile.sys.) Once data is moved out of its location in RAM, it resides in the pagefile. If the system is asked to retrieve data that is not in the proper RAM location, it will load the data from the location where it is stored and produce a so-called page fault.

NOTE The pagefile should be placed on a different drive from the drive on which files used by the Database Engine are placed, because the paging process can have a negative impact on disk I/O activities.

For an entire application, only a portion of it resides in RAM. (Recently referenced pages can usually be found in RAM.) When the information the application needs is not in RAM, the operating system must page (that is, read the page from the pagefile into RAM). This process is called demand paging. The more the system has to page, the worse the performance is.

NOTE When a page in RAM is required, the oldest page of the address space for an application is moved to the pagefile to make room for the new page. The replacement of pages is always limited to the address space of the current application. Therefore, there is no chance that pages in the address space of other running applications will be replaced.

As you already know, a page fault occurs if the application makes a request for information and the data page that contains that information is not in the proper RAM location of the computer. The information may either have been paged out to the pagefile or be located somewhere else in RAM. Therefore, there are two types of page fault:

  • Hard page fault The page has been paged out (to the pagefile) and has to be brought into RAM from the disk drive.
  • Soft page fault The page is found in another location in RAM.

Soft page faults consume only RAM resources. Therefore, they are significantly better for performance than hard page faults, which cause disk reads and writes to occur.

NOTE Page faults are normal in an operating system environment because the operating system requires pages from the running applications to satisfy the need for memory of the starting applications. However, excessive paging (especially with hard page faults) is a serious performance problem because it can cause disk bottlenecks and start to consume the additional power of the processor.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *