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

Physical Design Overlooked

When troubleshooting performance problems, why is it that the physical database design is so often overlooked? The primary reason is that most DBAs have been taught that bad SQL or insufficient resources (hardware, memory, etc.) are usually the main culprits in poor database performance. It is easier to hunt for bad SQL and throw hardware at a slow-running database than to investigate a database’s physical design for the following two reasons:

  • A proper physical design is difficult to construct.

  • A proper physical design takes time and sometimes lots of it.

Sometimes a DBA is stymied by a purchased application situation in which they are bequeathed a design that they have no control over, or so it seems. However, there are times when physical design tweaks can be made at the indexing level if such customizations do not void any agreements with the purchased application’s vendor.

When data modelers begin creating a non-RDBMS specific database design, the model is labeled as a logical design. The modelers work diligently at normalization, in which they ensure the model is relationally accurate. This means that all entities have primary keys; all attributes in an entity depend on the primary key, etc. That design is then often turned over to DBAs for the creation of a physical design, which is a specifically targeted model for a particular RDBMS, such as SQL Server.

Designing a high performance database is complicated work. It takes skill and experience to develop a design that runs efficiently. The unfortunate truth is that experienced database personnel are at a premium these days, so junior or completely novice IT workers are called upon to design and build databases.

The corporate mindset of retaining a staff of experienced logical data modelers was all but thrown out in the early nineties when a then prominent tool company promised everything under the sun but cracked under the strain of real world business models. Since many of the company’s tools failed to deliver as promised and the designers of these tools stressed logical design as the necessary forerunner of a good system, logical design was discounted with respect to its importance.

Corporations had endured so many logical design projects that never got off the drawing board that Rapid Application Development (RAD) became the accepted mode of development. The end result was, and still is today, that logical and physical design are not taken nearly as seriously in overall system development as they should.

The second reason quality designs are overlooked when the topic of performance is discussed is that a considerable amount of up-front time is needed to create a good design, and time is not what most companies have these days. The application lifecycle has never been shorter in corporations than it is right now.

Projects that would have taken years to complete merely five years ago are being completed in six months or less. To accomplish such a feat requires one of two things: superior personnel using state-of-the art software tools; or the elimination of necessary tasks from the application construction equation.

Usually, one of the first compromises is the abandonment of the database logical design phase. The reason for this is that project leaders believe that all will be well if the database is designed in parallel with the application code. Instead of taking the time to intelligently lay out the necessary components and objects of a database, the database structure is built in the development phase alongside the code base used to run the application. The end result is poor design.

Instead of concentrating on good physical database design, database professionals look to other methods to enhance performance. As they look the wrong way, they risk overlooking the problem and end up with a database that simply will not perform.


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