Voice of the DBA

Tag: Database Weekly

  • Performance Tuning Through Panic

    We’ve all been there at one time or another: panicked by a performance issue. I once upgraded a FoxPro for DOS application to a VFP front end with a SQL Server back end. We had a much more powerful server, generations ahead of the previous file server share. When the first users logged in and searches ran slower, I was in a panic. My team worried about the size of our inventory data in memory and quickly upgraded our 4GB, 1996-era server to 8GB of RAM, hoping for improvement. We also hurriedly split out queries and separated some data in an effort at quick tuning. It wasn’t much above a knee-jerk reaction, but we had users, and management, yelling at us on an hourly basis.

    I’m sure many of you have had other performance problems that resulted in some panic during your career. I’ve had more since then, and will probably have more in the future. Many of us do, and many of us perform knee-jerk performance tuning. However you can, and should, avoid tuning in a panic, as pointed out by Paul Randal. Doing so usually doesn’t fix the problem, and when it reoccurs, it’s more embarrassing with people less likely to listen to your next great idea.

    Tuning a system is a science, but it’s a complex science. Without experience, without having knowledge about all the complex interactions, and a deep understanding of how all the parts fit together, it often looks like a hopeless task. Who can keep all that information in their head and relate all the various components of the application? The network, the OS, the SAN, clients, all can cause problems that appear to be in the database.  As Paul points out, failing to understand the actual root cause can lead you to make changes that will have no effect on the actual underlying issue. You’ll often realize this when the problem re-occurs in a short time.

    I think one of the best reason to hire consultants is that they see so many problems that they can recognize patterns in performance that many of us don’t see. What they do appears to be an art because they can seemingly understand what information is relevant, and what isn’t. They walk down a list of potential issues, gathering evidence in a logical way, but one that can appear random to someone with much less knowledge and experience.

    All of us can learn more, we can read about problems others have had and learn how they solved the issue. However we often face the same reaction that many consultants do: no one wants to implement the advice. Until clients and vendor are willing to tune their code, many problems will only be masked by hardware.

    For a time. Then they’ll reappear.

    Steve Jones

     

  • The Ultimate Tool List

    I’ve seen Scott Hanselman’s Ultimate Tool List a few times and adopted some of the tools in the past. He updated it for 2014 and has added quite a few new tools. I downloaded F.lux the other day and am giving it a test run right now. I’m not sure how well it works, but I like the idea of this little utility. I also used Chocolatey for my new machine after Troy Hunt blogged about the utility and thought it was amazing. AutoHotKey is the new items I’m testing.

    We are in the business of technology and using computers to work more efficiently. We manage, build, and deploy software that allows our organizations to get more work done with less resources than ever in the past. We have, or should have, utilities that automatically check our math, our spelling, that communicate quicker than in the past.

    Those of us working with computers should be using utilities, tools, and helpful software all the time. We should test it, share our thoughts and results, and help others to learn to let computers improve their lives. While there are places computers aren’t needed or aren’t appropriate, there are many more ways in which computers can help us save time and effort by tackling tasks, even simple ones, with automation.

    I’d encourage you to look at some of the tools on Scott’s list and see if they might help you work more efficiently in the new year. With the slower times that usually occur during the holidays, this is a good time to experiment with some utilities that might help you in the new year. There’s also a SQL Performance Tool collection from MSDN in this week’s newsletter.

  • Regrets of the Last Decade

    Microsoft was never an exciting company to me as a kid. I grew up excited by Commodore with their GEOS, early Atari computers and consoles, and the original Macintosh computer. I was fascinated by Sun and Silicon Graphics workstations, running X Windows across multiple machines. The NeXT workstations made early Windows machines seem primitive. When I first worked with SQL Server, it felt like a half-finished product that wasn’t stable on OS/2. Microsoft felt like a pedestrian company that was good for my career, and was trying to grow, but they weren’t exciting.

    Some of that changed in the late 90s as Microsoft embraced the Internet and helped it grow with the ActiveX system. For better or worse, ActiveX seemed to bring life to web sites in a way that Java and other techniques couldn’t. We could argue about the damage that systems like ActiveX and Visual Basic 6 did in terms of application performance and maintainability, but without a doubt these tools caused an explosion of development and no end of work for those of us in technology.

    Developers, developers, developers was exciting and was a great mantra. I believed that Microsoft had really hit upon the key when I heard that. Woo developers, help them build applications, and let the excitement grow. I watched SQL Server mature with it’s expansion into BI areas. I saw Microsoft create the XBOX platform, and build a much better Office Suite than I’d had with Lotus, AmiPro, and others. Microsoft had a time during my career when I thought they were really going to dominate the world of computing.

    However I wasn’t thrilled when Steve Ballmer took over. He wasn’t a geek, and wasn’t one of us. Slowly I felt that the company lost its way. Mr. Ballmer talks about some of his regrets in a piece this week, one of which was Longhown. That project dragged on for years and become the debacle that was Windows Vista. During this same time, SQL Server dragged, taking 5 years to get SQL Server 2005 released. Office seemed to stagnate, offering little reason to upgrade, other than because others were upgrading. The company felt lost, and paled in comparison to the excitement generated by Google and then Apple during the last 12 years.

    As I read the quotes and thoughts in the piece, I have to admit that Microsoft really has quietly advanced. They have had successes and lots of growth, even if the stock price hasn’t skyrocketed. They’ve invested in platforms, research, and technology, internally and through acquisitions, that may help the company maintain its position as a technology leader for some time. For every misstep like aQuantive, Danger and Ray Ozzie, there are advances like Azure and Dynamics. Windows has continued to grow inside enterprises and I rarely see the complaints over scale and capabilities that I remember from early in my career.

    Were the Steve Ballmer years a success or failure for Microsoft? I think I’d call it a maintenance time. Like the manager he is, I think Mr. Ballmer managed the company, without ruining it, but without creating much excitement either.

    Steve Jones

  • Achieve Better Performance

    One of the hottest topics at most every SQL Server event I attend is performance. The sessions that talk about improving performance through various means are well attended, and that tells me a few things. One is this is an important topic, and two, people need to learn more about it.

    This past week I noticed a lot of performance related topics appearing in blogs and headlines. I’m not sure why there were so many this week, but I think it does deserve pointing out that this is a good week to beef up your skills in this area. I saw a little bit of something for everyone, no matter what your job.

    Database administrators, who often deal with the end product from developers and can make limited changes, should make sure they understand statistics, which can clue them into problems in a number of situations. You ought to be able to troubleshoot various issues, like blocking or tempdb issues. For those dealing with merge replication issues, you ought to ensure you understand how to deal with conflicts and resolve them. No matter who you are, you need to understand the impact of IO on your server, and that means ensuring you can measure the load. Measuring CPU can be tricky, but there’s an interesting idea from the SQL Server team this week.

    The developers, who can make changes early in the process of software development, really should learn to write better SQL. That means studying the various ways people solve problems and practicing using some of them. Do you know the issues with Scalar UDFs? Do you understand temporary objects? Can you PIVOT data? There are so many writings on T-SQL that you can’t keep up, but you should be getting better over time if you write T-SQL code in your job.Developers also should understand the basics of indexing, and deliver a few basic indexes along with their software. That’s the least you can do.

    This week we started to see various blogs on SQL Server 2014, especially the Hekaton feature. While I’m sure the next version of SQL Server will perform better and speed up some of your workloads, it can’t cover from fundamental mistakes, at least not completely. Do your career, and your current and future employers a favor and work on your performance skills this week.