Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
  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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter