SQL Server 2000 Performance Tuning Tools

By Guest Authors on 22 September 2000 | Tags: Performance Tuning , Profiler & Trace

This article comes to us from Brad McGehee at www.sql-server-performance.com. It discusses various tools that can be used for performance analysis and enhancement in SQL Server 2000. Brad runs this site dedicated to SQL Server performance. He has articles on just about every topic of performance you could want.

SQL Server 2000 includes several tools you may find useful when performance tuning your SQL Server applications. The include:

Query Analyzer
Profiler
Index Wizard
Performance Monitor

In the next couple of sections, we will take a look at how your can take advantage of these tools to help optimize your SQL Server-based applications.

SQL Server 2000 Query Analyzer

The SQL Server 2000 Query Analyzer is not only a great tool for developing and debugging Transact-SQL code, it is also a great tool for performance tuning Transact-SQL code. In this section we will take a look at what the Query Analyzer can do, and also learn a little about how it can be used to help identify and resolve performance problems. And like many of the more advanced SQL Server 2000 tools, you need to have a fairly good understanding of Transact-SQL to get the most out of this tool. Let's take a look at some of the key features included with Query Analyzer that can be used to help performance tune Transact-SQL code.

Show Execution Plan Whenever you enter a query into the Query Analyzer, you can run it and see the results immediately. While this lets you know if the results are what you expected, it doesn't tell you much about performance. Sure, the bottom of the screen tells you how long the query ran, but that's about all.

One of the most powerful features of the Query Analyzer is that you can turn on a feature called Show Execution Plan. This option allows you to view the execution plan used by SQL Server's Query Optimizer to actually execute the query. This option is available from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the execution plan are displayed in graphical format in a separate window, available from a tab that appears below the query results window on the screen.

The execution plan displayed in the Execution Plan window may be very simple, if the query is simple, or it may be very complex, if the query is complex. It will show you, step-by-step, how the Query Optimizer executed the query. The execution plan should be read right to left, as the right part of the plan indicates the first step taken by the Query Analyzer, and you continue reading the plan, from right to left, until you get to the left side of the screen, which indicates the very last step taken by the Query Analyzer.

While the graphical representation of the execution plan is interesting, the most useful and powerful part of this tool is somewhat hidden. If you move the cursor on top of each of the steps in the query plan, a pop-up box appears with detailed information exactly what the Query Optimizer did in each step as the query was executed.

The information displayed in the pop-up box is sometimes obvious, such as telling you that a "Clustered Index Scan" was performed, or it may very obscure, such as the "Subtree Cost" was .0376. The details provided not only often need further explaining, they need interpreting. The explanations can be found in the Books Online, but the interpretation takes experience using and performance tuning SQL Server, a topic beyond the scope of this article.

If the query you are working with takes a long time to run, and you want to experiment with your query, you don't have to actually run the query each time. Query Analyzer also has an option to create and display an Execution Plan without actually running the query. This option is also available under the Query menu, and it is called "Display Estimated Execution Plan".

When this option is selected, the Query Optimizer creates and displays the execution plan, but does not actually run it. Notice that this is referred to as an "estimated" execution plan. This means the resulting query plan may not be the exact one the Query Optimizer will use if the query is really run. It will be close though, and it is a good tool if you are doing a lot of experimenting with long running queries. Once you have the query fine-tuned using this feature, you can turn it off and then run it for real, and see how it actually performs.

Show Server Trace The Show Server Trace can be used to help performance tune queries, stored procedures, or Transact-SQL scripts. What it does is display the communications sent from the Query Analyzer (acting as a SQL Server client) to SQL Server. This is the same type of information that is captured by the SQL Server 2000 Profiler, described later in this article. The main difference is that you only see the communications to SQL Server that are instigated by the query or script you are running in Query Analyzer.

The Show Server Trace feature can be turned on from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the trace are displayed in a new window available from a tab that appears below the query results window on the screen.

The results of the trace are in the form of rows, with each row representing a distinct communication from Query Analyzer to SQL Server. Each row includes the text of the communication, such as Transact-SQL code; the Event Class, which describes the type of communication being sent; the duration of the communication; the amount of CPU time used, and how many reads or writes that were performed for the event. This information can be very valuable when analyzing query performance, and when comparing the performance of one variation of a query against another.

Show Client Statistics Like the Show Server Trace feature, the Show Client Statistics can be very helpful when performance tuning queries, stored procedures, and scripts. What this option does is provide you with application profile, network, and time statistics of whatever Transact-SQL you are running in Query Analyzer. This statistics provide additional information you can use to see how efficiently a query is running, and also allows you to easily compare one query against another.

The Show Client Statistics feature can be turned on from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the trace are displayed in a new window that is available from a tab that appears below the query results window on the screen. The results of the Show Client Statistics feature are a series of three tables, each containing a set of statistics on how the query performed. For example, some of the statistics include:

Rows affected by INSERT, UPDATE, and DELETE statements
Rows affected by SELECT statements
Number of User Transactions
Number of server roundtrips
Number of bytes sent
Cumulative client processing time

And many others. These statistics can be valuable roadmaps to how a particular query is running, helping you to diagnose performance-related problems.

Manage Indexes The SQL Analyzer Manage Index tool is not designed to help you diagnosis performance, but it allows you to easily experiment with your table's indexes as you are fine-tuning your queries using the other tools we have already described. The Manage Indexes option is available from the Tools menu, and allows you to add, edit, or delete indexes on any table. This way, you can experiment with indexing, and test your indexes, all in one central location.

Manage Statistics Without you doing anything, SQL Server automatically creates and maintains internal statistics on the rows of data in all of your tables. These statistics are used by the Query Optimizer to select the optimal execution plan of Transact-SQL code. Most of the time, SQL Server does a fine job of maintaining these statistics, and the Query Optimizer has the necessary information it needs to do its job.

But sometimes, the statistics SQL Server creates and maintains is not optimal, and that's what the Manage Statistics Tool is for. This tool, available from the Tools menu of Query Analyzer, allows you to modify how SQL Server automatically creates and maintains statistics. You can add, edit, or delete the various statistics maintained by SQL Server. And because this tool is available from the Query Analyzer, you can experiment with different sets of statistics and see how it affects the query optimizer's execution plans.

Unless you are a very experienced SQL Server DBA or developer, I would not suggest you use this tool. Selecting the proper statistics is a difficult task, and your time would most likely be better spent elsewhere performance tuning SQL Server.

Index Tuning Wizard (for Individual Queries) A little later in this article you will learn about a tool called the Index Tuning Wizard, which can be used to recommend optimal indexes for an entire database. But for now, you need to know that the Index Tuning Wizard can also be run from the Query Analyzer and be used to recommend indexes for specific queries.

For example, say you are evaluating a particular query for performance, but are not sure if the indexes currently on the tables being hit by the query are effective for this particular query. What you can do is to run the Index Tuning Wizard (available from the Query menu of Query Analyzer), and it will evaluate the query, and if appropriate, recommend that a new index be added in order to optimize this particular query. While this can be a useful tool, it is also a little dangerous. This is because it is only evaluating indexes for one specific query. It does not take into consideration other queries that might be run against the tables, or the impact adding new indexes might have on INSERTS, UPDATES, or DELETES against these same tables.

In many cases, it better to use the Index Tuning Wizard to evaluate an entire database at a time, instead of a single query. This way, the Index Tuning Wizard can provide more balanced suggestions.

Take Some Time to Master the Query Analyzer As you can see, the SQL Server 2000 Query Analyzer is a powerful tool with many features, and we have just barely touched the surface of how you can use it to help performance tune Transact-SQL code. You will find it very worthwhile to take whatever time it takes you to learn how to master this important tool.

SQL Server 2000 Profiler

The SQL Server 2000 Profiler is a powerful tool for helping identify SQL Server performance problems, but it is not a tool for the beginner. Essentially, it allows you to capture the communications between your application and SQL Server. While the actual capturing of data is not hard, what can sometimes be difficult for the beginner is interpreting the captured data.

In this section, we will take a look of what the Profiler can do, and also learn a little about how the Profiler can be used to help identify and resolve performance problems. Profiler Features

The SQL Server 2000 Profiler can capture virtually all communication between a SQL Server and any other application. The various communications you can capture are referred to as events, and are grouped in Event Classes. Each Event Class includes one or more specific events. For example, the Event Class "Performance" has eight events, such as Execution Plan and Show Plan Statistics. Profiler offers you 13 different types of Event Classes to choose from.

Every event includes a variety of data columns associated with them. For example, the NTUserName or the ApplicationName that are just two of the many columns that contain information about the various events that you can capture.

On a production system, thousands of events per second can occur, more than you could ever attempt to analyze. To make the analysis of Profiler data a little easier, the Profiler has the ability to filter only those events you are interested in. For example, you can choose to only capture events between a specific user and SQL Server, or from a specific application and SQL Server, or from and to a specific database residing on SQL Server. You can also select which events you want to capture, and which data columns you want to capture about each event. You don't have to capture all events or all data columns about an event. This helps to substantially reduce the amount of data captured. A large part of learning how to use the Profiler is deciding what events and data columns you should and should not capture for analysis.

To make your life easier, the Profiler has the ability to create what are called Trace Templates, which are files that let you save the various traces you create so that you can use them over and over. This can be a great time saver if your trace is a complicated one and you want to run it repeatedly.

Once you create and save a Trace Template, you can run the trace at any time. The results of the trace (the events you capture) can be viewed and discarded, saved as a trace file, or saved in a SQL Server table. Saving your trace results is handy, as you can go back to it at any time to review it.

Once a trace has been run, you can view the results (view the captured events) using the Profiler itself. In the Profiler window you can view each event and the data columns you captured, line by line. In many cases, the events will include Transact-SQL code, which you can view directly using the Profiler, or you cut and past into the Query Analyzer if you want, for more detailed analysis.

Creating a trace for the first time can be hard if you are not familiar with all of the various events and data columns. The easiest way to begin learning how to use the Profiler is to use the Profiler's Create Trace Wizard. This tool includes a variety of basic templates that you can customize for particular needs. For example, the "Find the Worst Performing Queries" template can be used to help you identify those queries that take longer than a predetermined amount of time to run, such as queries that take longer than 1 second. There are a variety of these templates available with the Create Trace Wizard, and you should explore them all.

How to Use the Profiler for Performance Tuning The Profiler is a powerful tool for identifying performance related problems, and can be used at any time during the development process. One of the areas where I find it extremely useful is when troubleshooting performance of existing applications. As you know, I highly suggest that performance be done early in the design phase of the application to prevent performance problems in the first place, but this is not always possible.

For example, you may have inherited an in-house application, or perhaps your company has purchased an outside application that uses SQL Server as the back-end. In these cases, you may be called in to try and resolve application-related performance problems. One of the first problems when troubleshooting applications you are not familiar with is not knowing how the application works. While somewhat tedious, you can use the Profiler to "sneak a peek" at how your application communicates with SQL Server. You can configure a trace in Profiler to capture all of the communication between the application and SQL Server. Then, one step at a time, you can perform some task in the application, and then review the communication between the application and SQL Server to find out how the two communicate.

Interpreting the communications usually requires a solid understanding of Transact-SQL, but if you know what you are doing, you can figure out how an application works with SQL Server. While you won't need to analyze all the communications between the application and SQL Server, you will want to focus on those areas of the application's functionality that appear to be causing the performance problems, such as a specific report, or some update process.

Generally, once you have created a trace of the offending operation, you can review the Transact-SQL in the trace and identify the problem. For example, I analyzed one particular in-house application and discovered the problem was that the VB code used to access the SQL Server data was creating a cursor, and then moving only one row at a time from SQL Server to the application. The problem was that there were several million rows that were sent, which really slowed performance. Once I identified the cause of the problem, I was able to get the VB code rewritten.

Another feature of the Profiler is that you can create traces of your application's activity with SQL Server, then use this trace as input to the Index Wizard. The Index wizard then analyzes the activity and can recommend that clustered and non-clustered indexes be added or dropped in order to boost your database's performance. You will learn more about the Index Wizard in the next section of this article.

As you can see, the Profiler is a very powerful tool, a tool that every SQL Server DBA and developer needs to learn and master.

Index Wizard

The SQL Server 2000 Index Wizard will be a tool you will soon grow to love. Although not perfect, this tool has the ability to evaluate actual production queries running against your SQL Server, and based on the nature of these queries, recommend specific clustered and non-clustered indexes that should be added to your database's tables. The Index Wizard can be run from the SQL Server 2000 Profiler's Tools menu.

This tool can be used during testing of your database during the early stages of your application's development, and it can be used after the database has gone into production. In fact, you should consider running the Index Wizard on your database periodically after your application has gone into production. This is because database usage patterns can vary over time, and the optimal indexes for your database may change along with how the database is actually used.

Although the Index Wizard can be a great tool, you also don't want to depend in it exclusively for recommending indexes on your databases. While the Index Wizard is very smart, it still can't make better choices than a DBA experienced in index tuning. The best feature about the Index Wizard is that it can work with real data from your production SQL Server databases, not fake or simulated data. This means that the indexing recommendations made by it are tailored to how your database is actually used. Here's how it works.

Before you can use the Index Wizard, you must first create what is called a workload. A workload is a Profiler trace or a Transact-SQL script. In most cases, you will want to use a Profiler trace because it reflects actual database activity.

If you want the Index Wizard to produce useful results, it is important that the workload be created during a time period that is representative of typical day-to-day database usage. This way, the Index Wizard will be able to offer suggestions based on the real world use of your database.

Once the workload has been created, it can be feed to the Index Wizard. What the Index Wizard does is to take a sample of the queries it finds in the workload, and analyzes them using SQL Server 2000's Query Optimizer.

Once the Index Wizard is done analyzing the workload (if the workload is large, it can take hours to run) it will recommend what it considers to be the best mix of clustered and non-clustered indexes for the tables in your database, based on the workload it analyzed. In addition, if you already have indexes on your database, and the Index Wizard finds out that they are not being used, then it may recommend that they be removed.

When the Index Wizard makes its recommendations, you have the option to allow the Index Wizard to make them now (not recommended on a production database) to schedule their creation for a later time, or to save them as a script. I recommend you save them as a script, which allows you to take some time to carefully review the recommendations. And only once you are happy with the recommendations, you can then run them using the SQL Server 2000 Query Analyzer at an appropriate time. If you disagree with any of the recommendations, you can easily make any changes you find necessary to the script before you run it.

Performance Monitor

The Performance Monitor is not a tool that is part of SQL Server 2000, but a tool included with Windows 2000. The Performance Monitor allows you to monitor both Windows 2000's and SQL Server 2000's performance, and is a great tool for monitoring and analyzing performance. It can be run by anyone with Windows 2000 administrative rights on your SQL Servers. SQL Server 2000 system administrative rights alone will not allow you to run Performance Monitor.

Performance monitor has the ability to monitor several hundred Windows 2000 performance indicators (called counters), and over 110 SQL Server 2000 counters, more than enough to monitor and troubleshoot SQL Server 2000's performance.

While Performance Monitor offers you an abundance of counters you can measure, in most cases you will only want to monitor a handful of them, saving the more obscure counters for special situations. While you might think that you might only need to monitor SQL Server 2000 counters, and not Windows 2000 counters, this is not the case. More often than not, you will probably spend more of your time monitoring Windows 2000 counters than SQL Server counters. This is because SQL Server's performance is greatly dependent on how Windows 2000 performs.

What Counters Should You Watch? As mentioned previously, there are a handful of Windows 2000 and SQL Server 2000 counters that you may want to monitor on a regular basis. Here are some of the key counters you may want to watch to help you identify potential performance problems.

To help identify potential CPU performance issues, the Windows 2000 System Object: % Total Processor Time counter measures the average of all the CPUs in your server. This is the key counter to watch for CPU utilization. If this counter exceeds 80% for continuous periods (over 10 minutes or so), then you may have a CPU bottleneck. If you do have a bottleneck, then potential solutions include reducing the workload on SQL Server, getting faster CPUs, or getting more CPUs.
To help identify potential memory performance issues, the Windows 2000 Memory Object: Pages/Sec counter is important to watch. It measures the number of pages per second that are paged out of memory to disk, or paged into memory from disk. Assuming that SQL Server is the only major application running on your server, then this figure should average nearly zero, except for occasional spikes, which are normal. If this counter exceeds 0 for continuous periods (over 10 minutes or so), then you may have a paging-related problem. These can occur if you are running programs other than SQL Server on the server, or if you have turned off dynamic memory configuration on SQL Server.
To help identify potential I/O performance problems, the Windows 2000 PhysicalDisk Object: Avg. Disk Queue Length counter is critical to monitor. If the Avg. Disk Queue Length exceeds 2 for continuous periods (over 10 minutes or so) for each disk drive in an array, then you probably have an I/O bottleneck for that array. Ways to remove this bottleneck include: adding drives to an array (if you can), getting faster drives, adding cache memory to the controller card (if you can), using a different version of RAID, getting a faster controller, or reducing the workload on SQL Server.
To help identify if your server has enough physical RAM, the SQL Server 2000 Buffer Manager Object: Buffer Cache Hit Ratio counter needs to be monitored. This counter indicates how often SQL Server goes to the buffer, not the hard disk, to get data. In OLTP applications, this ratio should exceed 90%. If it doesn't, then you need to add more RAM to your server to increase performance, or reduce the workload on SQL Server.

These are just a few of the Windows 2000 and SQL Server 2000 counters that you can monitor and use to help performance tune your SQL Server-based application.

How to Best Use Performance Monitor Essentially, Performance Monitor offers two major ways to collect and analyze Windows 2000 and SQL Server 2000 counters. You can both collect and graph them in real time, or you can collect the data in log files and then graph and analyze them later. The real time collection and graphing option is best when you want to perform testing on your server and receive immediate feedback. It is also handy when troubleshooting specific performance-related problems. This real time mode collects data, by default, every second, and displays in on a graph as it collect the data. You can collect and graph several different counters all at the same time. This can be very useful as it is often important to see how related counters work in unison.

While real time analysis is often handy, it is generally much more useful to log data over a period of time, and then analyze it later at your leisure. The Performance Monitor allows you to select which counters you want to collect, and how often to collect them. For example, you might want to collect information on 20 counters, every 60 seconds, for a 24 hour period. Or you might want to collect information on 50 counters, every 600 seconds, for 30 days. Once the data is collected, you can analyze it in the form of charts by using the Performance Monitor, or if you like, you can also export the data to a database or spreadsheet for a more detailed analysis.

If you are serious about monitoring the performance of your SQL Servers, I highly recommend that you collect performance data all the time on key counters, and then use trend analysis (which can be done in Microsoft Excel) to identify performance trends. For example, you can use the data you collect, and trend analysis, to help predict future SQL Server hardware needs, such as a need for more CPUs, faster I/O, or more memory. Trend analysis lets you project historical data into the future, which can be great evidence to show your boss if you are trying to justify hardware upgrades to your current hardware, or replacement hardware for your current servers.

Performance Monitor is a great tool, and you need to take the time to learn how to master it. You will find it very handy for troubleshooting performance problems and helping you to quantify your future hardware needs.