(somewhat) Social
« MCM Readiness Video Downloads | Main | Quick Powershell Disk Space Check »
Monday
Dec132010

T-SQL Tuesday #13–A Mysterious Business

It’s T-SQL Tuesday again and Steve Jones (blog|twitter), Mr SQLServerCentral, is hosting this month. Steve has provided a topic that is near and dear to the heart of pretty much every DBA – “What issues have you had in interacting with the business to get your job done”.

I really didn’t know what I could possibly write about this given that I’ve never had an issue with the business.

Stopped laughing yet?

Here’s an actual business challenge that I have had the fun to deal with in the last few months.

 

What is the maximum number of columns you can have in a table?

It’s a simple little question like that which instantly sets off the alarms in your head, especially when it comes from a Business Analyst. What worried me even more was that this individual had a habit of promising the moon on a stick to users without actually understanding the bigger picture of what might be involved.

The BA was delighted when I told him that the non-wide table limit was 1,024 columns (based upon the Maximum Capacity Specifications in BOL). This took the plotz-meter from rumble to gurgle.

I heard nothing more from the BA for a couple of months so figured it had been just a curiosity that I had answered, then…

 

Please deploy this work order to create a new table

A work order showed up in my queue. A script was attached to create a bunch of new tables. One of these tables was to have 514 columns, another 365 and another 234 along with a few others that were in the 30-40 column range. That’s some pretty wide tables. Each of these tables was to also have a whole host of indexes (more than 40 on one of them) and were expected to contain several million rows of data. There were also FK constraints (well at least there was some DRI).

With an impending sense of doom I asked why we were creating such wide tables and how they were going to be used. I had a hope that these were going to be used in some kind of OLAP solution. It was a faint hope given that we don’t have Analysis Services deployed anywhere.

I was advised that data was going to be loaded into the tables using a “real-time” ETL process to provide reporting functionality to the user base. There would be a bunch of reports written which could be called, there would also be ad-hoc reporting access to a small group of power users.

I could understand if all of these tables were denormalized to the max however to actually get useful data out would require performing a minimum of four joins.

 

The business needs them

When taken back to the development team they stated that they developed this to the requirements that they were given. I can understand and appreciate this, they are working with what they are provided. I have no doubt they would have questioned this requirement and just been asked to proceed.

I performed a code review and sent back my recommendations. Narrower tables, denormalization, fewer indexes (given that they could never cover any of the queries that would be executed). I also explained the huge performance issues that we were going to experience, the impact to the other databases on the server and how we were going to see some quite significant disk issues.

My recommendations were returned with “these need to go in, the business needs them and we have made a commitment to get them in by next week”.

 

You’re there to keep the boss happy

My concerns were obviously not being heeded so it was escalation time. I went to my boss and explained the situation. I went over the concerns I had, the expected performance problems we would experience, the bad design, the lack of denormalization which would have been the ideal solution for what the team was attempting to do.

It sat with the boss for a couple of days. Finally he got back to me. It had gone up the chain and come back down again. The decision was that “the business” needed this and until I could provide hard numbers that this was a problem in production it would go in.

That was a bit of an epiphany moment. I found out that I am not actually there to do the best job that I can. I’m not there to get the most out of each and every server, eek out a little more performance from a SQL instance and get things running all nice and smooth. I came to the realization that I was just there to keep the boss happy.

 

Performance isn’t everything

The new tables went into production. Disk queuing went through the roof, buffer cache went through the floor and my head went through the desk. Tickets were opened because things were performing so poorly, they were rerouted to the BA and closed as “expected behavior”.

I provided perfmon stats and I showed the severe impact that these new tables and processes were having on our environment. I sat down with management and explained what was going on over and over again figuring that some change would be made.

I tracked down a few of the power-users who were a part of “the business” and asked them what they thought of the whole situation. To a person they told me that this was not what they wanted, not what they asked for and not what they needed.

 

These tables still exist. Performance is still lousy. I’ve stopped taking this to management as it wasn’t keeping my boss happy. I would love to say that the business won, but it doesn’t actually look like the business did. It seems more than likely that this was the brainchild of one BA who got the ear of someone important and made their dream a nightmare reality.

PrintView Printer Friendly Version

EmailEmail Article to Friend

References (2)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Response: news
    Lovely Website, Stick to the excellent work. Thank you so much.
  • Response
    English To SQL - English To SQL - Blog - T-SQL Tuesday #13–A Mysterious Business

Reader Comments (3)

I think everybody can relate to your story in one way or another. Just because you can create a table with 1024 columns doesn't mean you should.

December 14, 2010 | Unregistered CommenterCameron Mergel

Sounds like a classic case of a BA playing tech designer, combined with the "they want it now so we have to do it now, consequences be damned" attitude. Hopefully there isn't much else on that server this thing is pounding!

December 14, 2010 | Unregistered CommenterJosh Feierman

In the last couple of weeks I've been able to offload the solution to a new machine and have implement replication from there to a couple of servers behind a VIP just to try and lessen the suffering of the users. It's definitely helped especially as a rearchitecting of the solution, I've been told, is not going to happen.

December 15, 2010 | Registered CommenterNic

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>