Voice of the DBA

Category: Editorial

  • SQL Server Tuning Skills

    Performance tuning a SQL Server instance, database, or even query is a skill that most of us could improve. In fact, I see that the query performance presentations are often the most popular learning items at any event. I’ve seen quite a few people asking for more tuning talks in different areas, which is a good thing. I think most SQL Saturdays could schedule a different tuning session every hour, or even repeat the same one a few times, and the room would be mostly full each time.

    Learning to look at the various possible problems in a SQL Server is both a science and an art. There are good methods, such as waits and queues, for examining where an issue occurs. There are basic techniques for looking at execution plans and then making changes to indexes or T-SQL. There are different options for queries, SET values, and more that will affect your query or even all your queries.

    Sometimes it seems there might be too many options for a beginner to decide how to begin examining a system, especially one that is having issues. I can’t cover all of the issues in this short piece, but I can give you some guidance that might help. First, make sure that you know how to gather information about your issue. We have forums at SQLServerCentral to answer your questions, but you have to do some work. Gail Shaw wrote a great piece on how to post performance problems.

    Ultimately, you need to understand the recommendations that we may give. It’s helpful to learn about indexes, and the differences in the various types (including Columnstore). You should also have a baseline so that you can measure the impact of your changes. How do you know the problem is fixed? Maybe the user gets lucky with a quicker experience when you tell them you’ve changed the system, but will they call again soon? That second call might be worse than the first one. Even getting a baseline of the poor performance now will help you understand (and prove) that the system is running better.

    You also need to understand what not to tune, and which items to ignore. I thought quite a bit about this after reading Paul Randal’s post on wait types to ignore. This is part of his 101 series, which are a good way to start improving your skills. Performance tuning takes practice and knowledge, which takes time to acquire. Even if you’re fighting a problem today, try and learn a little something about how you get your system to run better.

    I’d also recommend that you look to continue your education over time. Take advantage of those sessions at local events, like user groups, virtual groups, and SQL Saturdays. Take advantage of pre-cons at larger events, which pack lots of learning into a few hours. If you go, take a paper pad, not a laptop, and take lots of notes. If you can’t get away, maybe you can lobby for some tuning classes from sqlSkills, Brent Ozar Unlimited, or even self-paced courses on Pluralsight.

    Learning to become better at tuning is an ongoing process, and one that requires some investment. Perhaps some money, but definitely some time and practice are necessary. Don’t let this be a daunting task, but one that’s a journey. Your goal is to get a little better each time you work on this skill, not to be an expert that knows it all. I’m not sure anyone gets to that point, but you can become as good at this skill as any of the presenters you watch. It might take you longer than it took them, but you’re also able to build on their work and learn from their knowledge. Take advantage of that opportunity.

    Steve Jones

     

  • The Journey to DevOps

    Netflix is one of those companies that I find amazing. I was an early subscriber to their mail DVD service, and thought it was amazing how they processed both orders and physical objects with technology. As they pivoted to streaming, I continued to be impressed with their technology growth, from the chaos monkey to their DevOps deployments. They have been an organization often looked to as a model for other technology companies.

    I certainly think there are things to learn from Netflix, as they’ve scaled and build quite a resilient system. They aren’t necessarily worth copying, however, as the problem domain they solve is both narrow and also quite different than that many of us work in. If someone can’t watch a movie, it’s annoying, but they can pick another one. If a customer can’t transfer money, communicate with another user in an app, or schedule a ride, it’s a bigger deal for other problem domains.

    Still, Netflix didn’t build this system overnight. They didn’t come up with amazing DevOps techniques for building and deploying their software from the beginning. It’s been a journey, and they talk about some of the full cycle developer challenges in a recent blog post. This looks at one team’s journey across 6 years, from 2012 to this year. The piece is an interesting read, and it’s not advocating for their particular approach, but rather trying to explain the value that they received from moving to a DevOps model, where they have a group that must run what they build.

    As I try to help customers and clients move to Database DevOps models, I see lots of similarities to what’s in this post. As we look to optimize the entire software development life cycle, this requires a changing of roles and closer cooperation between groups. As I look at the evolution of Netflix, using a centralized group to build tools and then ensuring you have a better staffed development team that works to both build and support their software, ensuring clients get the value (or features) they need quickly.

    I’ve worked in orgs that did this in groups, and for the developers to be involved in operations is an eye opening experience. Developers will learn ensure they think about their design and test more because they don’t like getting woken up. They listen Operations and learn about the ways in which they can better build a system that works. They start to realize “works” is a feature, perhaps the most important one.

    I’ve also found my role as a DBA can facilitate DevOps. I’m often between development and Operations, with a foot in both camps. I help developers build better tools and techniques to work with databases. I understand the impact on production databases, meaning I can help ensure that deployments are smoother, and we avoid risky changes. We build indexes early, install primary keys, test queries for performance, and more. The DBA is the one of the ways in which DevOps can grow in an organization, if they work with the developers instead of against them.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Will Coding Be Less Important?

    How many of you can read Greek? I can decipher the alphabet and pronounce words, but that’s about it. I thought about this after running across an essay that says that coding will eventually be as important to most people as ancient Greek, which is to say, hardly important at all. The author seems to suggest that eventually we’ll tell systems what we want and they’ll build algorithms. I guess that’s what we do in code, but the impression I have from the piece is that we’ll use natural language to inform some AI system that will actually produce the code.

    Possibly, but let’s set that aside for a minute. Assume that we could have an AI that removed the need for us to code the corporate CRUD systems or most of the websites we have. Could such a system be adapted to manage data?

    Possibly, and maybe more consistently than we can. Certainly plugging in RTO/RPO values and HA needs might allow an AI like system to schedule backups, build up or tear down new nodes in a cluster or Availability Group, and certainly respond to DBCC errors, reindex systems, and more. I certainly could see that we could request a system to do X and lots of the background details would be handled.

    There are some other thoughts about this piece that I tend to agree with. The ability to easily specify the way in which a system can work, for the average or even above average user, isn’t coming soon, if ever. The thing is, I’ll take solace in something that I truly believe.

    Writing the code isn’t the hard part.

    Sure, syntax can be challenging, and working out the mechanics of how modules interact, and building MAKE-like scripts, and deploying changes are difficult. We forget about the differences in environments and configuration, and I bet better static analysis and AI might help here. The thing that’s really hard, that is likely to still exist even if we specify the way something works is that it’s really, really hard to be specific.

    We struggle to express the exact way that systems should work. We struggle sometimes to communicate what “a discount for more than 10 pieces” means. Is that a discount at 10? At 11? what if there are 20 ordered and 10 returned? Do we remove the discount or pro-rate the return? We can solve the issue, but so often, we don’t know there’s an issue until the system gets into production. Changes in rules/regulations? We don’t often know about them, much less know how to express those changes in plain language.

    Even if we could get by without writing code, the expression of “how” something works likely requires a lot of detail, a meticulous person, and endless patience to get the machine to understand. Something many developers do now. I suspect the same thing would be necessary for database development. Even if we could specify the result set, would we really trust the machine to query the source and not come up with multiple ways to get this result set, which might not be valid for another?

    Call my cynical, but coding isn’t going away, even if the syntax changes to English (or some other spoken tongue).

    Steve Jones

    The Voice of the DBA Podcast

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

  • How Far Back?

    At the Redgate SQL Privacy Summit recently, there was panel I hosted that I thought was really interesting. At one point someone asked a question and there was a bit of a discussion about the right to forget in the EU. One of the fascinating things wasn’t about the rights, but about backups. One of our panelists noted that we often keep too many backups, most of which are really useless.

    This portion of the discussion started by asking how many backups people keep. Many of those in the audience kept many backups, sometimes dozens. Many of them had some rotation scheme that determined how many files were kept over time. I know I’ve worked in places where we kept over a week or two worth of backup files, often along with a monthly or quarterly backup for a year and then multiple year end backups in perpetuity. I guess since we didn’t really know how long those backups were valid as we just marked some tape as the end-of-year one and left it in a vault somewhere.

    What was also interesting is the panelist asked if many people had restored a backup more than a month old. Almost no one had, but a few had restored a year old backup. I think that’s common, and I find I’ve only restored one backup more than a month old. That one was actually for a legal case, and it was six or seven years old. I actually had to find someone at Microsoft that could provide us with an old version of an SP to restore the database.

    I’m wondering today about the rest of you. Not if you have done it, but would you do it today? How far back would you go for a backup of an important database? Assume this isn’t any requirement to find the data at a point in time, but for recovery purposes in a disaster.

    For me, I’d expect that most of the systems I’ve managed really would have struggled to go back a week. After that point in time, so many transactions are missing that there are serious repercussions. This is why I’ve often ensured I had automated restores happening all the time, since I need to be sure that we can restore from yesterday or two days ago. If I went back more than a week, it would be because we had corrupt backups. While the business would probably survive, I’m not sure my job would. If I hadn’t been able to produce a workable backup for the last week, I think most organizations would be justified in terminating me.

    Whether they would or not is another debate.

    Steve Jones

    The Voice of the DBA Podcast

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