Blog

Opening Eyes On Query Performance

Over the past week, I've given 2 national webinars and an in-house presentation on query performance. In particular, how companies are completely in the dark as to how to manage this most important component of IT performance. The feedback I've received was incredible and so were some statistics.

For the over 1,000 folks I spoke to:

- 85% said they learned something

- 70% said query performance is not owned in their company

- 75% said query performance is handled "reactively", instead of "proactivley"

So, these statistcs say I'm spreading a message that is a new concept and that most people are clueless as to who owns query performance in their company. And even when query performance is addressed, it's in a reactive scenario (not proactive).

This is all good news, because that means one company at a time I'm going to keep opening eyes to the cold fact they are needlessly spending tens of millions of dollars (if not hundreds of millions) and leaving much more than that on the table in the form of lost revenue.

I think since I'm "matter of fact" with my delivery and hold no allegiance to a database, company or technology, this gives me the freedom to say exactly what's on my mind. After 22 years and 40 companies, I've had it with overpaid blow hards that cloak themselves as a consultant with fancy reports and leave you wondering post sales "what did I buy exactly?".

Companies needs to stop thinking that DBA's can handle query performance as a discipline. They simply don't have the time and about 50% of the DBA's I've ever met really aren't that interested in query performance.

Someone, somewhere long ago in an IT dept far far away thought it would be a good idea to have DBA's be responsible for query performance. This couldn't have been a bigger mistake. Once you separate this discipline into it's own team, with 100% focus on query performance the gains for the company are literally surreal.

Please give me your thoughts. I'm on a warpath and I don't plan on stopping anytime soon. I'm at cat@dbgtools.com.

Curt

Query Performance - The Missing Link In Your IT Dept

After working in over 40 companies now, there is either some amazing coincidence or the discipline of query performance is not truly "owned" by anyone. Yes, every company says they "have it covered" to some extent. Usually, this means the DBA's tune queries. But the reality is that the typical DBA simply does not have the time to tune queries everyday, therefore can't possibly build the skill set desired for such a discipline.

What do they say....repitition breeds proficiency? The point is you can't possibly be as good at something you do 20% of the time that someone else does 100% of the time.

A day in the life of a DBA is immense. First and foremost (way before performance) is the availability and integrity of the database. Oh, and then there is the constant cycle of applying patches, migrating database objects between databases, creating new databases, answering dozens of questions from various sources, etc. etc. etc.

What ends up happening is performance takes a back seat. And when "performance" is looked at, it's usually database performance that is the focus. Not query performance. Too often, a shotgun approach is taken to turn a big knob on the database to make all the queries run faster. Did that fix anything? Of course not, but you can bet the queries will run faster. For now.

Because there is a mentality to want to kill many birds with one stone by tuning the database, we never fix the true problem. The queries. So, the common cycle of making changes at the database level, partitioning, buying hardware, etc. is prescribed instead of doing what's right....tuning the queries.

I propose that we quit thinking of ways to take broad strokes at "fixing" query performance and start making broad strokes at "diagnosing" what queries to fix! Whether it's fixing the anomalies that bubble to the top of the averages or the individual elapse times, the idea is to determine which queries bring the greatest return.

So, let's trade in the habit of taking the shotgun approach of tuning with tactics like partitioning and disk array tactics intended to evenly distribute I/O. Why distribute I/O? Eliminate it. That sounds far more efficient to me. Eliminate the I/O by tuning at the level that's needed, the query level.

Having said all that, it takes a unique process and discipline to help identify what queries will bring the greatest return. This discipline will need two things. The right product and the right skill set.

So, share with me any thoughts and think about the following:

  1. How is query performance addressed in your shop?
  2. Is it effective?
  3. How do you know it's effective?
  4. Could there be a possibility of a different, maybe even better, methodology that exists to make your query performance discipline a world class service?

I have assumptions about the answers to the first three questions, but I know the answer to the last one for sure. The answer is "Yes" and it's called DPOP. DPOP is the methodology I've created for addressing query performance and you'll quickly see how it applies to any database (DB2, Oracle, SQL Server) and for any application (developed or purchased).

Is anyone out there?

cAt