Sybase® SQL ServerPerformance and Tuning Guide

Chapter 1

Introduction to Performance Analysis

What Is "Good Performance"?

Performance is the measure of efficiency of an application or multiple applications running in the same environment. Performance is usually measured in response time and throughput.

Response Time

Response time is the time that a single task takes to complete. You can shorten response time by:

Reducing contention and wait times, particularly disk I/O wait times

Using faster components

Reducing the amount of time the resources are needed

In some cases, SQL Server is also optimized to reduce initial response time, that is, the time it takes to return the first row to the user. This is especially useful in applications where a user may retrieve several rows with a query, but then browse through them slowly with a front-end tool.

Throughput

Throughput refers to the volume of work completed in a fixed time period. There are two ways of thinking of throughput:

For a single transaction, for example, 5 UpdateTitle transactions per minute

For the entire SQL Server, for example, 50 or 500 Server-wide transactions per minute

Throughput is commonly measured in transactions per second (tps), but it can also be measured per minute, per hour, per day, and so on.

Designing for Performance

Most of the gains in performance derive from good database design, thorough query analysis, and appropriate indexing. The largest performance gains can be realized by establishing a good database design, and by learning to work with the SQL Server query optimizer as you develop your applications.

Other considerations, such as hardware and network analysis, can locate performance bottlenecks in your installation.

What Is Tuning?

Tuning is optimizing performance. A system model of SQL Server and its environment can be used to identify performance problems at each layer.

Figure 1-1: The SQL Server system model

A major part of tuning is reducing contention for system resources. As the number of users increases, applications contend for resources such as the data and procedure caches, spinlocks on system resources, and the CPU or CPUs. The probability of lock contention on data pages also increases.

Tuning Levels

SQL Server and its environment and applications can be broken into components, or tuning layers, in order to isolate certain components of the system for analysis. In many cases, two or more layers must be tuned to work optimally together.

In some cases, removing a resource bottleneck at one layer can reveal another problem area. On a more optimistic note, resolving one problem can sometimes alleviate other problems. For example, if physical I/O rates are high for queries, and you add more memory to speed response time and increase your cache hit ratio, you may ease problems with disk contention.

The tuning layers in SQL Server are:

Applications layer - most of your performance gains come from query tuning, based on good database design. Most of this guide is devoted to an explanation of SQL Server internals and query processing techniques and tools.

Database layer - applications share resources at the database layer, including disks, the transaction log, data cache,

Server layer - at the server layer, there are many shared resources, including the data and procedure caches, locks, CPUs

Devices layer - the disk and controllers that store your data

Network layer - the network or networks that connect users to SQL Server

Hardware layer - the CPU or CPUs available

Operating system layer - ideally, SQL Server is the only major application on a machine, and must only share CPU, memory, and other resources with the operating system, and other Sybase software such as the Backup Server(TM) or SQL Server Monitor(TM) .

Application Layer

The majority of this guide describes tuning queries and the majority of your efforts in maintaining high SQL Server performance will involve tuning the queries on your server.

Issues at the application layer include the following:

Decision support vs. online transaction processing (OLTP) require different performance strategies

Transaction design can reduce concurrency, since long transactions hold locks, and reduce the access of other users to the data

Referential integrity requires joins for data modification

Indexing to support selects increases time to modify data

Auditing for security purposes can limit performance

Options at the application layer include:

Remote processing or replicated processing can move decision support off the OLTP machine

Using stored procedures to reduce compilation time and network usage

Use the minimum locking level that meets your application needs

Database Layer

Issues at the database layer include:

Developing a backup and recovery scheme

Distribution of data across devices

Auditing affects performance; audit only what you need

Scheduling maintenance activities that can slow performance and lock users out of tables

Options include:

Transaction log thresholds to automate logs dumps and avoid running out of space

Use of thresholds for space monitoring in data segments

Use of partitions to speed loading of data

Object placement to avoid disk contention

Caching for high availability of critical tables and indexes

SQL Server Layer

Issues at the SQL Server layer are:

Application types--is the server supporting OLTP or DSS (Decision Support) or a mix?

Number of users to be supported can affect tuning decisions-as the number of users increases, contention for resources can shift.

Network loads.

Replication Server® or other distributed processing can be an option when the number of users and transaction rate reach high levels.

Options include:

Tuning memory, the most critical configuration parameter and other parameters

Deciding on client vs. server processing--can some processing take place at the client side?

Configuring cache sizes and I/O sizes

Adding multiple CPUs

Scheduling batch jobs and reporting for off-hours

Reconfiguring certain parameters for shifting workload patterns

Determine whether it is possible to move DSS to another SQL Server

Devices Layer

Issues at the devices layer include:

Will the master device, the devices that hold the user database, or database logs be mirrored?

How do you distribute system databases, user databases, and database logs across the devices?

Are partitions needed for high insert performance on heap tables?

Options include:

Using more medium-sized devices and more controllers may provide better I/O throughput than a few large devices

Distributing databases, tables, and indexes to create even I/O load across devices

Network Layer

Virtually all users of SQL Server access their data via the network. Major issues with the network layer are:

The amount of network traffic

Network bottlenecks

Network speed

Options include:

Configuring packet sizes to match application needs

Configuring subnets

Isolating heavy network uses

Moving to higher-capacity network

Configuring for multiple network engines

Designing applications to limit the amount of network traffic required

Hardware Layer

Issues at the hardware layer include:

CPU throughput

Disk access: controllers as well as disks

Disk backup

Memory usage

Some options are:

Adding CPUs to match workload

Configuring the housekeeper task to improve CPU utilization

Following multiprocessor application design guidelines to reduce contention

Configuring multiple data caches

Operating System Layer

At the operating system layer, the major issues are:

File systems--are they available only to SQL Server?

Memory management--accurately estimating operating system overhead and other program memory use

CPU utilization--how many CPUs are available overall, and how many are allocated to SQL Server?

Options include:

Network interface

Choosing between files and raw partitions

Increasing the memory size

Moving client operations and batch processing to other machines

Multiple CPU utilization for SQL Server

Know the System Limits

There are limits to maximum performance. The physical limits of the CPU, disk subsystems and networks impose limits. Some of these can be overcome by purchasing more memory and faster components. Examples are adding memory, using faster disk drives, switching to higher bandwidth networks, and adding CPUs.

Given a set of components, any individual query has a minimum response time. Given a set of system limitations, the physical subsystems impose saturation points.

Know Your Tuning Goals

For many systems, a performance specification developed early in the application life cycle sets out the expected response time for specific types of queries and the expected throughput for the system as a whole.

Steps in Performance Analysis

When there are performance problems, you need to determine the sources of the problems and your goals in resolving them. The steps for analyzing performance problems are:

1. Collect performance data to get baseline measurements. For example, you might use one or more of the following tools:

Benchmark tests developed in house or industry standard third-party tests.

sp_sysmon, a system procedure that monitors SQL Server performance and provides statistical output describing the behavior of your SQL Server system. See Chapter 19, "Monitoring SQL Server Performance with sp_sysmon" of this guide for information about how to use sp_sysmon.

SQL Server Monitor, a separate Sybase product that provides graphical performance and tuning tools and object-level information on I/O and locks.

Any other appropriate tools.

2. Analyze the data to understand the system and any performance problems. Create and answer a list of questions to analyze your SQL Server environment. The list might include questions such as the following:

What are the symptoms of the problem?

What components of the system model affect the problem?

Does the problem affect all users, or only users of certain applications?

Is the problem intermittent or constant?

3. Define system requirements and performance goals:

How often is this query executed?

What response time is required?

4. Define the SQL Server environment--know the configuration and limitations at all layers.
5. Analyze application design--examine tables, indexes, and transactions.
6. Formulate a hypothesis about possible causes of the performance problem and possible solutions based on performance data.
7. Test the hypothesis by implementing the solutions from the last step:

Adjust configuration parameters

Redesign tables

Add or redistribute memory resources

8. Use the same tests used to collect baseline data in step 1 to determine the effects of tuning. Performance tuning is usually an iterative process.

If actions taken based on step 7 do not meet the performance requirements and goals set in step 3, or if adjustments made in one area cause new performance problems, repeat this analysis starting with step 2. You might need to reevaluate system requirements and performance goals.

9. If testing shows that the hypothesis was correct, implement the solution in your development environment.

Using sp_sysmon to Monitor Performance

Use the system procedure sp_sysmon while tuning to monitor the effects of adjustments you make.

Performance tuning is usually an iterative process. While specific tuning might enhance performance in one area, it can simultaneously diminish performance in another area. Check the entire sp_sysmon output and make adjustments as necessary to achieve your tuning goals.

For more information about using sp_sysmon see Chapter 19, "Monitoring SQL Server Performance with sp_sysmon."

SQL Server Monitor, a separate Sybase product, can pinpoint where problems are at the object level.