||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
“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