SQL Server 2000 Performance Tuning Tools
October 20, 2000
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.
Server 2000 Query Analyzer
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.
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.
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.
And many others. These statistics can be valuable roadmaps to how a particular query is running, helping you to diagnose performance-related problems.
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.
Wizard (for Individual 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.
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
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.
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
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.
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.
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.
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.
System 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 System 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.
Should You Watch?
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.
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 System 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 System 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.
System 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.