Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #54–Interviews and Hiring

    TSQL2sDay150x150It’s the May T-SQL Tuesday, and this time it’s a great one. This month the host is Boris Hristov (@BorisHristov) and his topic is one that’s near and dear to me. He’s asking about interviews and hiring, and I have a lot to say on this topic. I could talk about both sides of the table here, and I’ll have to choose one for today, but I’ll give a few more stories over at The Modern Resume.

    This is the monthly blog party in the SQL Server community. The second Tuesday of the month is when you need to publish a post, on GMT time, and then link to the invitation. I’d encourage you to participate.

    I Don’t Know

    I’ve interviewed lots of times in my career. I’m not sure why, because I’ve often gotten great performance reviews, and I think I’ve been well liked at my jobs. Perhaps it’s my own restlessness, but it seems that I’ve often found myself in some situations where I needed to move on for some reason. Quite a few of the times it wasn’t my fauly, but However I’m picky, and so I’ve usually interviewed with lots of companies before deciding to take a job.

    A few years back I was unhappy with my current employer (they’d been acquired) and set up 5 or 6 interviews across a couple months. I often get responses from resumes I submit, and usually secure an interview after a phone screen. A lot of my writing at The Modern Resume is based on my success in getting interviews and job offers.

    The interview I got was at Raytheon Polar Services, the company that manages the infrastructure for the South Pole research station at McMurdo. A developer friend I’d worked with at a previous job had spent a couple years there, including a summer at the South Pole and he helped me get the interview.

    oval-conference-table-wood-southwest-airlines-2

    I walked into a room like the one above. My friend and his boss shook hands with me and we sat down. About 9 or 10 others filed in and took seats around the table. They introduced people and said they would go around the table, allowing the various developers to ask questions.

    The first person to my left looked at me and asked me, “what is ACID?”

    I started to answer. “It’s Atomic, Consistent, …”

    I couldn’t remember. I was sitting there, this great opportunity being offered to me, a friend’s recommendation, and I was stuck on the first question of the afternoon. With a room full of developers watching me.

    “I don’t know what the rest of the acronym stands for, “ I said (if you don’t, look it up), and then I proceeded to explain that the idea of ACID in databases has to do with transactions and ensuring data integrity and consistency. I also explained that I could google the exact terms, and would after the interview.

    I didn’t know, and I admitted it. I try to be honest in interviews, hoping that I can get the company to be honest with me. It’s a joint interview, and I interview the company as much as they interview me.

    However I also know that I don’t know everything. I’ve had multiple questions over the years where I didn’t know the answer, and I just admit I don’t know, explain how I might solve the issue. Someone asked me about a complex networking issue one time and I didn’t know the answer. I admitted it, but also told them I had a friend that was a CCIE and he’d be who I’d ask about networking issues I didn’t understand.

    When you don’t know, admit it. However don’t stop there. Explain what you’d do. Show how you can learn, or solve the problem. Would you ask other developers on the team? You can do this, but not too often. Would you research? How long do you work on it before you ask for help? Do you admit when you’re out of your depth?

    Often I’ve found that showing you can admit faults, can learn, and have plans to drive yourself forward help. Of course, you can’t be woefully under qualified. If I interviewed as an MDX developer, I could talk for days on how I’d learn and the people I could ask for help, but outside of an entry level position, I wouldn’t be qualified to work in that area. Hopefully, however, I’d have made that clear in a phone interview.

    Admitting you don’t know is OK. Just don’t stop there.

  • T-SQL Tuesday #53–Why So Serious?

    tsqltuesdayIt’s an interesting topic this month for T-SQL Tuesday #53. When I read Matt Velic’s invitation, I became intrigued. I like jokes. I like messing with people, and I was thinking about the recent April Fool’s jokes I’d written and those in the past that have been fun. I’m going to enjoy this.

    You can participate as well on any month. Just write a blog post and publish it on the second Tuesday of the month, linking back to that month’s invitation. Follow the #tsql2sday tag on Twitter or set a reminder and Google on the first Tuesday of the month.

    If you hurry, you can participate this month.

    Not So Serious

    The number one rule is know your audience. I’d be very careful who I might play this joke on. Note that this is also a bit of a abuse of privileged accounts.

    There are a few utilities from Sysinternals (owned by Microsoft) that are very useful for administrators. However they have also been very handy for practical jokes as well. Specifically I’ve used the PsExec and PSKill to amuse myself.

    I once worked in a group that had four administrators to run our systems. We lost one of our people to a better job and the corporate management decided to "promote" an internal employee to replace him. This new person was a "paper MCSE", who had studied for the exams. He was making an effort to grow his career and that was something to admire.

    However he was a jerk.

    He had the mistaken impression, as many people that have achieved something they set out to do, that they know more than they do. In this case, much of his "book learning" and boot camp work wasn’t appropriate for the real world. What’s more, when we would work with him to teach him how things worked differently for us, or explain why the book recommendation wouldn’t work, he was arrogant and dismissive of us.

    After suffering through a few months of his dismissive attitude and desire to avoid learning more in the real world, we decided to play a few jokes on him. We downloaded the sysinternals tools and also wrote a few scripts to command line launch tools with specific scripts or settings.

    One day when he picked up the phone to work on a call, we watched him start to connect to a server. Using PSList and PSKill, we’d kill his connection. Then we’d quickly use PSExec to launch another connection to a different server. At times we could be creative and redirect him to a development server instead of a production server. He’d swear he was fixing something for a user, but since he was on the wrong server, the user didn’t see the item working correctly.

    We were sporadic in our efforts, only causing issues a few times a day when we felt the problem wasn’t too critical. Eventually my manager realized what a few of us were doing and had us stop.

    It was a bit mean, and not something we should have done maliciously. I was young, and let myself get irritated. In later years, I out grew some of this childishness and limited jokes to people that I genuinely liked, changing settings, altering wall paper, creating aliases that pointed to different systems, and even unplugging people mice and connecting my own in an adjacent cube.

    Joking around at work can be fun, but remember that it shouldn’t be malicious and it can’t prevent people from getting work done. We certainly don’t want to get someone fired, as much as we might like to at times.

  • T-SQL Tuesday #52 – Arguments

    tsqltuesdayIt’s time for T-SQL Tuesday, this month hosted by Michael J Swart, our artistic DBA in the community. I love Michael’s pictures, and enjoy the time I get to chat with him at events.

    His invitation this month for #52 is to argue against a popular opinion. He asking us to find a "never ever" issue and argue for it. Or perhaps an "always do this" advice and argue against that. It’s a great topic.

    T-SQL Tuesday is the monthly blog party, invented by Adam Machanic. The second Tuesday of the month is the time to publish a post on your own blog about the topic and join the other people that write about the same topic.

    Never Turn on Auto Shrink

    I think that if you turn on the auto-shrink setting, Paul Randal rips an LED off his interactive panels. Even if he doesn’t, why would you ever enable auto shrink? It causes fragmentation, it’s somewhat insane as your files will just regrow if the space is needed for a workload such as a maintenance operation.

    The recommendation is to never turn on Auto Shrink on your databases. There are numerous references, though I’ll point to Brent Ozar’s nice summary. I’m going to pick a fight with this advice, though only in a narrow, limited sense.

    Auto shrink is, in general, a very bad idea. However I think there are a few places where I’d use the setting, understanding the implications.

    The Road Warrior

    There was a salesperson that had a laptop, traveling around and working his a piece of SQL Server (Express) based software. Let’s call him Joe, since that’s a nice generic name for this story.

    When Joe arrived at a client site, he’d get data from the clients in an Excel spreadsheet. He’d use the import wizard, load a bunch of data into a new table, point his software at it, and do some analysis of the data. He’d then make recommendations to the client and sell something based on his recommendations. When he was done, he’d click a button in the software and drop the table. The amount of data from clients varied, and it sometimes caused his database (and log files) to grow. He’d also forget to the drop the table sometimes and would wind up with 4-5 clients’ worth of data before he removed the tables.

    Over time, this also caused his laptop to run low on space. This wasn’t a big deal, and the help desk could respond to his concerns by remotely running a shrink of the files for him. However the time spent dealing with questions, and the concern that Joe experienced weren’t worth the effort. Auto-shrink worked fine for Joe and some of his colleagues.

    Performance wasn’t a big concern, as a single user accessed the database. The data didn’t live long enough to worry about fragmentation or maintenance and the reduction in irritation from Joe and the help desk was worth it.

    The Dusty Archives

    I once worked in a company that had an archive database. We had a process that copied data from previous years to a new database that was taken offline and a copy burned to a few optical discs for long term archival. We actually build this process to use a separate instance for the copy as the source SQL Servers were located in various parts of the US, and the data was remotely transferred to a central location for archival, and because we didn’t want to ship optical drives to multiple locations and explain to warehouse personnel how to use them.

    I, and my cohorts in IT, also didn’t really want to travel to small, inconvenient warehouses located in small cities without direct flights.

    One of the requirements was that we make these databases as small as possible to save space. We typically built a few archive databases before we shrank them and took them offline. In hindsight, the process of copying the data should have minimized the size of data and log files with its own shrink, but that wasn’t the case. An administrator needed to perform the shrink and sometimes they’d forget. Actually, most of the time they’d forget.

    The simplest solution was to enable auto shrink on this instance. That way once database files were sitting on the instance for any length of time, the databases would shrink themselves. No need for anyone to worry.

    Performance wasn’t an issue in these cases as recovery of the data would have meant a transfer back to a source system, where fragmentation and indexing would have been a concern.

    Narrow Cases

    In both cases, the reason auto shrink worked was performance wasn’t an issue, and administration was. Performance didn’t matter, and it wasn’t a heavily used system. I think these, and perhaps a few other narrowly defined cases are acceptable uses of Auto Shrink.

    However I also think there are many more abuses of the setting and I’d prefer it removed from SQL Server. Scheduling this process with Powershell would be trivial if you really needed it.

    And if you don’t know how to use Powershell (or SQLCMD), perhaps you shouldn’t be shrinking database files.

  • T-SQL Tuesday #51 – Place Your Bets

    tsqltuesdayIt’s T-SQL Tuesday time again and Jason Brimhall is hosting this month’s event. He used to live in Las Vegas, and with that theme in mind, he calls for you to Place Your Bets.  That’s a time when someone has gambled, or risked something on an application, venture, process that relates to SQL Server? When have you done something that caused an issue you could have avoided?

    It’s an interesting topic, and I’m looking forward to reading what people write.

    T-SQL Tuesday is a monthly event, the second Tuesday of the month, started by Adam Machanic (blog|twitter). You can participate by setting up a blog and writing on the month’s topic. Check Twitter for the #tsql2sday hash tag or look for the blog postings with a search.

    If you want to host, contact Adam.

    The Big Bet

    I didn’t place this bet, but a bunch of application designers and management did. I came in to the company late, and realized we had issues, but wasn’t able to effect change in any short order.

    I worked for a large software company. Large as in thousands of employees, and certainly hundreds of developers. Most were engaged in writing the software we sold, but there were plenty of people engaged in managing our internal systems and writing software that we were using internally. One of these was our internal Intranet site for employees.

    This was a large project, replacing a number of applications, and designed to be a central point of information where employees could turn and various departments could publish information. Being a software company, we knew that it’s worth paying for software that’s written and we chose a framework that would cover many of our needs, but allow us to customize parts of the site for use by different departments. All in all, a CMS-type system that would fit our needs.

    The project started before I did, and no one consulted me or my group, as the production DBAs, as to the architecture, hardware spec, or anything else. It’s not that we should have, or would have, led the way, but we could have offered opinions about how things might perform.

    The day of the roll out came. I was told which day it was and never participated in any testing. The global policy was changed to set everyone’s default browser to the intranet, and I came into work expecting to find a new site where I’d see information posted, resulting in (I hoped) less emails from various departments. Not long after I arrived, I was pulled out of a meeting because of performance problems.

    I had no idea what to look at on the site, but we found CPU pegged and very high I/O on the server. I decided to run a short Profiler trace from my machine to get an idea of what the workload was and what we might look to run. I found fairly simple queries, lots of SELECT * to a few tables. What was interesting was that the queries were scrolling rather quickly on the screen. I found that the default trace values in SQL Server 2000 were storing around 25MB/sec worth of data on my local hard drive.

    In 2001, this was a fairly large load on a SQL Server 2000 instance. When we checked the tables and calculated result sets, we found very little data from each query. There were missing indexes, but even adding those to tables that were a few hundred rows of data didn’t help. The problem was simply that so many employees, each hitting the server by default, with a series of sidebars on the web pages, each of which created its own connection, ran its own query, and ran lots of them on each page, was simply overloading the hardware.

    If was bad design, poorly tested, and not well thought through. Even simple caching mechanisms as Brent Ozar has talked about would have dramatically removed a lot of the load. In the end, those weren’t really needed. What we found was that the majority of the information being returned by queries changed less than once a week. A few weeks of development time removed most of those queries and had them replaced with static XML files that were loaded by the application for display, and could be updated from the database by an administrator.

    Ultimately the site worked well, and we did get less emails from various departments since announcements could be made on the Intranet, which most of us learned to check once or twice a day.