Archives

I Wish my SQL Server was Slower

I know that the title of this article may sound ridiculous, but hear me out. My current employer purchases higher-end servers than most companies of the same size. Our IT Lead believes in overbuilding servers so that there is room for future expansion. “So where is the problem?” is probably the question in your mind right now.

Well, the problem is simple and something most IT professionals will relate to:

  • I am constantly under a time crunch.
  • The data needs of my current employer can never be satisfied.
  • They will always want to view their data in a different way, format, or perspective.
  • I am regularly tasked with designing ways for Made2Manage to work around our business model rather than the other way around.

Due to all of these, I have a project list that is a mile long and growing. I will quite literally never run out of things to do. How I spend my time is scrutinized, and much of my learning goes on during my free time at home.

So what does this have to do with having a slower SQL Server? I am encouraged to write T-SQL code as quickly as possible, test for accuracy, but not necessarily speed. After all, we have the “big iron,” and optimization time is considered wasted time. From my employer’s view, they would rather I spend the hour I would normally spend in optimization working on the next report. We can get by this way; because in the bigger world of SQL Server, our databases are considered small at around 10GB each. I’m not suggesting that I don’t optimize my SQL queries, but I can tell you that I would like to spend more time on it. I wish it was considered a priority here, rather than something I surreptitiously do behind the scenes.

Why do I care? There are several reasons:

  • If you write enough poorly performing code, you will eventually run into performance bottlenecks that “bigger iron” will not solve- or at least not cheaply.
  • Although mediocre code will run fine on a 10GB Database, it will not scale up with a terabyte database. I eventually want to work on very large databases, and I don’t want to pick up bad habits along the way.
  • I don’t want to write code that is “good enough;” I want to learn to write the best code I can. I want to be the best- period.

I know it’s a paradox, but what I’d really like sometimes is a slow SQL Server.

Related posts:

7 comments to I Wish my SQL Server was Slower

  • Darren

    You can have mine slow server if you want! Care to trade?

  • Andrew

    Can’t you just simulate a slow server on another machine? You mention all the time that you use a test server when you write code.

  • Andrew, there isn’t any point in that because my main server has a totally different hardware configuration from a comparison server. Optimizations which work for one server will not necessarily work on another.

  • scott

    I wish I had servers that were too fast! I do understand your point though and I have ran across it myself. Often I would like to spend the time improving my VBA code but I can not justify the time since it works.

  • SQLJoe

    We just made an all-in-one-shot migration, OS, SQL & Virtual. After the upgrade, users are complaining that our custom ASP based application (developed by vendor) is running slow. I’ve been trying to pinpoint what’s causing the “slowliness” and so far I can’t really come to a conclusion.

    Only one virtual SQL Server 2008 is running with more CPUs and RAM than physical server had before the upgrade. We have an average of 50-70 users accesing the application at one time. CPU sometimes spikes to 75%-90% for periods of 3-4 seconds, then fluctuates between 20%-50%.

    Scenarios Tested & Possible culprits on my list:

    1) Disk I/O (discarded, files separated by 2 RAID 1 and 1 RAID 1+0)
    2) Processor (discarded, no CPU oversubscibing)
    3) Memory (discarded, bumped to 16 GB with no noticeable performance gain)
    4) Maintenance (discarded, index rebuilt, reorganized, statistic updated nightly, log file backup and shrink every 48 hours)
    5) Transactional replication (discarded, stopped/deleted to test impact…no major performance gain after it was stopped)
    6) Indexes (possible, according to DB Tuning Advisor 70 indexes missing/needed)
    7) Bad code / query optimization (possible, how can I prove this and put this nicely without agitating the ant’s nest…programmers ? )

    I have done SQL Profiler traces, disk I/O stress tests (SQLIO, SQLIOSIM) and the response times seem ok.

    Any other ideas? Have I overlooked anything on my config or maintenance?

  • Joe, thanks for the comment. I’m not the person to answer that kind of question. However, I would suggest checking out
    http://www.brentozar.com or
    http://sqlblog.com/blogs/adam_machanic/ or
    http://scarydba.wordpress.com/

    They would be your SQL tuning experts.

    I can personally vouch for Brent Ozar as he is one of the most helpful people I’ve met with SQL Server.

    However, one thing I would like to mention is that you aren’t automatically shrinking your databases are you? That’s definitely a bad practice.

  • Ray Collazo

    Once upon a time I too wanted to write the best code out there: Use as tight of a code as possible to get things running.

    Problem is that the Time required to create good code is hampered by the fact that I DO have a life outside of work: I have a Girlfriend: A House: 3 Cars: 3 Dogs and a Cat: Parents: Friends… In other words, I have a LIFE Outside of work that needs to be attended to, and the Time I have each day doesnt get any longer.

    Once upon a time I DIDNT have a life: I was bored most of the time, and way back then I Was Able to sit around and learn the code better and do things streamlined and “Be as good as I can”.

    The problem being is that the Irons have gotten BIGGER. and theyre getting PROGRESSIVELY bigger every time I turn around, making my want of streamlined code almost moot. That and the fact that the bigger irons run my code Way faster than I had dreamed of at first really made me go “Is it Worth my aggravation to try to make this better?” These days Good Enough in some caes gets the job done, and I can progress to the other projects on My neverending list of things to do.

    Preservation of Sanity among all of my projects has become my #1 priority, and I CANNOT allow one hiccup in code to stop me: I would rather bandaid it and keep going. It would be LOVELY if I could go back and improve upon the code, but if it works, and doesnt crash, Ill leave it as is, UNLESS its a mission-critical bit of code.

    Mind you that Doesn’t make me “lazy”: Im Never sitting still: Heck I dont even have Cable TV at all, and I Rarely play games. I’m fully engaged in life, and going through Code and making it better is actually a luxury now.

    Being both IT And HR in my job is pretty grueling. I Really look forward to going home and getting away from it these days.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>