Voice of the DBA

Category: Editorial

  • The One Metric to Rule Them All

    When we want to know if our databases are running well, most of us look at metrics for our servers. Usually some combination of CPU, RAM, Disk IO are chosen, though network, user connections, batches and transactions can be a part of tracking performance. I would guess if I asked any of you that are responsible for performance whatmight be a good measurement for your most important database server, you’d have some idea of what CPU, RAM, or something else should be running at. I know I can easily check on SQLServerCentral.com at monitor.red-gate.com and usually expect to see CPU in the 20-25% range. If it’s substantially higher, I know something is going on.

    I was listening to one of the DevOps Cafe podcasts recently where the subject was monitoring your infrastructure and how well it was performing. and one of the hosts mentioned listening to Werner Vogels from Amazon years ago. Apparently despite all the technical measurements that were available for Amazon’s IT infrastructure, the one measure that they most often used to track how well everything was working was the order rate. Given their size and experience, Amazon could look at order rate and decide if their systems were performing well, both hardware and software.

    Most of us might not be as tightly focused on our business, but I’m wondering if any of you use business based metrics when examining the performance of your hardware and software. Is there a metric for your business that might help you determine if your hardware is actually performing as expected? Or if your code does what the business needs in an application?

    At SQLServerCentral, we have various ways of analyzing our systems, but we have added some business based metrics. One of those is forum posts/hour. We should see a similar curve throughout the week that shows how many posts we’re getting. Across years of data, we have a fairly steady rate of posts that occur on a regular basis. If we’re not getting a steady set of posts, we know something might be wrong.

    Perhaps you don’t have any business based metrics that you use, but are there any that might make sense for your industry? Let us know today. And if you think that there isn’t a good way to measure your hardware and software with business metrics, I’d be interested in your explanation as well.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Testing Coders

    Interviewing someone is hard. In fact, I’m not sure many of us really know how to do it well. Looking at the past hires in most companies I’ve known, I find that all too often the success of hires in developing software is a bit of a hit and miss effort. Some hires do well, and some don’t quite perform as expected. Why is that? Wouldn’t you expect that we could adequately test someone’s skills as a developer? After all, we know what kinds of code we expect people to write. Shouldn’t there be some sort of test that you could have someone complete, even across a few hours, that would allow them to show what they can produce?

    I ran across an interesting post from Ore Eini that looks at a way of interviewing people by asking them to improve code. Rather than a take home test, or having someone develop code from scratch, Ore gives them a file and some code, then asks them to make it faster. The interviewee has around an hour (mentioned in the comments), but this is a test of whether or not someone understands how to read and write code well.

    Perhaps there’s a good way to do this in the SQL world as well. Can we take a loop or a complex join and have a user rewrite a query to be more efficient? Some of the changes in T-SQL in recent versions (especially 2012) can dramatically change the way you write code. Perhaps a candidate should be tested to see if they actually know how to avoid Grant’s seven sins? I bet more than a few people would want to know if candidates would remove, or at least question, the use of NOLOCK.

    There are many reasons why a candidate might interview well and then not perform as expected on a day to day basis. Life changes, we have outside distractions that might affect us at work. During the workday, we may struggle to get along with co-workers. Our managers might not bring out the best, or even the good, in us. Perhaps we are asked to perform tasks that weren’t covered in an interview and are outside our area of expertise. Perhaps we just don’t try as hard after we’ve achieved our goal of getting he job.

    There isn’t going to be any magic, guaranteed way of ensuring we hire people that will always perform up to their abilities. That doesn’t mean we should give up. I would really like to see us continue to try new techniques, share ideas, and most of all, continue to inspire and motivate others to learn more about their craft and constantly improve their skills.

    By the way, if you’re interested in the code side of things, Ore discusses some basic improvements and then more efficiency changes. One interesting thing, moving away from Linq dramatically lowered the memory allocations and working set size. Is this a big deal? It really depends on the way in which your application is structured, but this is an optimization that might be worth doing early and often with a little developer training on how to better write queries.

    Steve Jones

    The Voice of the DBA Podcast

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

  • First Class Jobs

    I was looking through the forums the other day and noticed that someone wanted to track changes to the Agent jobs on their instance. However, they couldn’t find a DDL trigger to capture the changes to a job. That’s because a job is really data in a table, in dbo.sysjobs, dbo.sysjobsteps, etc. in msdb. To capture changes to a particular job, one would need a DML trigger that captures the insert/update/delete actions on these tables. You could also use XE, Audit, or some other feature that can track data changes in the msdb tables.

    I’m not sure that makes sense to me. This week I’m wondering if any of you feel that SQL Agent jobs aren’t well integrated into SQL Server.

    Do you think that jobs should be a first class database citizen and managed with CREATE JOB, ALTER JOB, etc. DDL commands?

    I think so. In fact, this would be the first step (in my opinion), towards getting jobs as a more useful database work process. Let’s have a job system that handles automated mechanisms inside the database as actual objects we can manage, set permissions on, and control just as we do other objects. Let’s treat these objects for what they are, actual items in SQL Server that should be objects.

    Could this be implemented? Sure. In fact, I think this might be one of the best ways to start containing jobs inside of a database. Make them a first class object inside a database. We can use msdb as the container for instance wide jobs. Then any sort of scheduler, including SQL Agent, could use an API to pick up the code from within the database and execute it however they determine is best.

    I don’t think this is simple or easy, but it’s possible. It would take some engineering effort from Microsoft, but I’m confident they could build a great ssytem. Making a job a first class citizen could help us control and simplify everything to security to transfer of the work from instance to instance. When the database moves, so does all the work associated with it.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Learn to Earn

    Could you double or triple your salary by learning more technology skills? Sure, and here’s a great story about someone learning PowerShell and dramatically increasing their earnings. This is a good read, looking at the journey of someone through their career and how they credit learning PowerShell for the changes in salary.

    When I read this, I don’t view this as PowerShell being the reason that Mr. Duffney increased his salary. Instead, I think the takeaway is that professional learning and regular skill development, focus across time, and producing results for an employer as being the primary reasons for raises. I think that the right part of the salary curve in the post, which has a steeper slope, is also likely due as much to speaking and giving back as it is to solving problems. The other lesson in this piece is that Mr. Duffney is constantly gaining skills, and looking to move forward in his career.

    The world of technology is ever changing, with new platforms, additional features and changing paradigms constantly appearing. I’m sure many of you have experience with management that becomes excited by the latest buzzword or hot topic in the media, expecting that each of us can quickly build a proof of concept. Perhaps you’ve dealt with a boss that assumes we could buy a product or build a tool that easily solves some problem because they read about some other company in a publication having success.

    Tackling a new project or technology is a challenge, but we can learn to ease the way for ourselves. We should be constantly learning something, anything that exercises our minds. One of the best talks on the topic I’ve seen is from Andy Warren, and I’d encourage you to check it out. Andy talks about directing your learning in an area that can help you. That may sound daunting, but I think that building the habit of regularly learning something is important in this business. We never know where our career may take us, and being accustomed to the idea of picking up some new technology and using it for a task is a skill you should practice and develop. The more often you try to improve your skills, the more comfortable you will be with the idea of tackling some new technology. In the first piece linked, we see that Mr. Duffney had a plan to become a CCNA, but over time that plan morphed into something else, as did the focus of his learning.

    I certainly have been able to raise my salary by learning more about databases. This usually comes about not just from learning, but from applying that learning to my job, showing my employer (and potential future employers) that I provide lots of value for my salary. Using new skills in a valuable way is the method by which I’ve most often been able to translate learning into a raise.

    Steve Jones

    The Voice of the DBA Podcast

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