Are you a functional DBA?

I’ve worked with SQL Server long enough to have interacted with several hundred database professionals, one on level or another. I’m going to take that as license to tell you this, one internet friend to another: You’re probably not the DBA you think you are.

Junior

If you’re just starting out as a DBA or SQL programmer, then you likely know that you don’t know much. You’re spending your days working out the ways to do different tasks, looking up best practices, and stumbling over “Oh, wow!!” moments in various blogs and books. Good for you! Never stop studying, my friend.

Mid-level

You have a few years of SQL Server on your resume…maybe even several. You’ve worked for long enough, and/or for enough companies, that you feel like you’ve got the basics. You probably have a sense for your strong points…”I’m good with query tuning.” “I’m really an admin guy.” “I’m happiest elbow-deep in SSIS.”  You might be on a team with other SQL folk, or you might be the only DBA/dev/BI guy in a smallish shop.  Backups, security, user requests…got it covered.

The thing is, you don’t (bless yer heart).  Yes, I know you passed your MCITPLDLOMP last year, or whatever the latest test is, and good for you. But you haven’t picked up a book since, or made a conscious effort to learn a new thing.  Reading blogs is nice, and I’m glad you do…but reading blogs is not the same as studying.

This doesn’t apply to you? I see. Then you won’t mind answering a few questions…

  • What’s the difference between char and varchar?  Between nchar and char?
  • What are the requirements for an indexed view?
  • What should you do about blocking? How about deadlocking?
  • What does the sp_ prefix signify for a stored procedure?
  • Name three DMVs you use on a regular basis.
  • What’s the syntax to expand a data file? (No fair reading the last blog.)
  • Name three things that really bother you about the product.
  • Have you looked at SQL 2012 yet? Great. Now, have you installed it? Played with it? Explored Filetable, or any of the new cool features?

There’s tons more, but that’s a start. The thing is, at least 80% of DBAs I encounter in the wild* will get most of those questions wrong.  If you have 5-10 years of SQL Server administration on your resume, and these questions lose you, start studying.

High level

High level guys? I’m not going to presume to offer you advice. I suspect you got here by study and experimentation, so you’re most likely in the habit. Just, you know, be sure you’re not fooling yourself.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

*In the wild meaning, away from conferences. Conference DBAs are often better educated, because they give a flying shit about their education. So say we all.

20 thoughts on “Are you a functional DBA?

  1. Brent Ozar

    What’s the difference between char and varchar? Between nchar and char? – Great question.

    What are the requirements for an indexed view? – You know, it’s funny – I think I do a lot of indexed view work now, much more than I ever did as a DBA, and the vast, vast majority of my clients don’t know what an indexed view IS, let alone how SQL Server will use it in Enterprise Edition without specifying the view name, let alone the requirements for it. I’m not surprised that most people wouldn’t know this.

    What should you do about blocking? How about deadlocking? – Ooo, that’s interesting because it’s so subjective. Sometimes you can fix it with less indexes, sometimes more, sometimes making transactions smaller, sometimes changing isolation levels. I don’t think I’d be picky about the answer with “should” – I’d just want to hear the candidate understand a few of the options.

    What does the sp_ prefix signify for a stored procedure? – I got this one. Superpowers.

    Name three DMVs you use on a regular basis. – I can’t. I’ve had the same scripts in use for years, and even though I use it at least three times a day, I can never remember if it’s sys.dm_db_index_usage_from_behind or whatever. That’s what script files are for. If you’re typing the DMV name by hand multiple times per day, you’re doing it wrong.

    What’s the syntax to expand a data file? (No fair reading the last blog.) – Again, if you’re regularly typing this one by hand, you’re doing it wrong. I can’t imagine a scenario where a good DBA is regularly expanding data files by hand. If you need to do syntax that often, automate it.

    Name three things that really bother you about the product. – Love that question.

    Have you looked at SQL 2012 yet? Great. Now, have you installed it? Played with it? Explored Filetable, or any of the new cool features? – I wouldn’t be surprised if people haven’t just because often the company holds back on deploying new versions. It’s like saying, “Tell me what you like about the new Aston Martin Vanquish.” Instead, I’d probably ask, “If you wanted to learn what’s new in SQL Server 2012 and how to get started with it, where would you go?”

    The bottom line with a lot of these is akin to an email I sent you recently – 80% of people out in the wild would indeed get these questions wrong, even decent mid-career DBAs. If you ask a question and 80% of the people don’t know the answer, sometimes the problem isn’t the people – sometimes it’s the question.

    1. Jen McCown Post author

      The bottom line with a lot of these is akin to an email I sent you recently – 80% of people out in the wild would indeed get these questions wrong, even decent mid-career DBAs. If you ask a question and 80% of the people don’t know the answer, sometimes the problem isn’t the people – sometimes it’s the question

      Of course some of the questions don’t have a single right answer, and of course I wouldn’t expect even an MCM to get every single question perfect in an interview…the point is that most DBAs should be able to answer at least half of these questions – or a similar set – right, and they can’t.

      If you’ve watched our show or spoken to us in interviews, you know that we tend to conduct probing, instead of rote, interviews. The person’s resume says they’re experienced in performance tuning, then they should be able to answer some questions about perf tuning. HA, then clustering. Etc etc. But a MAJORITY of the time, they can’t.

      The problem IS the people, again and again and again. I wrote this to get people to look at their own skill set, identify weaknesses, and maybe start trying to correct them. (And no, I’m no genius…I study to improve my weak points, too.)

  2. Justin Dearing

    Name three DMVs you use on a regular basis. – I can’t. I’ve had the same scripts in use for years, and even though I use it at least three times a day, I can never remember if it’s sys.dm_db_index_usage_from_behind or whatever. That’s what script files are for. If you’re typing the DMV name by hand multiple times per day, you’re doing it wrong.

    It depends. Personally, I’m a dev so I don’t use the DMVs much, although I do use the system views because I prefer sqlcmd over SSMS. However, as a vim power user (its a weird text editor) I always prefer to write regexes by hand in vim instead of writing a PowerShell script or whatever specifically because I want to stay sharp with regexes. Then again, some things I script the hell out of. For example, whenever I have to use the OleDb drivers for access and Excel (emphasis on HAVE TO) I pretty much just copy and this code into my app because its a relatively straightforward problem.

    Have you looked at SQL 2012 yet? Great. Now, have you installed it? Played with it? Explored Filetable, or any of the new cool features? – I wouldn’t be surprised if people haven’t just because often the company holds back on deploying new versions. It’s like saying, “Tell me what you like about the new Aston Martin Vanquish.” Instead, I’d probably ask, “If you wanted to learn what’s new in SQL Server 2012 and how to get started with it, where would you go?”

    I’d agree with you if it wasn’t for Express Edition. Now I know as a guy that contributes to OSS, and blogs and speaks occasionally I’m a one percenter (I also acknowledge that you guys are the once percenters of the one percenters.). So in the end I think “I haven’t had time to play with 2012 because quite frankly Id rather play with my kids and watch sports after work then look at more SQL” is perfectly OK for some work environments. However, anyone can install SQL Server Express edition 2012. Its a fair question to ask and any form of affirmative answer shows passion and drive that I’d weight against a technical deficiency. E.g. if the person lacked HA experience, I’d expect him to pick that up quickly if he built am image server with file tables and ASP.NET MVC4 and tuned the crap out of it.

    1. Brent Ozar

      About Express Edition – I hate to sound like a snob, but if somebody tells me their experience with a version is based on playing around with Express Edition on their desktop, I tend to not give that a lot of credibility when hiring.

      1. Justin Dearing

        Brent,

        I meant as supplemental experience. So yeah 9-5 your a DBA running some huge 2005 instance with no clustering solution but dealing with all the real world problem of deadlocks, and statistics getting out of date, and your boss yelling at you for not filing your TPS reports. Then you go home and write some image server with express edition using FileTables and ASP that you throw on codeplex.

        This theoretical guy has the real world experience on yesterdays technology, and hobby experience with the new fashizzle, which one can assume he can easily apply to the real world because he knows about the real world.

        Or to throw another scenario at you, pretend BrentOzarPLF is hiring a triage person to answer the phone and manage your trouble ticket system. You don’t actually expect them to know anything about SQL server. However, some 22 year old that used to work at a cable company help desk applies and mentions his crazy projects with Running Home server as a domain controller and sticking SQL Server on it. Sure your not going to actually let the kid have SA on your clients servers, but wouldn’t you pick him over the other ex-help desk guy with no extracurricular activities, and find a way for him to channel that passion and grow from a hobbyist to a professional?

        1. Brent Ozar

          Justin – that’s like saying, “Would you rather have a serial killer or a bank robber babysitting your kids?” Sometimes the answer is not to hire either one.

  3. Grant Fritchey

    Having spent years & years interviewing entry level & mid-level DBAs, I’d be thrilled to find someone who had a guess what to do about blocking. I could never find someone who could tell me the difference between blocking & deadlocks. I think the questions that explore concepts are excellent. The syntax questions, that’s why BOL is for.

    1. Brent Ozar

      Grant – yep, agreed about the blocking & deadlocking question. I like to ask if someone knows what to do when a query is slow, and most of the time they don’t even come up with checking to SEE if there’s blocking. If I prompt them, I still usually get blank looks. I totally understand why – this is something that production DBAs don’t have to face often.

  4. Bradley Ball (@SQLBalls)

    What’s the difference between char and varchar? Between nchar and char?

    I love this question to. I started asking it after I saw Sean post a blog about it and I still think it is great. Questions in job interviews are opportunities. This one will not only let you show you know data types, but opens the door to talk about data record internals. Lets the both parties learn the range a candidate.

    What are the requirements for an indexed view?

    Brent’s point is valid on this one, I’ve done more of these in my short time as a consultant, then I have ever as a DBA. But I knew how to use them before I was a consultant.

    What should you do about blocking? How about deadlocking?
    What does the sp_ prefix signify for a stored procedure?

    Another good one just because there are so many approaches. Two different issues, not only how do you solve them but how do you monitor them, what versions of SQL do the issues occur on.

    If they are SQL 2000 heavy on the resume they should know how to handle this for that version, 2005 again, 2008 or even 2012, the approach can change yet again.
    Leading from trace flags to extended events and the pro’s and con’s of each.

    Lot of places for the conversation to go on this.

    Name three DMVs you use on a regular basis.

    It’s funny, because I point people to Brent’s script all the time. But I love this question. I don’t care if they get the name wrong, hell I get the names wrong when I type them sometimes, but I want to at least hear that you know sys.dm_exec_requests, that you know you need to join sys.dm_exec_sessions to get certain info. Or even why you still use sysprocesses (and you should).

    I’ll even take using sp_whoisactive & sp_who3, but what do you use, how do you use it,and why do you use it are all important.

    and Irony, I’m going to blog on sys.dm_db_index_operational_stats today 🙂

    What’s the syntax to expand a data file? (No fair reading the last blog.)
    Not sure that I would know the syntax off hand, but I can tell you exactly how to set up Instant Database File Initialization and the trace flags to validate that it is on, and how to use those same trace flags to debug backup and restores through the error log. If I wanted to do this I’d use the GUI and hit the script key, but if I need to do this regularly I’d automate it.

    Name three things that really bother you about the product.
    this also helps see the range of other products, my biggest gripes about SQL sometimes are the features we don’t have. I’m LOOKING AT YOU DEDICATED USER TEMPDB’s!

    Have you looked at SQL 2012 yet? Great. Now, have you installed it? Played with it? Explored Filetable, or any of the new cool features?

    I’m biased, but I think this goes to how willing are you to learn. If you can’t at least name things your interested in, that is bad. Maybe you are so slammed at your current job that you’re just keeping your head above water. I get that, but you should be able to say AlwaysOn (even though that means many things Clustering and Availability groups), Sequence Containers, Contained Database, PowerView, ColumnStore Indexes…. something.

    I’d also toss in What blogs do you read? Or if you got a problem you were not familiar with how would you solve it?

    you should get blogs, forumns, and hopefully #SQLHelp out of this. Maybe even I’d ask my friends at [insert SQL Server User Group]

    All in all great blog!

  5. NSP

    I’ve been working with SQL Server/ Data since 1999 and although my experience and resume sells me as a mid level (i think i could answer 75% of the questions with reasonable answers), I will always think of my skills as junior.
    SQL Server is like an ocean. Just when I think I’ve learned a lot, I realize oh wow there is more underneath what I just learned and there are complementary things to what I just learned. Also, I just know my work life and my personal life only allows for so many extra hours of ‘studying’. Lastly as for SQL 2012, like others have said, that’s so fresh I’m just reading on the new functionality, so when the time does come along I have some clue of what’s it about. But installing it and playing with it is not going to help if my boss isn’t going to implement it for another year or so.
    So I always feel like with SQL Server that I take 1 step forward and then MS fast forwards 5 steps.
    I guess I should have taken this 10 minutes to study rather then reply 🙂

    1. Jen McCown Post author

      I don’t expect most DBAs to be a solid midlevel in every area. But a guy with 6 years under his belt should know a good set of the core basics, at least.

      And I never begrudge anyone who has little time to study…I myself have a job, three kids, and a very active extracurricular life…I don’t even spend an hour a day studying. We go by the method “pay yourself first”, meaning just 20-30 minutes of study every morning. It makes a huge difference.

      And no, you should always read and respond to my blogs! 🙂

  6. Pingback: Something for the Weekend - SQL Server Links 03/08/12

  7. Pingback: Getting to the next level in your SQL Server career

  8. Pingback: I cannot name the 3 DMVs I use on a regular basis, but…. | Dirk Hondong's blog

  9. newbie

    Hi! Jenna,
    Love your blog. I learn something new every time I visit your blog.
    I want to enter the vast field of SQl as a developer. What advice would you give for people like me regarding job search?
    Thank you

  10. Mala

    Jen – great post! I always consider myself a mid level, never a senior – simply because sql server is getting to be such a huge and complex product and there is no way one can know or answer questions on every aspect it has, even if it is only DBA related. We work on clustering and replication, for example and I can answer most questions in that area. We only use transactional replication so i can’t say i’d know ins and outs of all other kinds of replication also, although I do know what they are and how they are different. I really like an interviewer asking me what technologies i have most experience on and question me on those instead of ‘any question within sql server’ which can be pretty tough. Also questions that lead to conversations instead of one person talking through it are usually the best – although sadly many interviewers don’t know answers themselves to engage in conversations. (One of the worst things ever must be to hear your own voice droning on in a large interview room!). And must agree with all that Brent has said. Thank you.

    1. Jen McCown Post author

      I don’t expect even masters to be conversant in every aspect of SQL Server, and I don’t expect anyone on the planet to be able to answer all of the questions right. BUT, if you’re applying for a position as a DBA (as this post specifies), you should be able to answer MANY of the questions intelligently.

Comments are closed.