|
 |
        
|
|
What the IT Decision-Maker Needs to Know About SQL Server Performance Tuning
by Don Henderson
This article is written for someone with a working knowledge of information technology, but with little specific SQL Server expertise. The typical reader would be an IT manager or executive who needs background information to properly evaluate all the performance tuning suggestions being offered to him/her before spending significant amounts of money on performance tuning.
Executive Summary: The View From 10,000 Feet The most common SQL Server performance problems can often be fixed quickly and inexpensively. However, companies sometimes pursue complex, expensive and ultimately ineffective solutions because of an incomplete understanding of their problem. Before undertaking performance optimization, it is essential to monitor, measure, and most importantly, correctly interpret the appropriate SQL Server performance metrics to identify the source of the problem. This article is far from a complete treatment of SQL Server performance optimization. It focuses on the 20% of bottlenecks that cause 80% of the problems, with an emphasis on the simplest, least expensive solutions. After reading it you will not be a SQL Server performance expert, but you will be in a better position to make decisions regarding SQL Server performance optimization.
Performance Tuning SQL Server: The View from 5000 Feet This article is is not intended to be a step-by-step guide for implementing any of the procedures discussed. Use this advice at your own risk, and don’t make or allow any changes to a production server without testing thoroughly first. If you only take one thing away from this reading, let it be this: Take care of the basics first. Start with the simple, inexpensive fixes. Before undertaking a $50,000 hardware upgrade, spend a few hours optimizing the indexing in your database, then reassess the performance issues. In our experience, the most common sources of poor SQL Server performance are inadequate indexing, poorly-written SQL code, and insufficient memory. These are also areas where the most dramatic improvements can often be made with the least investment. Before you spend a lot of money on new hardware or complex fixes, make sure your applications are using the resources you have efficiently. Even when monitoring shows that one or more hardware resources are overloaded, it does not necessarily mean that they are inadequate. Inefficient application code or missing indexes can put unnecessarily heavy loads on your hardware. New hardware won't always help if your application code is generating huge numbers of blocking locks, or is scanning a 200 million row table from beginning to end every time it needs a part number. In all that follows we will be talking about SQL Server 2000, differentiating between Standard and Enterprise Editions where appropriate. Most of the discussion applies also to version 7, and will continue to apply to SQL Server 2005.
Monitoring, Measuring and Testing Before you can fix performance problems, you need to know what’s causing them. Fortunately, SQL Server has some very good diagnostic tools built in, and others are available for free download from Microsoft’s web site. For the most part these tools are fairly simple to use. The hard part is knowing how to interpret the results they generate. This article will give you some rough rules-of-thumb, but if you don't have anyone on staff experienced in this area, you may need outside expertise for an accurate and detailed analysis.
SQL Server Should Be on it’s Own Server Before we get into the details of performance tuning, it is necessary to emphasize that your production SQL Server should not be sharing resources with any other major application. It shouldn't be on a domain controller and it shouldn't be on a web server. It should be the only application running on the server and it should usually be configured with it’s default memory options to dynamically manage the memory on that server. If this is not possible, then your performance tuning job becomes more difficult.
Indexes
The simple act of placing an index on the right column in a table can often
reduce execution time for a critical procedure from minutes to seconds, or from
hours to minutes. Really.
Why Index?
When SQL Server doesn't have an index to follow, it looks up data using a table
scan. Using the metaphor of an encyclopedia, a table scan is exactly like
starting at Volume A and reading every line until you find all the information
you’re looking for. To be sure you haven’t missed anything, you have to read
all the way through the Z’s.
What to Index? This is not a simple question. Here are some guidelines, but keep in mind that for every guideline there are circumstances in which it does not lead to the best indexing choice. When you create a primary key, it will be indexed automatically with a clustered index if you don't explicitly create the index as non-clustered. Often the primary key is not the best use of the only clustered index you can have for the table, so you should always explicitly create the index based on what you consider is the ideal columns(s) for taking full advantage of a clustered index. Foreign keys, columns that reference primary keys in another table, should almost always be indexed, but that is not done automatically when you create the foreign key. You must do so explicitly. It is of critical importance to index columns that are frequently used in the WHERE clause of important queries, but the subtleties of deciding which columns or combinations of columns to index is well-beyond the scope of this article. However, keep in mind that this is perhaps the most important single aspect of performance tuning, and you must find the expertise you need to get it done. You can experiment with indexing using Query Analyzer. Be sure to do it on a test database because your experiments will negatively affect performance in the database you are experimenting with. We find that the graphical statistics window is not as useful as the old text-based tool. Use the "set statistics to on" statement to enable statistics output for the query window. This will generate text output of performance metrics. The important metric is the number of logical reads. It is a direct measurement of query efficiency. The lower the number of logical reads, the better. Execute the query and note the number of logical reads. Test different indexing schemes by comparing the number of logical reads they generate against this baseline number. When you hit the right indexing scheme, you may see some dramatic changes. 50,000 reads being reduced to 5 reads is not uncommon.
Auto Create Statistics This is an option you can set on a database by database basis. By default it is on, and it usually should be left that way. It allows SQL Server to automatically create a collection of statistics for the data in an unindexed column. In the absence of a useful index, the statistics help it decide how to execute a query most efficiently. Statistics collections are not as efficient as indexes, but they are much better than nothing, and SQL Server can create them on the fly when it needs additional information to make better query execution decisions. A very good DBA might not want to enable statistics creation because he/she is on top of the performance issues and Auto Create tends to clutter up the table with pseudo-indexes. That’s OK for a very experienced DBA. Everyone else should enable Auto Create of statistics if only to find out where SQL Server thinks you ought to have an index, but do not. Statistics collection appear to be indexes in the system tables, but have a system generated name with a prefix like "_WA_". When you see these collections defined on a column, you might consider putting an index on the column. That is not always the case, however.
Identifying Problems in Application Code Trying to find poorly performing routines in half a million lines of source code is a daunting prospect, but it ’s fairly easy to come up with the top 10 worst performing queries in a running application. Take a quick, simple look with SQL Profiler, a tool that comes with SQL Server. Capture the events Stored Procedures: RPC Completed and TSQL: Batch Completed. Throw in the Exceptions event to make sure your applications aren’t generating errors that have been going undetected. For metrics, capture the Duration, CPU, Reads, Writes and the Textdata (text of the actual code that is being executed at the moment these measurements are taken). Let Profiler run for a time during a period of high database usage and log the results to a file. After you’ve captured enough data, sort it by duration and note the statements that are taking the longest to run. You can sort by the other metrics too, but you will probably find the statements with the longest duration are also the ones with most CPU usage, reads, etc. When you find the poorly performing queries, you can use the ShowPlan and Statistics I/O options in SQL Query Analyzer to find out why the statements are inefficient and how they can be fixed. This can require some experience to understand what’s going on, but there are some simple things to look for. ShowPlan helps out by highlighting these problems in red. Read the output from left to right, top to bottom. The percentages refer to the percent of total work involved that occurs in that step of the job. Look for the heavy hitters. Hover your mouse over a graphic to pop up a window of detailed information. The most common problem is that the query does not have an appropriate index to use and is using a table or index scan on a huge table. This is the condition we described above where it is reading the whole encyclopedia from A to Z. The fix is simple and the results can be dramatic. Put an index on the column or columns that are being used in the JOIN or WHERE clause of the highlighted statement. This often requires experience and/or experimentation to find the best column(s) to index.
Locking SQL Server locking problems often result from inexperienced developers who do not realize that they can and should change the default locking behavior for certain statements. Locking problems often go undetected during the development and testing of a database application because it is difficult and expensive to generate testing scenarios to simulate dozens or hundreds of users hitting a database simultaneously. The problems usually start to show up a few weeks after roll-out as the amount of data and the number of users increase. Locking is a complex issue, but it is necessary to understand a little bit about it in order to understand how it can be fixed. The crux of the issue is the contention between processes that only read the data in a table and those that modify it. SQL Server's default locking behavior is conservative in this regard. By default, any number of reading processes can read the table at the same time, but no process can change data while it is being read. The other side of the coin is that no process can read the data while another process is changing it This assures the data consistency that is necessary in some kinds of database operations. For example, if one process were updating the Accounts table to reflect a transfer of funds from one account to another, and second process was summing the total of funds in all accounts, it would be critical to enforce this kind of consistency. Otherwise, the second process might read the data after the first process had debited one account but before it had credited the other. The total of funds would be in error. But in many other cases, the processes reading the table do not need an absolutely consistent view of the data. For example, if someone is opening a report that summarizes last month’s sales figures, why lock the tables involved? Even something that is presumed to be up to the minute often doesn’t need absolute consistency. A librarian might want to see if a particular book has been checked out. The chances that it's status will change in the 10 milliseconds that it takes the query to run are very slim, and it doesn’t matter much even if it happens. In these kinds of queries, you may safely override SQL Server's default behavior to avoid these locks. It can be done several ways, but for a variety of reasons placing a locking hint in the query is most often the best method. Usually the developer should add the directive "WITH READUNCOMMITTED" to queries of his type to eliminate locking altogether. This is especially important when batch processes update a table. Users can only type or click mouse buttons so fast, but an automated process can issue hundreds or thousands of inserts, updates, or deletes per second. There can be a huge performance bottleneck when other processes need to read the table involved. And locks are a lot like freeway traffic at rush hour. A slight delay anywhere causes traffic to back up behind it very quickly. Although SQL Profiler and System Monitor can capture some locking information, they are less useful in diagnosing locking problems than they are in other areas. Microsoft has more useful utilities available for free download, however the documentation is very thin. As a place to start, take a look at "How to Monitor SQL Server 2000 Blocking" on the Microsoft web site.
Hardware Resources To repeat what we said above: a hardware bottleneck does not always mean that your hardware is inadequate. Inefficient application code also manifests itself in hardware bottlenecks. Too many table scans will keep your disks spinning, and bad application design or poorly written SQL code can waste memory and CPU cycles. Since we will be talking about monitoring tools in this section, here are some general considerations to keep in mind. Monitoring puts its own load on the server and, depending on how much you are monitoring, it can be a big load. You should not run System Monitor or Profiler from the SQL Server machine itself. You should run them on another machine and have them write their logs there also. Don't try to monitor everything at once. A few carefully selected counters, such as the ones described below, should be enough to start with.
Memory The Standard Edition of SQL Server can only use 2GB of memory, no matter how much you have on the server. Keep in mind that memory is cheap, but SQL Server Enterprise Edition is not, nor are the advanced versions of the server operating system that may be required to utilize all the memory you buy. Before starting down this road, make sure you understand the true cost of upgrading memory. That being said, get as much memory as you can afford. SQL Server loves memory, and it is often the cheapest way to get the performance boost you need. If you have SQL Enterprise Edition running on Windows Advanced Server or Datacenter Edition, with 4GB or more of RAM, you need to configure special memory management options at the operating system level. Otherwise you should leave SQL Server's default memory settings alone, unless you are sure you know exactly what you are doing.
How Do You Know If You Need More Memory? The simplest measure is to look at the SQL Server Buffer Manager: Cache Hit Ratio counter in System Monitor (a tool that comes with Windows and is already installed on your server). Log a typical 24-hour period so you get the big picture. Average cache hit ratio should be in the high nineties. 99% is OK, 95% is not. The counter Memory:Pages\Sec is also important. It is normal for this reading to spike very high for brief periods, but it should average 20 or below over a typical 24 hour period.
CPU Log the following counters in System Monitor for a typical 24 hour period. If the Processor: % Processor Time frequently exceeds 80% for several minutes at a time and/or System: Processor Queue Length (total) exceeds an average of 2 per processor (i.e. a total of 4 on a 2 CPU server) for several minutes at a time, you may want to add more or faster processors. Keep in mind that the size of L2 cache is sometimes more important than actual processor speed, so get as much as possible when you upgrade.
Disk I/O Disk I/O can be measured with System Monitor also. Take measurements for each individual physical array. The totals for all disks mean very little. If Physical Disk: % Disk time averages over 60% for several minutes at a time and/or Physical Disk: Avg. Disk Queue Length exceeds 2 per physical disk in an array (i.e. a 5 disk RAID array is good for a queue length of 10), you probably could use more or faster disk arrays. Disk configuration on a database server is a complex subject. There aren't any quick generalities that are useful here. If System Monitor indicates a serious I/O problem, you probably need to consult with an expert. However, remember that the disks may be spinning because your application is not adequately indexed. Take care of that first. The disk problem might disappear.
Database Configuration
Most of the default database configuration options that SQL Server uses aren’t
going to give you serious performance problems. If an inexperienced person has
been tinkering with database options, you may need to readjust them. Auto
Shrink is one to watch out for.
In Conclusion We hope this has helped you better understand the issues involved in improving SQL Server performance. It may not have been easy reading, but we did cover a lot of ground in a short time. If you have further questions or would like clarification on some point we covered, I would be glad to help. If you have comments or suggestions to improve this background paper, I would like to hear those too.
About the Author Don Henderson is the president of SQL Server Expert Help, LLC. (www.SQLServerExpertHelp.com), a database consulting group specializing in SQL Server performance tuning. Don has been working with SQL Server almost as long as it has been around. First certified by Microsoft in 1993, he still holds current certifications as an MCSE and MCDBA. Don can be contacted at email Copyright 2005 by the author. home   about us
services and support
client list
contact us
email
Copyright © 2005, 2006 SQL Server Expert Help All rights reserved |