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

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  

 

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