While chatting with attendees before my Don’t Fear the Execution Plan webcast, a question was posed: “As a DBA who mainly worries about backups, index fragmentation and checkdb, should I also be looking at execution plans?”
YES! Here’s why.
- Performance is important. A production DBA’s main focus should be the safety and security of the data, but the performance of servers is also something to be concerned with. Companies pay a lot of money in hardware, support, and licensing costs for these servers. Being able to squeeze every bit of performance from them will save the company money – which helps everyone in the company in the long run.
- Be a superhero. Your first response to an ongoing, persistent performance problem may be a hardware fix. Add more memory, add more disks, ask the SAN team to give you dedicated LUNs, beg for a new server. But at some point, it is going to be worth your time – and the company’s money – to tune bad queries. By being the person that can identify the bad queries and provide ways to improve them, you are putting on your superhero cape and saving the day.
- You’re being proactive, not reactive. We all need to be reactive sometimes – that’s a facet of the job. But being proactive – identifying top resource-consuming queries on a server, identifying problem spots in the queries, and suggesting fixes – makes us look better. We show we are bringing value to the company.
- Grow your knowledge of SQL Server. You never know when you may need the information. A new developer may have been able to ask the DBAs questions at her last job; you may need the skill at your next job. You will also learn things along the way about SQL Server internals, and that’s great knowledge to have.
- Increase communication between DBAs and developers. If your developers already use execution plans to tune queries, you will be able to speak their language. If they don’t, you can teach them as you learn – and there is no better way to learn than to teach something! Breaking down communication barriers is a goal my clients typically have. It involves some work and willingness to give, but will make your job better.
8 Comments. Leave new
Following are some graphic true and recent war stories from DBAs I know.
> Performance is important.
> You’re being proactive, not reactive.
Management response: “The system works, why are you spending time investigating performance when you could be adding features!”
To which you may reply that this is your job. To which they reply, “Your job is to do what we tell you to do.”
Until the day the system is slow. “Why is the system slow?” Well we don’t have baselines so I don’t know. “Why don’t we have baselines?” Because I was told not to investigate performance as it wasn’t a profit center…
> Be a superhero.
Management response, literally word for word, “We don’t want superheroes here. We want everyone to be able to do everyone else’s job.” (But don’t do specifications, documentation, or training… nobody has time for that!) To them you are an interchangeable human resource on the balance sheet.
> Grow your knowledge of SQL Server.
No problem here, this is always a good thing. I mean unless you put your certifications in your email signature, and then get reprimanded because it’s non-standard…
> Increase communication between DBAs and developers.
Management: The system is slow.
DBA: This query is responsible using a lot of resources.
Developer: I use ORM/LINQ. I have no idea what generates that in the database.
-Or-
DBA: The web services is retrieving dozens of rows from dozens of tables one by one to fill your ORM query, instead of one query to select all the keys it needs from each table, which it could do.
Developer: We don’t want to redevelop that.
-Or-
DBA: It’s caused by this stored procedure. I’ve rewritten the stored procedure.
Not to be pessimistic or anything.
I wouldn’t be pessimistic, I’d be looking for a new job. A company where management is not interested in optimizing performance, improving communications between teams, or having people teach each other more efficiency is not someplace I would want to stay at. There are better jobs out there!
I have sympathy for Cody’s predicament. A friend once worked for a relatively large enterprise in a relatively small, remote city. Certainly it was the only place in town she could ply her particular mix of SQL skills. The management in that place sounded a lot like the buffoons ruining Cody’s life. In order for my friend to remain true to her own professionalism, she had to find one of those “better jobs out there”. This initially meant a four hours-a-day commute. When that became too much, she had to move. This meant moving the kids to a new school, selling the house etc etc. This upheaval took its toll. It’s often not a simple matter to leave a toxic situation.
Absolutely – and it’s not always easy to leave an abusive spouse, but you’ve gotta be able to recognize when something isn’t the right situation long term.
Re #2
To your point Jes throwing hardware at the problem doesn’t always fix the problem either. A query written poorly enough will bring any system to its knees no matter how big and or expensive. Ex: A table with a few hundred thousand rows with a primary key that’s a varbinary and encrypted.
Now you may ask:
a.
Q: who in their right mind would do this…
A: It’s always interesting what we inherit. Often times a development shop will start without a DBA and won’t bring them in until they have issues.
And
b.
Q: This wasn’t a problem in our dev environment and/or why was this not a problem before.
A: My favorite saying is “a fast table is an empty table so truncate often”… turns out the business doesn’t find this nearly as funny as I do. In this case poor planning didn’t manifest itself until the product/db became successful (more users = more data). As is often the case the development environments didn’t have enough data to replicate the issue nor was there full load / performance testing (see a. above for why).
What to do next:
My first stop was to have a chat with the developers to see how the data was used and what could be changed to increase the speed of the queries (possible immediate solutions suggested: archive unneeded data or add an additional criteria or 2 in the where clause). In this case the magic bullet is the relationship with the development team. They’re the ones who know how the data is used and where it fits in the application. They also know the impact of the suggested solutions. While we don’t have an answer yet, we are on the path.
Oh, wow. Wow! (I’m a little speechless.)
I agree: wow. You bring up a great point about having a development data set that isn’t anywhere near the size of production data set. That makes it very difficult to accurately test code and processes.
We have a similar situation where I work now the production servers are a bit over loaded but, the servers I have in development are more so. Must the databases in dev are sample sets of data because that is all we can fit. So far the pleading for better servers for both has gone on deaf ears. Might be time to Jes’s advise and look for a new job for me also.