Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Robin Schumacher

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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter