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.
System
(Performance)
Monitor
The System Monitor is not a tool that is part of SQL Server
2000, but a tool included with Windows 2000. The System 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 System Monitor.
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.
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 System Monitor
Essentially, System 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 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.
