| |
 |
|
SQL Server Tips by Burleson |
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:
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 |
