 |
| | |
|
Increase Application Speed by Tuning your SQL
Server for Optimum Performance
If your software application
has become sluggish over the years, your first instinct may be to purchase
larger hardware to solve the problem. This is an expensive solution,
so other avenues should be explored first. Many software
applications can achieve a 50 to 75 percent increase in speed by simply
tuning the database for optimum performance.
There are 4 steps to tuning your database to achieve improved application speed.
These are discussed in detail in this newsletter, along with demonstrations on
performing each step.
To see newsletters from prior months, click
here. This newsletter is sponsored by Software Planner (http://www.SoftwarePlanner.com).
Collect Statistics
Before tuning your application, you must understand how your clients use
your application. This is a daunting task for most database
administrators, because they know that different clients use the application in
different ways. Luckily, SQL Server comes equipped with a tool called SQL
Profiler, designed to collect statistics for queries being executed by the
application.
With SQL Profiler, you can turn the profiler on during
business hours and it will determine what SQL queries are being executed by your
clients. It will log those queries to a "trace file" that can used
by SQL Server's tuning wizard. The tuning wizard will evaluate your
existing indexes based upon the statistics collected by SQL Profiler. Then
it will recommend a new set of indexes based on your actual client usage.
 See
it in Action - Click here to see an interactive demonstration that
shows you how to invoke SQL Profiler and use it to collect your usage
statistics.
Optimize your Indexes
Now that you have created your
statistics with SQL Profiler, you can use those statistics to have SQL Server
recommend a new set of indexes.
See
it in Action - Click here to see an interactive demonstration that
shows you how to
use the Index Tuning Wizard using your SQL Profiler
table you created earlier.
Before creating your new indexes, it would be prudent to review them to ensure
that they are good recommendations and to see if other indexes would also
benefit your system. You can find other candidate indexes by looking at
the SQL that was stored in your SQL Profiler table. Look for fields that
are frequently accessed in your select and where clauses, these can be good
candidate indexes.
Each SQL Server table can have 1 CLUSTERED index.
A clustered index can greatly improve performance because it is saved in the
same area as the data, requiring no bookmark lookup, so access to the data is
much quicker. In addition, it is saved physically in sorted order by the
index, so retrieval is much faster.
Tip! When considering CLUSTERED indexes,
consider a column (or set of columns) in which range scans are done. For
example, if your data is separated by project, branch, department or company,
those fields make an excellent candidate for a CLUSTERED index because the data
that is needed by that area can be located closer together. For
example, let's say that your system tracks widgets and you have many different
branches located in different areas that sells those widgets. Let's also
assume that you have a workflow where you change the status of widgets often and
track those status's online. A great CLUSTERED index on the Widgets table
would be Branch and Status.
Tip! Another consideration when creating
indexes is how many to create. For systems that do a lot of adding,
updating and deleting and very little queries, the fewer the indexes the better
(3 or less). For systems that do a lot of reporting but very little
adding, updating and deleting, a large number of indexes is better (5 or more).
For systems that add, update, delete and have a number of reporting and query
screens, it is normally best to keep the number of indexes to about 5.
Make sure that if you have a table that references another table (via a foreign
key), place an index on the foreign key to speed up access to the dependent
table.
Measure the Results
Before running the SQL to create the
recommended indexes, you should run a set of test scripts against your
application, timing each test script using a stop watch. As you may know, the
first time you access a screen in your application, it may be slower than
subsequent times. This is because SQL Server puts the execution path in
cache so that it can more quickly execute the queries on subsequent times.
To eliminate this variable, it is best to do 3 timings for each test script.
You will most likely throw out the first timing and average the subsequent 2
timings.
When doing timings, do not try to test every screen in
your system. Just take a few (probably between 5 and 10 screens) that are
used most often. You should have a test script for accessing a large set
of data, a small set of data, adding data, updating data and deleting data.
To time it, keep a spreadsheet that lists each test script description, the
timings for iteration 1, 2 and 3, and an average of the last 2 iterations.
Once you have collected your timings, run the SQL
Script created by the Tuning Wizard to apply your new indexes. Then run
the same test scripts once the new indexes have been applied. Just like
before, run them 3 times each, taking an average of the 2nd and 3rd iteration of
the test script. Then you can compare your performance to the performance
prior to your new indexes. Do not be surprised if you see at 50% or higher
performance improvement!
See
it in Action - Click here to see
an example of a spreadsheet used for capturing test
script timings.
Create Maintenance Plans
Once your application is running optimally, it is a good idea to revisit the
performance tuning periodically (quarterly at a minimum). SQL Server
allows you to create maintenance plans to keep your database running optimally.
Re-computing Statistics
As your data grows, the distribution of your data changes. For
example, when you first created your application, you may have had 10,000
widgets in ACTIVE status, 20,000 in CLOSED status, and 100 in HOLD status.
As your clients began using your application, the distribution of data may have
drastically changed over time (e.g. 150,000 ACTIVE widgets, 30,000 CLOSED
widgets, and 1,000 in HOLD status). Due to this, your indexes may not be
optimized.
SQL Server can periodically review your data distribution and make better
informed decisions about what indexes to choose under specific querying
requests. To improve this process, you can have SQL Server re-compute
statistics periodically by analyzing your data distribution. It would be a
good idea to create a maintenance plan that does this for you automatically.
You could schedule this to happen at any interval (weekly for example).
See
it in Action - Click here to see
an example of
creating a maintenance plan for re-computing
statistics.
Once you have created a maintenance plan, you will see how easy it is to also
create other maintenance plans. You should also create maintenance plans
for reorganizing your data and backing up your data.
Summary
In this newsletter, we discussed how to improve your application
performance by tuning your database using the SQL Server tools.
To see newsletters from prior months, click
here. This newsletter is sponsored by Software Planner (http://www.SoftwarePlanner.com).
Software Planner Tip of
the Month - You can use Software Planner to track project
deliverables. For example, let's assume that your team is creating a new
release of software and various people on your team must work together to
deliver the solution. The project manager can create the project
plan in Microsoft Project (or any other tool), and then import that data into
Software Planner, allowing all team members to update their percentage
complete on items that are assigned to them.
See
it in Action - Click here to see
how to use Software Planner to create a project plan
in Microsoft Project, import the project plan to Software Planner and have
your team members update their percentage complete.
Software Planner is a web-based
software lifecycle management tool that fully supports the Iterative Software
Lifecycle. To learn more about Software Planner, click this link:
Software Planner.
Free Templates
Pragmatic Software offers free templates for software development. These
templates cover all areas of the lifecycle, from the planning to production
phases.
Templates for the Iterative Software Lifecycle
|
| |
|
| | |
|
|