Last week I used some local user group meetings as tune-ups for my talk that I am giving this Saturday in Nashville.
[Sorry that you had to find out this way Seacoast and SNESSUG. It’s not easy finding out you were being used, but you were. And thanks for the bacon pizza, I can let myself out. Can we stay friends?]
Anyway, part of my talk has to do with how you define if something is a problem. Let’s take a quick example from my travels last week: You have a query that joins eleven tables and views. The code for this join is about as easy to read as anything written by Ayn Rand. The query takes 15 seconds to complete and while it is running it consumes memory and CPU resources. The question becomes: is this a problem?
Is it? Assuming that Paul Randal (blog | twitter) has convinced so many of you to answer everything with “it depends” allow me to interject right here and now and say “fine, what does it depend on?” And yes, there is only one right answer here. And rather than make you wait until Saturday to find out, I’ll go ahead and give you a sample of the knowledge I intend to share with you during my talk.
It depends on your definition of a problem.
Sit down and stop yelling at me. I don’t care about any technical details about what is going on under the hood. I don’t care about how eleven table joins are probably a good indication that the database needs some design work. I don’t care about writing T-SQL in a way that Dr. Seuss himself would be proud. None of that matters. Why? Because nobody knows if any of that is a problem unless it has been defined to be a problem. It’s that simple, people. Really.
An eleven table join is only a problem if you have it written down somewhere that eleven table joins are a problem. A query that takes 15 seconds to execute is only a problem if the end user doesn’t want to wait 15 seconds for the results. An unreadable T-SQL statement is only a problem if someone comes along and documents how to properly write and format T-SQL statements.
Without definitions for such things then all you have is Chaos. And that means Don Adams is your DBA. Nobody wants that.
Imagine if everyone just sat around and complained about things simply because they felt that it was a problem. But they have no evidence to back up their feelings, so they just complain about time, resources, unreadable code, etc. Wait, did I just describe every code review you have ever taken part in? Are you having flashbacks? Next time be prepared for your meetings. Find out if the customer thinks 15 seconds is too long, for example. Start with the user experience because that is always going to play a big part in how problems are defined. In fact, in my talk I list three common sources for definitions:
Performance benchmarks – Quest released a great poster to help you understand many of these benchmarks and you can get it at http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf. You’re welcome.
User experience – This is when the user doesn’t want to wait 15 seconds for the results and they tell you as much. Write down what they tell you is acceptable and not, and use that as how to define if something is a problem or not.
Business requirements – For when those rare moments when business requirements change, you have the opportunity to document them and refer to them as how to define if something is a problem or not.
This means that you already have three sources for how problems are defined in your shop. And if no one is complaining about that query taking 15 seconds then you may want to just close your eyes and believe that a problem doesn’t exist. After all, how long will it take you to redesign the database in order to write a more efficient join? And will that time (and expense) be worth the (at most) 15 second gain in run time?
Well…it depends…right?
Great to see this reasoning be voiced. I have seen the queries with 11 tables here and, after a very quick look to see if there are any easy wins, have taken a deep breath and moved along to the next problem. There is a lot to be said for being guided by the users needs, so long as once in a while you do check to see that there isnt a big population suffering in silence because they dont know it could be better. I guess this is where being a DBA is more art that science.
Thanks Jonathan, I also believe there are times where our jobs are more art than science. And without having defined things as issues or not, it is hard to have discussions with people without them feeling persecuted. It is a delicate balance.
It’s a good point, and one that I’ve lost and gained sight of several times even in this current job (BUT THE QUERY TAKES 30s TO RETURN!! 30S!!! “But the users don’t care.” Oh, well…)
I’m going to presume that the rest of your talk goes into other factors that qualify, oh, let’s say a *multilevel nested view structure* as a problem…like supportability. (Readability IS important. I know you know that, but….gah.) I’ll be interested in seeing that talk some time, or hearing more about it around here.
Happy days,
Jen
Jen,
My talk is about helping people develop an overall performance tuning process, of which ‘define’ is but one part. The other parts are mostly bacon and awesomesauce, but I may be offering an impartial view.