Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #45–Follow the Yellow Brick Road

    tsqltuesdayIt’s T-SQL Tuesday time again, and this month Mickey Stuewe hosts the party. It’s an interesting topic, and one that I think is important, and will be more important, as we capture, store, analyze, and depend on more and more data.

    The topic this month is auditing, and you can read the invitation on Mickey’s blog. I think it’s amazing no one has asked us to write on this in the past, and I expect we’ll see some interesting stories that people will share.

    This is a monthly blog party and all you have to do to participate is write a post on the 2nd Tuesday of the month. The topic comes out about a week before, and you can schedule something to go out on the appropriate day. Be sure you leave a comment on the host blog or a pingback.

    Auditing

    I’ve never been bound by regulatory requirements for strict auditing. When I worked in industries that had bounds, they weren’t really updated for computer systems, and lots of our practices slipped by because no one thought to look. A few times that was scary.

    However I’ve implemented auditing in various systems, in various ways. The most common way to do this in the past was with triggers, though I had a situation where I had to build a unique solution that would capture changes being made to systems.

    I worked for a small company one time and we had a few critical systems our clients regularly accessed remotely for various functions. This was in the era of client server systems, and we delivered software to our clients that connected to our servers. We had lots of buggy software, and when we released new features, I’d cringe as I knew we would have calls and complaints for a few weeks as we patched things.

    I ran a team of 3 DBAs, and we had 5 or 6 developers working for another manager, including a few remote developers. We had agreed to limit changes to known times, and document the packages being deployed so that we were aware of changes and could better troubleshoot issues. Our environment was complex enough with multiple processes communicating with each other and SQL Server without adding the issues of unknown changes. We communicated things to our development staff, and though they grumbled everyone agreed.

    One day I came in to find that a process that had worked the day before had failed. We were struggling to get it working, and eventually discovered that a schema had been changed. This was in response to another enhancement, but it caused an import process to change. We hadn’t scheduled a change, which must have occurred overnight. No one admitted to making a change, and after a long day, I had to drop it.

    But not completely.

    I went back and set up a server side trace to run and capture information for object changes. I let this run constantly, rolling over the files periodically. It was a bit of a load on our system, but I felt it would be short term and it was important enough to understand what was happening.

    A few days went back and we were having an issue with another part of the system. Clients were screaming at my boss, who was coming to the development area to complain to DBAs and developers alike. As we tried to reproduce the problem on test systems, suddenly it started working.

    Relieved, but concerned, I decided to check my trace. Sure enough, I found a change to an object logged, by none other than that craft “sa”. Digging in further, we discovered that a piece of middleware had a timestamp of a few minutes before the system started working.

    It can be hard to track down who is using an “sa” account, which is why it is not recommended as an account to use by individuals. Cross referencing some information from the network team, we discovered our remote developers were slinging code changes on the system whenever they felt like it, using sa and “Administration” on Windows.

    They patched systems at times, but often these were patches to fix issues in poorly written software they’d deployed in the first place. They were also patching patches when thing didn’t work. Whether this was in reducing the time of problems or not was difficult to determine, but it certainly meant a lot of wasted time when we tried to troubleshoot issues with no idea what had changed. We also found lots of their changes weren’t being added to version control.

    My boss agreed this was an issue, and it allowed me to change all system administrator passwords, as well as revoke all rights to production systems from developers. Eventually we achieved more stability in our systems than the company had ever found. I’d attribute this to preventing cowboy coding changes to systems, which seem to cause as many issues as they fix.

    Auditing is something I’ve depended on, especially for myself. As I’ve worked with more and more systems concurrently, I find that it’s easy to forget about changes made in a hurry, or forget which change occurred on which systems. Logging and auditing allows me to retrace my own steps, as well as those of others.

  • T-SQL Tuesday #44–Second Chances

    tsqltuesdayIt’s T-SQL Tuesday time again. This is the monthly blog party, started by Adam Machanic. If you’d like to participate, write a post on the topic and publish it on the second Tuesday of the month. If you’d like to host, contact Adam.

    This month the topic is hosted by Bradley Ball and his subject is Second Chances.

    Second Chances

    We all make mistakes. I ask the question of people in some of my talks, and note that I make mistakes all the time. I do, and while most of them are fairly small and easily recovered from, some aren’t. Some are easy to brush off, and some are very, very embarrassing.

    In my career, when I think about the big mistakes I’ve made, I’ve got a few choices. There have been times I’ve deleted all the data in a table.

    secondchance

    There have been times I’ve restarted a SQL Server without telling anyone. There have been “fixes” to the system I’ve made without running through the proper channels. However there is one item that stands out for a few reasons.

    SQL Slammer

    I’d let patching slip. We had a large SQL Server environment (hundreds of Standard/Enterprise versions, thousands of MSDE versions) and while we’d been trying to stay patched, it was a hassle and we let some things slip in the fall of 2002.

    I’d gone to the mountains for the weekend to ski with my family. We returned late on a Sunday night to find numerous voice mail messages at home and on my cell phone. I was actually called again while I was checking messages on the way home from a friend at work that said I needed to come in. After dropping off my wife and kids, I headed to the office.

    Our analysis of the worm showed that it was wrecking havoc on our network. It constantly bottlenecked the network, and we had shut everything down. For a 5,000 person company, with a central network presence and hundred of software developers, this was not ideal. We were up most of the night, waiting on someone from Microsoft to fly in and help us rebuild the patch that Microsoft had released. Since we’d installed many instances of MSDE in non-standard locations, the patches wouldn’t work.

    Our network was down Mon and Tues, but we learned some valuable lessons, like email and file shares weren’t so critical to the business that we couldn’t function for a day. We also learned that allowing developers to ignore patches was a bad idea as the majority of the delays after Mon afternoon were due to MSDE instances.

    My lesson? Myself and the other DBA received a stern talking to from our boss about patching. We had delayed some of the patches for a few months, but there wasn’t a good excuse for getting almost six months behind. Microsoft had been sending lots of patches, but we didn’t have a good reason for not getting them installed quarterly.

    I still don’t like the every other month patches that MS releases as cumulative updates, and I don’t recommend them, but I have learned the security updates are worth getting installed ASAP.

  • T-SQL Tuesday #43–Hello, Operator?

    tsqltuesdayIt’s T-SQL Tuesday time, and this one is a bit of a challenge for me. Rob Farley is hosting, and asking for people to write on Plan Operators, those various elements that go into the execution plans in T-SQL. I don’t dive too deeply into these items, unlike some others, so I’m going to tackle this at a high level with something I learned a few years back, but a few years after I started working with execution plans.

    T-SQL Tuesday is the monthly blog party from Adam Machanic. It’s the second Tuesday of each month and if you want to participate, watch for the announcement on Twitter, or Google search it and then publish a post on the specified day, GMT time. If you want to host, you need a blog, need to participate, and then contact Adam.

    Parallel Operators

    Many of us know that execution plans can be serial or parallel. Which plan we want depends on the situation, but you can determine which plan is being executed from the plan. However I didn’t know the depth to which I could determine the parallel plans from the images.

    When I first looked at the Graphical Execution Plan Operator Icon page, I saw a lot of operators, many of which I’ve seen in the past. There’s a section for parallel operators, and if you look at it, you only see three items: the Distribute Streams, Repartition Streams, and Gather Streams icons. I’ve shown that section below.

    parallel

    These operators work to combine to separate out the data into separate parallel operators. If you see these items in your plan, you know there are some parallel operations.

    However, do you know which operations are parallel?

    If you look at the nonclustered index seek operator, it looks like this:

    nci

    The description doesn’t say anything about this being serial or parallel. This icon is serial.

    A parallel icon looks like the one shown in the image below (from Paul White’s Forcing a Parallel Query Execution Plan). It’s the lower right icon in the image.

    parallel2

    Note that the icon has two arrows overlaid on it. In fact, this is a parallel plan that has a parallel clustered index scan, a parallel nonclustered index scan, two repartition streams (parallelism operators), a parallel merge join (top row, second from the left) and a gather streams (parallelism operator). Lots of parallel work being done.

    If you look at an execution plan, you can tell which items are parallel with those two arrows overlaid on the icon.

  • T-SQL Tuesday #42–The Long and Winding Road

    tsqltuesdayIt’s T-SQL Tuesday time, this month hosted by Wendy Pastrick who asks you to talk about the Long and Winding Road. Wendy asks us to talk about your job, your road map to the future, and what might be in store for you.

    T-SQL Tuesday is a monthly blog party on the second Tuesday of each month, hosted by a different person. You can participate by writing a blog post on the topic for the month and following a few rules that you’ll find in Wendy’s invitation.

    If you want to host yourself one month, contact Adam Machanic (@AdamMachanic).

    The Long and Winding Road

    I’ve been in the same job for about 11 years: the editor of SQLServerCentral as my primary role. About 6 years ago, Red Gate Software bought the site, and I now work for them. The job is mostly the same, though a couple things have changed over the years. Three years ago I made an effort to begin speaking more, both on technical topics as well as Red Gate software. I’ve had a busy speaking schedule across that time, and I’m scheduled for about 15 events in 2013.

    That change in speaking for me, along with the SQL in the City events that Red Gate sponsors keeps me busy with building presentations and delivering them all over the world.

    These events are designed to provide attendees with free training just like other conferences with one exception: we can talk about Red Gate tools. The sessions I do aren’t marketing talks about the tools, but rather presentations about things you should do in your environment, but I do show how the tools Red Gate provides makes these tasks easier. That’s the idea behind our tools: they make you more productive.

    This year that has meant I’m moving a little bit away from the administration areas where I spent most of my career and back into some development work. I’ve been setting up Git and working with source control, learning about Continuous Integration, and more. Mostly this is to support the work Red Gate is doing on Deployment Manager, which I think is a great idea. It solves a lot of problems and hassles that I’ve dealt with when developing software. I’m excited, and looking forward to seeing how people like the tool, as well as watching it evolve based on feedback.

    I have no plans to leave my job or Red Gate in the foreseeable future. The position I’m in is unique, and I don’t expect to move up, or around, in the company because of that, but that’s OK.

    I have the best job in the world.