Voice of the DBA

Category: Editorial

  • Faster Versions and More Support

    When I started working with SQL Server, the versions came a strange paces. Three years between v4.2 and v6.0, but less than 1 before v6.5. Then two years to v7, one more to SQL Server 2000, but five until SQL Server 2005. After SQL Server 2008, we’ve moved to a 2-3 year cycle, but I can see that accelerating a bit more as SQL Server vNext looks like it might come in 2017, a year after SQL Server 2016. Since much of the code is released to Azure first and exercised there, I could see us getting on premise releases coming every 1-2 years in the future.
    Overall, I like this pace. We can see changes coming to the product on a regular basis and enhancements that I can choose to implement or not. We no longer have to wait years to get an enhancement or evolution of a feature. We always have the choice to upgrade. Many of us won’t, but that’s OK. Perhaps we’ll use new features in new applications, and learn how they might make the case to, or not to, upgrade existing instances.
    There’s a flip side to the rapid releases and enhancements to the platform. Many of our organizations expect a database server to last for years. We invest plenty of resources in building these database servers, and I think many managers and organizations expect they will be in service for at least five, and maybe ten, years or more before they are decommissioned or perhaps upgraded. I know there are still plenty of SQL Server 2005 and 2008 instances (probably not 2000) that are being used on a daily basis with no plans to replace them.
    This means that as the developers and DBAs that work with SQL Server, we will likely be supporting a wide range of versions in the future than ever before. Even if you started a company two years ago with a SQL Server 2014 instance, I’d guess you might have a few more now, perhaps some SQL Server 2016 instances. In two years you might have 2014, 2016, and 2017 (assuming vNext becomes SQL Server 2017). In five years, perhaps you’ll have five versions to support. I know there are people in that situation today with 2014/2012/2008R2/2008/2005 instances in production.
    There was an announcement this week from Microsoft. They are now offering SQL Server Premium Assurance, which will give you 6 years of support after the 10 you ca now get with Extended support. Certainly there are restrictions and costs, but I expect that there will be some large organization that think it’s worth paying this to continue to use older versions as long as possible. After all, this is a platform, which implies some level of stability.
    That doesn’t solve the knowledge issue and need to keep staff around that understand the platform, but with all the education and knowledge available from places like SQLServerCentral, perhaps supporting multiple versions isn’t a bit deal.
    Steve Jones

  • 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.