| |
 |
|
SQL Server Tips by Burleson |
The Link between Change
Control and Performance
Very few DBAs consider the effects that change control has on the
performance of their SQL Server systems. If there is any doubt that
a change control is needed to protect the database from performance
problems, the following true story should be considered.
A seasoned DBA, who was managing a large packaged financial
application, had to make a complex change to one of the database’s
critical tables. She thought she had built the right script to do
the job. Unfortunately, she did not have everything in place, and
when she ran her change job, she ended up losing a number of
important indexes that existed on the table.
Worse yet, since her table and data looked okay, she thought all was
well and did not know that the necessary indexes were lost. This is
easy to do, especially with today’s financial packages that have
thousands of objects. The next day, many parts of the application
slowed down to a crawl as queries that used to complete in an
instant had started taking forever.
The changed table was identified as the source of the problem, but
while the DBA discovered that the table now had no indexes, she did
not know which columns had been indexed. Again, this is not uncommon
in huge financial applications. Through trial and error, she was
able to get her indexing scheme back in place, but not before a lot
of time had been lost.
This is a good example of where good change control can save time
and headaches. If the DBA is smart and deploys a good change control
tool in the environment, she is pretty well set to recover from such
a problem. Nearly every good tool in this category offers a
synchronization feature that allows a DBA to compare an
up-and-running database with a saved snapshot of that database’s
object definitions. Once differences are identified, a click of the
mouse can restore any missing objects.
A change control tool can also help in physical design iterations.
By periodically capturing changes made to the physical design of the
database, it can be revealed what worked and what did not. And, if a
mistake occurs and actually causes harm, the change control tool can
be instructed to automatically put things back the way they were.
Now, if a company does not have the budget to purchase such a tool,
it may be possible to get by with taking periodic SQL extractions
using the Microsoft supplied tools, but this approach tends to fall
short of what is really needed for good change control.
The fact is that change control protects the
DBA in ways that traditional backup and recovery plans do not. The
basics of change management for SQL Server revolve around the
following four activities:
-
Database/Schema Archiving: This is not
to be confused with data archiving in which lesser-used data is
moved to other databases/servers so performance on the primary
server is increased. Instead, this concerns taking snapshots of
database security, configuration, and data/code object
definitions to preserve what the database looked like at a
particular point in time.
-
Database Comparison: Every SQL Server
DBA has asked or been asked the question “What Changed?”
countless times. Change management allows the DBA to
intelligently answer this question through the use of smart
comparisons between live SQL Server databases or archived
definitions of SQL Server databases.
-
Database Migration: Copying or cloning
all or parts of a SQL Server database can be challenging given
certain environments. Change management provides ways for
intelligent full or partial copies of databases to be carried
out without worry. This is one of the only areas Microsoft
supplied tools can be of help.
-
Database Synchronization:
Synchronization is generally performed for one of two reasons:
when unwanted definition, configuration, or security changes
occur to a database, a DBA will want to roll back to a
particular point in time or a DBA applies definition changes to
one database that they want propagated to other databases.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
