Voice of the DBA

Category: Editorial

  • Interviews: Covering index

    Performance seems to be a part of every DBAs job. It should be a part of every developer’s job, but I understand the focus to build software is different and many developers aren’t sure how much a feature or option they work on will be used. They don’t think too much about performance because at small scales, perhaps performance doesn’t matter a lot these days with multiple cores and many GB of RAM. I’d disagree with that, but I understand the perspective.

    What’s interesting to me is that in many interviews I’ve had over the years, I’ve not often been quizzed on specific scenarios that might help improve performance for queries. It seems many interviewers like to ask trivia questions such as the difference between a clustered and non-clustered index or what configuration knobs should I change. Code questions might be how to find duplicates in a table or deal with NULLs in aggregates.

    I hope that’s changing and more interviewers are giving candidates scenarios, and looking for ways to ensure that candidates think about performance. I hope someone asks how a candidate looks at an execution plan or structures code to remove RBAR concerns. I wondered about this since I chatted with someone recently that had been asked about covering indexes in an interview and was struggling to understand how a covering index might be different than a clustered, nonclustered, filtered, or other type of index.

    This might seem like a trick question, and perhaps it is for some interviewers. After all, if you don’t know, you might correctly guess what this means, but will you know this isn’t a special type of index? Perhaps you’ll try to bluff your way through the answer and hope you get close.

    Answering interview questions should be a discussion, as your answer can reveal as much about how you work as it can about your knowledge. Do you probe to understand the boundaries of the question or confirm that you know what’s being asked? Do you admit when you don’t know something or perhaps reason out loud? Do you give details that make sense, or do you launch into a lecture?

    In this case, you should understand that this index covers a query. This isn’t a type of index, but rather a characteristic of an index against a particular query (or set of queries). To learn more, you might want to read a bit about adding included columns or how to evaluate your choice. If you don’t know much about indexing, get started with an introduction or go through our Stairway to Indexing.

    Future versions of SQL Server will include automatic index tuning, but I think we’ll always need to balance the number of indexes we have. We’ll also often want to build some indexes that will ensure the system works fairly well when it’s first installed. That means data professionals (DBAs ans developers) need to understand how indexes work and ensure they can choose a good index most of the time.

    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.

  • Are You a Traffic Cop?

    There’s a joke that a DBA’s favorite word is “no”. I think that was certainly true for the individual from whom I first learned about Oracle and SQL Server administration. He constantly pushed back on developers and management who requested changes. He wasn’t rude or abrasive, though I’ve met my share of those individuals. Instead, he patiently would explain and debate the changes and his reasons for not wanting to make them. He had come onboard after a particularly bad deployment affected a significant portion of the company and while management listened to him, development wasn’t pleased.

    One of the analogies I’ve seen for DBAs is that they are a traffic cop. They implement the rules necessary for the safety, protection, and availability of databases systems. This is sometime at odds with the pressure developers feel to implement new features and functions in their applications that require a database. This can, and often is, resolved by using some methodology like DevOps, where the DBA, the developers, and operations staff discuss changes in advance and collaborate to ensure that functionality will not negatively impact data quality, performance, or availability.

    However, this can easily take a negative turn. People often have short memories, and we are all usually busy at work. When a DBA lets a change go through that causes problems, they are reluctant to let the next one go without a thorough review. They slow down and want to double check every new line of code, or worse, they start to second guess their own decisions to allow changes, which can further exacerbate the issues.

    Are you a traffic cop as a DBA? I saw this referenced as a great analogy recently, where the DBA enforces rules, perhaps taking on a bit too much power, and using these rules to limit change. They lock down too much access, even preventing metrics and data from providing feedback to developers. They may even seek to strictly enforce security by the letter of the rule, not the spirit, upsetting and angering others.

    I think good security is important, and often bypassing security for the sake of convenience or expediency is a source of issues, including data breaches. There are policies that are outdated or may not apply in specific situations, and I’d say the sign of a good DBA is that they know when an exception is needed, they document and manage the process, and may even seek to have the guidance changed. They work with others to get things done, constantly looking to push work out to customers, while protecting systems. They may slow work slightly, but a good DBA works with everyone to minimize delays.

    If you’re a traffic cop, I’d argue your employment days are likely numbered. The world is changing, and while security grows more important, the ability to get work done securely and quickly, while working with others, will become more important over time.

    Steve Jones

    The Voice of the DBA Podcast

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

  • If only the US would follow …

    I saw this blog from Allan Hirt and I wish the US would adopt something strong like this. I’d actually like this to apply to all organizations, but certainly critical services need to be secure. If you follow the link, you’ll see that the UK government has warned their critical industries that if they do not have effective measures or safeguards against cyber attacks, they can be fined up to £17 million. That might not seem like a lot in some industries, but it should get some attention from executives. I’m not sure how many CxOs would keep their jobs if they incur that level of fine because they didn’t implement strong security measures.

    For now the requirements apply to the energy, transport, water, and health industries. These are deemed essential by the UK government. The UK government is expecting that along with data privacy changes to ensure GDPR compliance, that these industries need to implement better cyber security to prevent or limit attacks. This is part of guidance from the European Parliment, and it’s overdue. I just wish the US were as focused on pushing organizations to adopt security as a priority rather than an afterthought.

    Not that I want government to dictate specifics, but I do think that having a government authority that can stay up to date and evolve their view of what constitutes good security is a good idea. This could be similar to some sort of review and feedback situation that we have for auditing. Ultimately, I’d like there to be some group that can weigh in on good security practices for platforms and systems, probably with research and industry feedback, on what constitutes valid patch levels for systems and software. It would be valuable to know that your version of Windows or Debian or PHP or the database platform is insecure. Not that I want to create more of an upgrade treadmill, but using software means patching it.

    Perhaps this would drive more organizations to move to open source software, or perhaps more vendors to issue patches rapidly and lower their prices to compete. Maybe more importantly, it would press vendors of third party software to ensure they continue to develop security patches, perhaps even spelling out support lengths in contracts. The pressure to perform better would be useful in raising quality in the security area. One thing to note is that the intent isn’t to fine companies, but ask them to make valid risk assessments and take appropriate measures (with input from regulators).

    For now, I’d say that most UK organizations ought to start taking security more seriously. Making changes in platforms to prevent attacks and limit downtime will require some planning and foresight. You might not be in an industry affected today, but in two or three years that could change.

    Steve Jones

    The Voice of the DBA Podcast

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

  • A New SQL Server Edition

    I first heard about this edition of SQL Server over a year ago, and at the time I wasn’t sure there was much of a market for it. Microsoft disagreed, and they moved forward, putting this in preview and then finally releasing it this past week. Now you can order and set up an Azure SQL Database Managed Instance. According to Microsoft, this is an edition that is near 100% compatible with on-premises instances. The difference is this is a PaaS option that removes much of the administrative burden for patching, backup, HA, etc.

    This seems like a VM that Microsoft manages and you connect to, except this isn’t really a VM. You do not have any access to the host, but you do get the option to have multiple databases and most of the features that don’t exist in Azure SQL Database are here. Keep in mind that near 100% is not 100% and there are limitations, quite a few, which may limit your ability to lift and shift your particular application.

    Brent Ozar Unlimited tackled this feature first, putting together quite a few blog posts on the topic. The first one notes that a new instance might take 24 hours to deploy (in the cloud??), and theirs did. In that post, they also discuss the networking, which isn’t necessarily hard, but it’s not as simple as most DBAs and developers are used to. In fact, while reading the post, I recalled memories of my time as a network admin, learning how to migrate from IPX to IP based addressing. For those of you not used to networking terms and configuration, you might want to read about this topic from the experts.

    The Azure fabric is managing all the HA behind the scenes, and I’m guessing they’ve got some advanced featuers that might end up in the box product at some point. It seems from reading Brent and Erik’s experiments that you really get a dedicated instance with some CPUs and schedulers turned off it you don’t provision the largest instance. There are new objects inside the instance, which again, might get into the box at some point.

    Are Managed Instances for you? Perhaps. Removing this administrative issue might make financial sense for your organization. Not for you, but when you factor in taxes and staff to perform tedious actions, maybe this works. Certainly you should understand if you have an option here for any applications and then present this as an alternative to the business and let them decide if this makes sense. I’m not sold, but I do like the idea of this as a new cloud option. We have public, government, private, hybrid, IaaS, Paas, and SaaS options. This slips somewhere between hybrid and PaaS, giving us more choice, which I do think will work for some organizations.

    I don’t know how these will perform, and really none of us do outside of Microsoft. They’ve been testing, and I hope this will work well, failover cleanly, and not be so confusing that customers find themselves misconfiguring networking. However, if you’re using parts of Azure, have AAD implemented, and want to get new instances provisioned that look mostly like what you have in your data center, take a look. Hopefully the provisioning process will smooth out and speed up, since 24 hour notice might be great for some large corporations, but it certainly doesn’t feel like the cloud to me.

    Steve Jones