SQL Server Tips by Robin Schumacher

SQL Workload Analysis

An interesting SQL story actually happened on a Teradata system and not a SQL Server, but it still demonstrates how important it is to find bad SQL, preferably before it is let loose on an unsuspecting database server.

“Bob” was a really nice guy but an absolute novice at writing SQL code. Bob was fresh out of computer science school and was hired at a large national insurance company.

The company maintained insurance claim information dating back to approximately the time of Noah’s flood, so one can imagine the volume of data being managed. Because of this, the company purchased a very expensive and large Teradata system that served as the company’s data warehouse. Bob was placed into a group of SQL developers that were charged with the task of writing decision support queries against the mammoth Teradata warehouse.

One day Bob asked the DBA group to look at a query he had written against the warehouse. It was truly an absolute monster.

If a DBA has never had the pleasure of working with Teradata, he has missed a treat because it is a database junkie's dream come true. Infinitely scalable architecture, massive parallel processors, tons of RAM, and fast hashing algorithms are used to distribute the tons of data among its many storage devices. Teradata also has a unique EXPLAIN plan that not only communicates the paths used to obtain a SQL result set, but also gives a time estimate of how long it believes the query will take to run.

Bob was asked if he had run his query through the EXPLAIN utility and he said, he had not. In fact did not know that such a thing existed. So without any other review of his query, it was put through an EXPLAIN. Teradata went through its computations and issued back a response. The year that this happened was 1993, and according to Teradata, Bob’s query, if executed, would not finish until the year 2049.

This story is completely true and highlights how damaging SQL can be in untrained hands. Unfortunately, there are many SQL novices currently working in IT shops getting ready to submit “the big one,” and their number is growing. Faced with a shortage of qualified database personnel, companies are throwing guys like Bob into the meat grinder and expecting them to write SQL code that meets the response time expectations of end users.

The DBA needs to have the right game plan in place for finding and fixing problem SQL code in the database. Fortunately, SQL Server is pretty good in terms of helping DBAs locate and analyze potentially bad SQL. By using the roadmap and scripts provided in this chapter, the DBA should be able make short work of pinpointing any bad SQL being run though the system.

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