Voice of the DBA

Author: way0utwest

  • I learned about the order of logical operations #SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I had logic in a CS curriculum many years ago and I’ve worked with AND and OR statements for years. I’ve sometimes confused myself, but I usually ensure I have parenthesis included to clarify the code. Not just for me, but for anyone that might glance at the code later.

    As a side note, I also try to format code so a quick glance can reveal what happens.

    However, I learned something new this week. I saw a question about the order of logical operations in this form: a or b and c.

    I had somewhat assumed, like math, we’d use a left to right evaluation. However, that’s not correct. Look at this snippet:

    2018-03-22 10_06_22-SQLQuery1.sql - (local)_SQL2014.SimpleTalk_1_Development (PLATO_Steve (57))_ - M

    If we went left to write, we’d have two rows from the OR (n=1, n=2) and then an AND that produces no rows. So no results?

    That’s not correct. According to BOL for OR,  the AND operations occur first. So n=2 AND n > 3 occurs, with 0 rows. Then the OR with n=1 is evaluated to return 1 row.

    Fascinating.

    At least to me. I’ve never thought because I’d write

    WHERE (n = 1 OR n = 2) AND n > 3

    or

    WHERE n = 1 OR (n = 2 AND n > 3)

    and be sure that what I wanted to occur would occur.

    A quick lesson. While it’s good to know what the order or evaluation is for your platform, don’t count on this. If there is a chance for confusion or unintended consequences, use parenthesis. It’s simpler and easier, and I might argue, more elegant.

  • More SQL Server Updates

    This was a busy week for updates. I finished adding new builds to the build lists for 2017, 2016, and 2014, the first time I’ve done all three platforms on the same day. To be fair, two updates were on the 19th and one on the 20th, but that’s pretty close to the same day. In the past I’ve had updates for different versions during different months.

    That will not be the case in the future. SQL Server 2017 is on the CU model where we get monthly updates to the product. No more service packs for this version. If you hadn’t heard that, be ready for the new servicing model that consists of just the monthly updates. I wasn’t sure if I liked this at first, but I do appreciate that we don’t get confused with trying to determine which CU1 is needed as there were different patches for RTM, SP1, SP2, etc. Now I’ve gotten used to expecting an update each month and applying them after a few days. So far, Microsoft has mostly been sticking to monthly releases, though the December release got delayed into January. With holidays, that’s expected.

    This week also saw the update of SSMS to v 17.6. You can freely use this version and download the full install or just the update package if you are running 17.x. We are seeing updates on a fairly regular basis to the tool, every couple of months. I’m hoping we continue to see more fixes and improvements aside from new functionality as SSMS has been neglected for too long. I don’t know how stable this version is, but 17.4 was on my machines for a long time without an ill effects, and I think this seems fine for now.

    Updates have always been a part of software, but often they are fairly rare, with interruptions occurring just a few times a year. That’s changing. Redgate releases software every week, with almost every product updating once or twice a month. For someone that uses relatively few pieces of software, all of a sudden it seems like I have a regular set of tools to update across my SQL Server development platform between SQL Server, SSMS, VS, Redgate tools and more.

    That’s good, as I get fixes and increased functionality, but across a set of tools, I might find myself updating something every day. While the software often updates itself if I click OK, I have to make a decision and the process is disruptive and time consuming, especially when I run into required updates, which happens more than I’d like. I like the idea of DevOps, but one of the unintended consequences is that if everyone does it, there are a lot of changes and updates for users to deal with.

    I don’t have a solution, but I do find myself delaying updates more and more, often moving to only take every 3 or 4 updates. Maybe if everyone had an easy way to apply patches with PoSh across systems, or smoother built-in mechanisms I’d feel differently, but for now I probably click “skip” more often than “apply”.

    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.

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