SQL Server Performance Tuning for DBAs

SQL Server Performance Tuning for DBAs

A WHITE PAPER PREPARED FOR ASPE BY TOM CARPENTER

SQL Server Performance Tuning for DBAs

DBAs are often tasked with improving the performance of the databases that they manage. While you can improve the performance of a database or database server by upgrading the server hardware, this method is not always the best method. In this white paper, you will learn what is required to tune up your SQL Servers without the need for costly upgrades. The topic will be addressed from four perspectives:

 Why Performance Tuning Matters  Myths of Performance Tuning  The Performance Tuning Process  Performance Tuning Tools

W

P

T

M

Anyone can throw more hardware at a performance problem, but the most valuable DBAs can use performance tuning processes to save their organization money, increase efficiency and decrease frustration for database users. The following sections address each of these benefits

C

S

If your only solution to performance problems is a hardware upgrade, your performance enhancements will be very costly. By understanding the factors impacting database performance, you can instead optimize existing software on existing hardware to improve performance. Consider the following example scenario:

ABC, Inc Discovers Low Cost Performance Enhancements

ABC, Inc. had a database server that had been in production for two years. The server provided exceptional performance for the first 18 months, but over the most recent 6 months users had started to complain about delays in the system. When the DBE priced servers that were 50 percent more powerful than the existing machine, the price average was $6700. She was sure that the hardware upgrade would resolve the performance problems, but the IT Director asked her to investigate other solutions first.

Furthermore, she realized that no index maintenance procedures had been implemented. When she inspected the fragmentation level of the indexes, many showed fragmentation levels above 40 percent.

Based on this information, she implemented a new maintenance plan. First, she created a job that ran every six months and archived data that was more than one year old. Second, she created a job that defragments the indexes once each week with a reorganization and once each month with a rebuild. After performing these operations and implementing the automated maintenance, the database began to perform well again and no further cost investments were required.

The preceding scenario is based on a real-world situation. Only the company name was changed. The most important lesson to learn is that you can often improve the performance of your servers without costly expenditures.

I

E

Not only will performance improvements save you money on hardware investments, but they will also provide a trickle savings. Your users will get their jobs done faster and this efficiency improvement means they will have more time for other responsibilities. Increased efficiency is not just about saving money; however, it’s also about increasing employee morale. The last thing you want as the DBA is a large group of frustrated users. Their increased efficiency results in your increased peace.

D

F

As the preceding section noted, once efficiency is increased, the logical next step is that frustration is decreased. Users are less frustrated because their tasks are completed in a timely manner. The DBA is less frustrated because she can spend her time dealing with issues other than user complaints related to performance. In the end, performance improvements provide value to everyone involved: the users, the organization and the support staff.

M

P

T

Before you investigate the specific tools used for performance monitoring and analysis, it’s important that you understand the realities of performance testing and achieving a well-performing database implementation. To do this, you need to avoid falling into some of the myths that surround performance analysis and improvement. The following myths seem to continually propagate through the systems administration and DBA world:

 MYTH: If processor utilization is high, a faster processor is needed. o TRUTH: One thing is seldom the culprit.

 MYTH: Eighty percent of the performance is determined by the application code. o TRUTH: Better code is better, but better design is best.

 MYTH: An optimized server is the only key to database performance. o TRUTH: It still has to travel the network.

O

T

S

C

When Microsoft introduced Windows 2000 Server, they made an adjustment to the System Monitor (which was called the Performance Monitor in Windows NT) so that it started with three default counters (% Processor Utilization, Avg. Disk Queue Length, and Pages/sec). This change has been a tremendous help in overcoming the myth of the faster processor, but it does still lurk in the shadows. Sadly, Windows 7 has gone back to only show the % Processor Utilization counter; hopefully, most administrators now know that they must monitor more than this one counter. It’s no question that scenarios exist where a faster processor is needed. However, it’s also no question that a faster processor is usually not the thing that will provide the greatest performance gain. In fact, the culprit is seldom one thing but is usually two or more things that need to be addressed.

Assume you have monitored the CPU utilization on your SQL Server, the virtual memory pages per second, and the length of the hard drive queue. Additionally, assume that CPU utilization is at an average of 82 percent. This reading would be rather high as an average, though not necessarily high as a single reading. You could double the processor speed and only reduce utilization to 80 percent. How could this happen? It could happen if the pages per second were really high. Such a pages per second reading would indicate that you do not have sufficient physical memory in the server. In a scenario like this, you may be able to cut CPU utilization as much as 20 to 40 percent by simply doubling the memory. If pages per second are very high, memory is the likely culprit. If the hard drive queue length is high, then you could also look at getting faster hard drives or using a RAID 0 array to store the virtual memory file. This configuration change would allow for faster reads and writes to virtual memory and may also reduce CPU utilization.

As this example shows, if you look at one counter and make your performance judgment based on that single counter alone, you may well make an errant decision. It is usually best to monitor multiple counters and then consider them as an integrated whole to make your performance improvement decisions.

B

C

B

,

B

D

B

It is very true that poorly written SQL statements and other code modules can reduce the performance of any database solution. However, the common thinking that 80 percent of a database system’s performance comes from the code, which accesses the database, is frequently untrue. You can have the best written code in history and still have a poorly performing database if the physical and logical design is poorly implemented or created. By improving the physical design, you can often double or triple the performance of a database system that already has perfectly coded modules and queries. For example, placing the physical data files on a stripe set RAID array can improve physical writes and reads. Database tables can be partitioned onto separate filegroups to control which data ends up on the different drives in your server. The point is that many things can be done in the physical design of a database system to improve its performance.

I

S

H

T

N

Finally, you can do everything to optimize the server, code and design and still have a poorly performing database system if the network between the server and the clients is overloaded. Performance is both a measurable fact and a perceptive reality. Stated differently, you can measure the server’s potential and ensure that it is fast enough, but still receive communications from users who feel that the “server is slow” because the network cannot handle the bandwidth demanded. With a well-performing server, the data still has to travel across the network. Therefore, you will need to ensure that the network bandwidth is sufficient for your purposes. If you do not have control of the physical network, be sure to check with your infrastructure network administrators before you implement a database solution that is bandwidth intensive.

T

P

T

P

The performance tuning process is represented in Figure 1. The first step is to analyze the current performance level. You cannot be sure you have improved performance (aside from perspective-based analysis, which can differ from user to user) unless you can measure the way the system performed before and after your changes were made.

Figure 1: The Performance Tuning Process

P

T

T

Microsoft provides several tools that can be used to analyze the performance of a SQL Server system. These tools include the Activity Monitor, Performance Monitor (Reliability and Performance Monitor in Windows Server 2008), SQL Profiler, and the Database Engine Tuning Advisor. It is beyond the scope of this white paper to provide detailed instructions on the use of these tools, but the following sections provide a description of each tool. For more information about the use of these tools, see our SQL Server training classes at http://www.aspe-it.com.

A

M

The Activity Monitor, shown in Figure 2, is available from within the SQL Server Management Studio (SSMS). To access the tool, simply launch SSMS, connect to the desired database server, and then right-click the instance name and select Activity Monitor. From here, you can double-click on the Activity Monitor to open it and view the information it provides.

Figure 2: SQL Server 2008 Activity Monitor

The Activity Monitor was changed significantly from SQL Server 2005 to SQL Server 2008; however, the same information is available. Using the Activity Monitor you can look for dozens of performance related parameters including the following important ones:

 Locks and blocks  Wait times

 Physical I/P operations

 Most recent query by connection

P

M

The Performance Monitor is a Windows server tool that has gone by many different names. In Windows NT, it was the Performance Monitor. Windows 2000 and 2003 called it simply Performance. Windows Server 2008 changed the name (and functionality) to Reliability and Performance Monitor. In Windows 7, it’s back to being the Performance Monitor. Whatever you call it, you can use this tool to monitor literally hundreds of different counters (dozens specific to SQL Server) in order to locate performance problems in your system. Figure 3 shows the Reliability and Performance Monitor in Windows Server 2008.

Figure 3: Reliability and Performance Monitor in SQL Server 2008

more accurate evaluations of the server’s performance and it is the recommended was to use Performance Monitor for performance problem analysis.

SQL

P

The SQL Server SQL Profiler is an excellent tool for performance analysis. The SQL Profiler, shown in Figure 4, is used to create traces of activity within SQL Server. You can capture the actual SQL statements being executed against the server. You can monitor for deadlocks as well. The SQL Profiler can save the trace it captures to a file or a SQL Server table. The saved trace can be used with the Database Engine Tuning Advisor to generate automatic recommendations for performance improvement.

Figure 4: The SQL Profiler in SQL Server 2008

The final tool addressed in this white paper is the Database Engine Tuning Advisor (DTA). DTA is used to analyze a SQL Profiler trace or an SQL load script to determine recommendations for performance improvements. DTA can recommend several improvements including:

 Indexes  Partitioning  Indexed views

Once the DTA has determined the actions that can improve performance, you can either apply the actions directly (i.e., automatically create the indexes, partitions or indexed views) or save them to a T-SQL script file. It is recommended that you save the actions to a file for documentation purposes. This documentation makes it easier to undo the changes or reapply them at a later time.

S