| |
 |
|
SQL Server Tips by Burleson |
Laying a Strong Foundation
Perhaps one of the largest benefits of working as a Database Tools
Engineer is having the opportunity to witness great examples of “how
not to do it.” One particular engagement that can be used as an
illustration involved a client who was having a terrible problem
with query response time from both his custom-built GUI and ad-hoc
reporting tools. He stated that the response time required to
receive a query result could exceed an hour or more for some
reports. Clearly, something had to change.
After reviewing the problem, the first thing that had to be done was
the running of a complete set of database diagnostics, as well as
server diagnostics, after which the results were analyzed. A number
of definite problems were found in both the placement of database
files and the discovery of heavy database fragmentation, which no
doubt contributed to the overall response time problem.
However, this was not sufficient to cause such poor performance.
There had to be something else that was the main culprit for such a
pronounced lag in response time. As would logically happen in such a
case, one of the typical reports that had been requested was
reviewed in detail. The report included a fairly complex query that
joined a number of database views. On the surface, nothing appeared
out of the ordinary. Yet, it was only upon closer examination of the
underlying views that the problem began to surface.
The first view used in the report was simply amazing in scope and
complexity. It involved a selection of 43 columns that joined 33
tables and had a join predicate that contained not less than 28
outer joins. This was just one view involved with the report!
The important point in this example is that even if every database
tuning guideline for building a system is followed closely, but the
physical database design is wrong, the system will fail. The
database described above was suffering from a case of extreme
normalization. Instead of recognizing and addressing their poor
design, the project leaders had hoped to see some quick tuning magic
that would set things right.
Unfortunately, their solution would not be that easy. Isn’t it
ironic that in an attempt to improve performance, many companies
turn to highly paid database consultants and spend tens of thousands
of dollars on database performance monitors that track thousands of
statistics, only to be left shaking their heads at a system that
still crawls along? As a SQL Server professional, the one question
to always keep in mind when examining a database’s performance,
“What is the actual physical design?”
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 |
