Voice of the DBA

Tag: sql server

  • Finding Problem Code

    Performance of a SQL Server seems to be one of the main concerns for most developers and DBAs. We are under pressure to constantly squeeze more performance out of our applications and reduce the time it takes for users to interact with systems. Reports can be especially problematic as users don’t want to wait for results, but certainly data entry tasks can’t be impacted either.

    Whether or not we can actually make changes to the system, or even have time to bother might be irrelevant. I’m sure there are plenty of databases that some of us don’t even bother to try to tune because we’re busy elsewhere. Likely writing more code for additional features or reports that have been requested. As a result, we may pile up lots of code that isn’t necessarily run often. However when that code is executed, we’ll receive no shortage of criticism if the code doesn’t perform well.

    This week I wanted to know how you might go about finding the code that is problematic if there isn’t a complaint. If you do have time to pro-actively tune your system, what are the techniques you use to examine a system. I suspect the more advanced people will have answers, and I hope they share them as there’s no shortage of readers that may struggle to improve the impressions of their database server.

    What’s the best way to find the SQL statements you need to tune?

    It’s a simple question, but imagine that you know there are complaints, but don’t know what is the best way to focus your time. You, as a new or accidental DBA, want to improve the system, but where do you start? What code is the problem? Is the report that your boss complains about that’s the issue or are there other queries that slow down the report execution?

    Share the way you’d tackle a system when you don’t have a specific query in mind to tune.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Is It Time to Upgrade?

    There was a post from the SQL Server Data Platform Advisor the other day that noted it’s been almost 10 years since SQL Server 2005 was released and it’s time to upgrade. At least, if you want to remain in support. Microsoft Support notes that support ends on Apr 12, 2016, which is just under a year away. While that might seem like a long time, it can take months to get an upgrade approved, planned, and executed, so if you think you’ll want to upgrade before support ends, you definitely should start planning.

    However, do you need to upgrade?

    That’s an interesting question. I would assume that if you have SQL Server 2005 in your environment, it’s been running since sometime before the end of 2009. It’s possible you installed 2005 after SQL Server 2008 was released, using the downgrade rights you have. I expect that you might have done that for a year, but I’d hope that if you were installing new instances after 2010, that you were using 2008 or 2008 R2.

    Given that assumption, I’m guessing that you’re relatively pleased with how SQL Server 2005 has been performing. At least I’d think you are. If you’re not, then why haven’t you upgraded? If it’s budget, then does support ending really make a difference? If whoever approves purchases has been satisfied with performance, why upgrade now? If your database backs a third party application, will you have to upgrade that code as well? If so, another reason it might not be worth making a change.

    We haven’t really seen patches for SQL Server 2005 in a long time. There have been two hotfixes and one security update since the last Cumulative Update, and the last one was in 2012. While I do think it’s possible that more security bugs might be found, the likelihood is low. Chances are that if you’ve been secure this long, you’ll still be secure for a few more years.

    If you are continuing to develop your code, however, there are lots of improvements in later versions of SQL Server that you might take advantage of. T-SQL has continued to grow and progress, with new features. Replication has improved, as have some of the manageability of the servers. Certainly security and encryption have advanced, so those could be reasons to consider moving to a more current version. The wealth of information available for tuning your system, as well as more efficient code and a better query optimizer might lead you to consider both a hardware and software upgrade.

    While there are some compelling reasons to upgrade with greatly improved performance and many more features that can help improve development, if you have systems that are working then is it really worth the licensing cost? Especially now that SQL Server is licensed by the core and not socket. Working is a feature, and one that I do think is worth considering as part of your decision.

    Steve Jones

     

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Searching for Plans

    Today’s editorial was originally published on Sept 13, 2010. It is being re-run as Steve is away at training.

    I was re-reading Conor Cunningham’s “Does Join Order Matter?” recently and found this quote in the post that stood out. As an aside, read this post. It’s fantastic.

    “…SQL Server’s QP is actually not set up to give you the best plan.  Instead, it is set up to give you a good enough plan quickly (where good enough is very close to “best”).”

    I get that, and it makes sense. Why spend 4 hours optimizing a query that runs in 2 minutes to get it down to 1:50 (Conor’s example) when the optimizer can spend 1 sec and have it execute in those two minutes. If the optimizer looked for the best plan, I’d be very angry as a customer. Especially if this happened with any frequency.

    Or would I?

    I’m not suggesting that we ought to optimize every query, or that I’d prefer the optimizer didn’t try to normalize queries, or that I want to see the internal tree and try to figure out if I can rewrite the query. I might, however, want to do that for some queries. There are queries that I might want to find the “best” plan for, either because they take a long time to run, or they are run often. I could then use that in something like a USE PLAN.

    Computers are cheap these days, as is disk. If I were to somehow able to copy my database onto an instance of SQL Server on a cheap PC, wouldn’t it be cool if I could set that optimizer loose on my query and let it try ALL possible plans? I’d be happy if it spent that 4 hours and gave me back an optimal plan that I could carry back over to my expensive, busy server, and shave that 10sec off a query that I run 10,000 times a day.

    I could see some value in a “test” instance of SQL Server that just looked for optimal plans. Now if I could just get Microsoft to build it.

    Steve Jones

  • Embrace Stored Procedures

    I’ve read a lot of pieces on stored procedures. Most of the articles were for using stored procedures, quite a few were against their use, and a handful with extreme opinions. This piece from Rob Conery, while perhaps NSFW, is one of the latest, and more entertaining, pieces I’ve read that discusses the value stored procedures bring to an application.

    For the most part I agree with the presentation, though not completely. It’s an interesting discussion about business logic v data logic, and while I do think that some logic isn’t really central to your business, there are ways you manipulate data that might be necessary in multiple parts of your business. Those manipulations might make more sense in the database as a stored procedure rather than trying to ensure every application implements (and updates) the logic in its own code.

    I do like Mr. Conery pointing out the fact that ORMs or custom applications might be extremely wasteful in implementing a process with multiple calls, and stored procedures handle these functions more efficiently. We have fast networks and powerful hardware, but those calls do add up, especially if your system gets popular and has lots of users.

    Ultimately I think the last sentence sums things up nicely: let’s wake up to the power of our relational systems. That’s something that developers should embrace. Why only use half the power of a tool you have? I do think the judgment of how much business logic to include in the database is worth debating, but not at an application by application basis. Think about whether each call, page, form, whatever makes sense as having the programming in the front end or the back end. Whatever you decide, you should be able to explain and justify your choice to the others you work with.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( MB) podcast or subscribe to the feed at iTunes and LibSyn.