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

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  

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