Defining Performance Expectations - Chapter 10

  1. What Can You Expect The first problem in tuning a SQL Server is to understand what level of performance you can expect. If it ain't broke, don't fix it. You need some effective measures to help you understand whether the performance you're getting makes sense, given the query and the data. When you tune performance, you should apply your time and effort to problems that demand your attention, and to those that you can fix.
  2. Focusing Tuning Efforts Where It Counts Let's start by understanding basic physical limitations. The weak link in the performance chain is often the physical disk drive. Today's fast-caching RAID arrays can deliver about 2MB of data per second to SQL Server, sometimes more. With multiple I/O channels and read-ahead threads on a multi-processor NT server, you can look for two to three times that volume of data. So a front-to-back scan (a table scan) of a 100MB table could run in less than a minute with a fast drive subsystem. For many users, however, that's simply too long to wait for an answer. Several techniques can improve that performance. Even faster, larger drives would deliver the data faster to cache; an enormous data cache would improve the chance that some portion of the table would be available to you in memory when you ran the query; the DBCC PINTABLE command could force the table to persist in data cache once it was read (although this is probably not a good idea with large tables). Another method might be to add CPUs and count on read-ahead threads to improve scan times. Depending on your disk configuration and the amount of contention on the system, this strategy may reduce scan time by a factor of 40 percent or more. Like all hardware-based, brute force techniques, these approaches have fairly limited effectiveness, and their cost may become prohibitive. The solution to this problem may be to deliver the same result while reading less data. That may involve storing derived or duplicate values, or changing the basic design of the table to reduce its size. Adding a nonclustered index may also lead to useful performance improvements. In any event, what your users perceives as a problem does allow some improvement and may be worth addressing. So, when you choose where to focus your tuning efforts, there are two key issues: 1. Where are the problems that need to be solved? 2. Which problems are the result of physical limitations? It's crucial to understand the performance capabilities of SQL Server, and to establish an understanding of how your hardware and software configuration relates to the finite limitations of the system. Based on that, you can develop reasonable expectations and capabilities as well. The following sections help you develop proper performance expectations and focus your tuning activities:
  3. Defining Performance There are three basic ways to define SQL Server performance: 1. Response time for queries. How much time elapses when running a specific query? Many organizations demand subsecond response time for simple queries. Of course, more complex queries take longer than a second. Comparing required response time to actual response time is an important measure of performance. 2. Throughput. This is typically measured in transactions per second, or TPS. A variety of industry-standard benchmarks can help you to compare the performance of database servers running on various platforms, but they won't help you define or predict the actual performance of the system, because they don't reflect your own transactions. Carry out your performance benchmarking with your own queries and data to get meaningful throughput measures. You need to make sure that your database and server are up to the challenge of managing the number of queries in the amount of time you need. 3. Concurrency. This standard can reasonably be considered a subset of throughput. Here's the basic question: Can your system handle 5,000 users? Answering this question is usually a substantial task that requires you to profile predicted query and throughput traffic, and then to answer the response time and throughput questions under load. 4. Combination of throughput and concurrency. Can this system handle online transaction processing (OLTP), decision support systems (DSS, also called data warehousing or EIS-Executive Information Systems), and batch processing (management and offline report systems) at one time? This is the hardest thing to tune for, particularly on single-processor SQL Servers.
  4. Tradeoffs Tuning for a single definition of performance is reasonably simple. For example, to tune for fast response time to queries is usually simple if throughput and concurrency don't matter. Approaches that solve single performance include 1. Normalization (eliminating duplicate data) - can speed throughput 2. Denormalization (storing duplicate data) - can speed retrieval time 3. Creating or dropping indexes 4. Partitioning tables across databases 5. Adding hardware or reconfiguring software Where solving a single problem is fairly straightforward, finding a way to solve multiple performance needs is usually a delicate balance act. For example, you may choose to speed throughput by removing indexes used to improve query performance. Modifications to tables are much faster without the additional indexes, so transactions require less work and the system can handle more transactions per second. The tradeoff is that queries previously supported by an index now might need to perform a table scan. A query that used to take three minutes may now take several hours!
  5. Common Tradeoffs You need to consider some common tradeoffs when you're looking at performance issues. None of these tradeoffs is simple or has a standard response. As you evaluate your performance options, it's vital that you be able to state clearly what you're getting and what you're giving up.
  6. Update Performance versus Read Performance Most database administrators (DBAs) develop database designs using a two-step approach that starts with a logical design and then moves to a physical design. A logical database design is a representation of data intended to remove all duplication and to express clearly the relationship between data elements. The physical design is a plan for how to store the data on a particular system. One of the first hurdles to overcome in physical design is understanding that it has a separate purpose from logical design. Logical design is for understanding and describing data; physical design is for performance. The first tradeoff is update performance versus select performance. A normalized database allows updates to run fast, but it also requires more joins to resolve multi-entity queries, and joins are costly. If you denormalize, you reduce joins; thus many queries run faster.
  7. Storage Efficiency versus Cost This brings up the next tradeoff: storage efficiency versus cost. Denormalizing frequently demands more storage because it requires the storage of duplicate data. and that isn't free; however, it's often cheap compared to the cost of unresolved performance issues.
  8. Fast Execution versus Ad Hoc Access Balancing OLTP and DSS is a specialized problem. OLTP applications typically support a fixed set of data operations, most of them update operations, allowing you to predict the appropriate data design and index selections. Small, lean tables and few indexes provide the best performance in those circumstances. DSS applications are based on ad hoc queries where users ask questions you can never anticipate. Wide tables and lots of indexes help a DSS applications, where retrieval time matters much more than the time to maintain the data. Before you can address your performance issues, you need to define your requirements and boundaries. You need to provide a physical design to enable the response times that you want to achieve.
  9. Expectations Let's take a look at some specific queries and try to identify the expectations. These queries are based on a table and index defined as follows: create table orders (... item_num int, warehouse int, ...) Create index ord_index on orders (item_num, warehouse) Here are some potential queries you might run against the table, with their response times: /* QUERY A: response time subsecond */ select sum(qty) from orders where item_num = 1234 and warehouse = 432 /* QUERY B: response time 600 seconds */ select sum(qty) from orders where warehouse = 432 /* QUERY C: response time 50 seconds */ select sum(qty) from orders where item_num in (1234,2345) and warehouse = 432 Now let's pose two important, distinct questions: 1. Which queries have acceptable response times? (Of course, your answer to this question depends on your users' demands.) 2. Which have expected response times? Query A: Is the response time acceptable? In subsecond, is yes. Is the response time expected? If you have a reasonable amount of data for the test, look for real problems elsewhere. Query B has response time that is unacceptable for most real-time operations. To understand this we need to understand the physical design, and how the server uses the physical design. Because the table is indexed on item_num, and item_num isn't in the where clause, the server can't use the index. Therefore, the only way to resolve the query is with a table scan, which is the process of reading every page in the table. Next comes understanding the data: How many pages of data do we have to read to resolve the query? It also opens up a hardware question: How many pages of data can SQL Server read in a second? If the data isn't in cache, query performance depends on physical drive performance. Depending on the server hardware, SQL Server can read between 200 and 1,000 pages (400 KB to 2MB) per second (typically) from SCSI disk drive. Back to Query B. How many pages of data must we read? (SQL Server reads one page at a time.) If the amount of data that needs to be read takes several hundred seconds (for example, the table is 800 MB), and you're willing to allow a few seconds for overhead, 600 seconds might be an expected result-even though it isn't an acceptable one. During your physical design phase, you need to identify the fact that you have a potential problem query, with expected results that are unacceptable, and you need to figure out a way to improve performance (this can be an additional index or one of many other possible decisions).
  10. Bottlenecks A bottleneck is a resource that limits system throughput. A typical bottleneck is a physical disk drive. You may need to get data from the disk drive a bit faster and be limited by the speed of data retrieval from the drive. But is this the real problem? Is the problem the disk drive, the controller, or the operating system? Unfortunately, eliminating a bottleneck has the net effect of shifting the bottleneck to some other limiting resource. With any luck, though, the new bottleneck will be wider. It's essential to understand where bottlenecks can occur, where they tend to occur, and under what circumstances. Potential bottlenecks are your performance variables-those things that you adjust, tune, and balance to get the best possible results for your application.
  11. Performance Variables A number of components can be bottlenecks. Some of these are based on configuration options that can be set; others are built in and unchangeable.
  12. Physical Architecture Physical (hardware) architecture specifically relates to these components: 1. CPU configuration CPU performance is an important element in performance, but seldom the bottleneck. Can you improve performance by reducing dependence on CPU performance? The best approach is to make your code more efficient by simplifying SQL and reducing cursors. Can you make better use of multiple CPUs? By tuning configuration options to improve the efficiency of read-ahead caching (RA configuration options), you may be able to improve multi-processing performance for a single query. 2. Disk I/O performance You can't change the I/O rate for a physical device, but you can reconfigure the physical design by introducing a RAID device or splitting up data across multiple disks. You can also reduce the amount of requested data. (For example, limit the number of rows to be retrieved from the database. Will a user really need all 4,000,000 rows? Usually not.) 3. Network The network is an unreliable component. In many systems, network performance is very fast, but with increasing dependence on intranets, more and more users are running client/server applications across a WAN. For most applications, you should regard the network as a bottleneck: make a point of reducing network traffic whenever and wherever possible. You can do this by reducing the number of packets transmitted or by increasing the packet size. You can also use stored procedures. 4. Concurrency Concurrency issues are not tunable, but can sometimes be contained. For example, if logging overhead is causing problems, you can try committing transactions less frequently or putting the log on a higher-speed device. Additionally, if sequential reads are slower than you would expect, you may be able to redistribute the data on your disks. Overall server architecture also affects concurrency. For example, additional memory often helps the server handle greater numbers of users more efficiently.
  13. Application Application components relevant to database performance include three critical elements: 1. Query The most important issue from an application-tuning standpoint is to make sure that your user writes queries properly. Substantial user training is critical, particularly where ad hoc queries are involved, because horrid SQL is probably the number one cause of bad performance. Bad queries can have a variety of flavors: unnecessary joins, insufficient joins, lack of search arguments, or an inability to take advantage of server features (for example, the update in place). 2. Logical design Logical design issues may involve changing table normalization to reduce joins, or data partitioning to take infrequently referenced data out of the scanned table. 3. Physical design Physical design is often the first place to start working on performance problems. Correct index selection can often fix - that is, improve the performance of- otherwise problematic queries. Adding indexes tends to help queries but hurts update performance. You can also store summary or redundant data. Cursors are a favorite way programmers can foul up performance, particularly concurrency. A poorly written cursor can lock huge amounts of data.
  14. Server Server performance relates to these elements: 1. Configuration 2. Optimizer 3. Lock Management 4. Concurrency 5. Maintenance activity 6. Dump/load 7. Index creation 8. Batch activity 9. Concurrency management The database server is the most tunable component of your environment. For example, you can configure and reconfigure memory, cache, locks, disk-resource dissemination, connections, and dozens of other things. There are also some components you can't easily tune. One thing you can't change is the optimizer. The optimizer chooses its own path, join order, and/or other search tactic. Note: You can, for a particular query or session, force a join order or index selection on the optimizer. See Chapter 13. Most lock management is handled automatically by the server; the maximum number of overall locks is configured by the administrator. The SQL Server manages the use of the locks. You can improve performance by keeping transactions short and by avoiding manual locking behavior. Also avoid situations where deadlocking becomes likely. Server overhead can sometimes be handled with hardware, such as using a solid-state device for the log to increase transaction throughput where the log has been positively identified as a bottleneck.
  15. Concurrency You probably will want to identify a batch window for performing (among other things) maintenance activity such as dbcc and database dumps, index creations and re-creations, or other batch activity (reporting, mailing labels, long- running ad hoc queries). All of these activities tend to hog an entire processor. It's also the type of activity that can lock tables or databases, which reduces OLTP concurrency.
  16. Tuning Your Approach Before beginning to tune, gather as much information as possible about the circumstances surrounding the perceived performance problem. Remember that you can't tune for everything. You need to identify and prioritize problems before addressing them. For example, you have a problem query that runs for 40 hours. You can fix the problem by denormalizing your database and adding four indexes to the tables. The cost of this solution is six additional disk drives and three hours added to your batch(dump) window. Ask yourself whether it's worth the cost. Are you fixing this query for the CEO of the company, who needs this report updated every two hours, or is this query being run every six months by an associate accountant? Sometimes the decisions are easy. If you're tuning an existing system, choose options that are transparent to users (for example, indexes and segments) over those that require changes to programming (like changing table structure). Estimate your requirements prior to the final rollout. Find or build a tool that simulates user activity, and act on the information you acquire. It's rare for users or application designers to estimate data requirements adequately, but you should make sure that you can handle at a minimum the stated system requirements. When you have specific problems, follow these steps: 1. Identify baseline time for your CPU and controllers so that you can state your expected results. 2. Decide whether the results are expected 3. Examine the problem query. Is it too complex? Does the query solve the user's actual need? 4. Are the indexes appropriately selected in the physical design? Is the optimizer using the indexes you think it should? 5. Is the optimizer selecting the correct approach? 6. When in doubt, break down the query. Do individual components take too long? 7. Finally, prioritize the problem. Does the situation, user, or application warrant physical-design changes or other work on your part?

Last update February 16, 1998.