Meme Monday: Dumb Questions

Meme Monday: Dumb Questions

Today is Meme Monday, and I’ve decided to join in on this particular topic. Thomas LaRock (blog|@SQLRockStar) has given us the topic of “Dumb Questions” for today.

No such thing as bad student, only bad teacher. Teacher say, student do. ~ Mr. Miyagi

Okay, some of you may say this is a cop-out, but it’s how I truly feel. Like the students in Mr. Miyagi’s statement above, there are no bad questions, only bad answers and I see plenty of those out there. I will never look down at or think poorly of someone that asks a question about SQL Server. I believe it was Paul Randal (blog|@PaulRandal) who said that we were all at knowledge level zero at some point in our past.

If you have questions, ask away. They’re not dumb questions. If answering questions, please be careful with your replies because students will do what you tell them. Give them good advice, and they will do good things.

A Bad Teacher

A few months ago, I was called in to consult on an application whose operations was being transferred from another operations team to one of the teams in our operations team. The application had a lot of performance problems on the SQL Server side, and they asked me to see if I could investigate the performance problems in advance of the transfer. I was happy to help out, but the existing operations team said no. The existing operations team said they already had two teams working on the performance problems and I would only get in the way. One of the teams investigating the issues was being led by the “SQL Expert” on their team.

When the transition officially started several weeks later, I was finally able to inject myself into looking into their performance issues. Neither their “SQL Expert” nor the two teams working on the performance issues had resolved any of the performance problems. once I finally got access to the servers, I was able to resolve their two biggest issues in less than half an hour.

One issue was that CPU utilization would spike to 100% while certain data maintenance jobs were running causing user connections to timeout. The servers were running balanced power plans and were running at 50% power. Simply setting the power plan to high performance gave them enough extra CPU power that the spikes no longer reached 100%, and end users were no longer getting timeouts.

The second issue was that a data maintenance job had not run successfully in months because whenever it ran, it would run for more than two days until someone finally noticed and killed it. The existing operations team and their “SQL Expert” had decided to disable the job out of fear of the performance impact that the job may be causing. In fact, the catalyst for disabling this job was that they believed that it might be causing the first issue with CPU spikes. Unfortunately, there was no improvement in the CPU spikes when they disabled this job. I investigated and determined that it was a coding issue that caused the job to run in an infinite loop.

The process would get a count of the number of records that needed to be processed, it would loop through choosing the top 1000 records and process them. Then it would use @@RowCount to get a count of the number of records affected and subtract that from the initial count. It would continue looping until the initial count had been decreased down to zero. The problem was that query to get the initial count had slightly different criteria than the query that chooses the top 1000 rows to process. The initial query was getting a count of 143 and in the loop, 0 rows were being processed. No matter how many times you loop through it, 143 – 0 = 143. The loop never updated any data and could never complete.

When I reviewed the servers, the servers violated almost every rule in the Standards and Best Practices document that my team had written. They were easily the worst configured SQL Servers I had ever seen. That wasn’t the key tell for me though. The key tell was the SQL Expert’s recommendation for their database mirroring configuration. Their application had two databases on two servers. DatabaseA was on ServerA and mirrored to ServerB. DatabaseB was on ServerB and mirrored to ServerA. The servers frequently had performance problems that the team would try to resolve by failing over one of the mirrored databases. Obviously, if a server is not capable of handling one of the databases, it’s not going to be able to handle both of them. Ultimately, the failover would cause problems on the other server.

The operations team and engineering team had two servers sitting idle because they wanted to change the mirrored databases to each have their own failover server instead of failing over to each other. They had not been able to make this change because the engineering team refused to grant them a six hour downtime window to make the change. I couldn’t believe my ears. I told them that it would be an outage of less than 5 minutes for that because the only downtime incurred would be when they change the connection strings in the web.config file of the websites (changing web.config causes IIS to restart the application).

Their SQL Expert had requested a six-hour downtime window because he thought that all traffic to the database had to be stopped in order to set up database mirroring. This means they must have taken a really long downtime when they first set up mirroring.

My advice is to avoid calling yourself an expert if you’re not. If you put yourself up on too high of a pedestal, it can be a very long way to fall. Now when I think of their SQL Expert, I think of one of my favorite quotes from the movie The Princess Bride. I bet you can guess which one it is.

You keep using that word. I do not think it means what you think it means. ~ Inigo Montoya

A Good Question

In lieu of posting a dumb question, I’m going to post a good question. Phrases like “SQL Guru” or “SQL Expert” are thrown about quite easily these days. I’ve been called just about all of these not-so-clever monikers at different times throughout my career. Recently, I was meeting with an engineering team to help them review the architecture of an application that they were taking over from another group. The other group had designed the architecture and built a prototype. I was introduced to the engineering team as the “Certified SQL Master” on our team.

A member of the engineering team chuckled a little at the description and asked, “So what makes you a Certified SQL Master?”

My response was simply, “Because I am a Certified Master in SQL Server. It’s a very high level of certification that only a few have achieved. There are less than 90 of us worldwide.”