DBA career advice

  • MikeS.

    SSC Veteran

    Points: 217

    It's funny you should bring up the subject of 'nolock' -- at most places I've worked, it's a big no-no. At my current job, you'll get your hand slapped if you *don't* use it.

    Trying to report off of tables that are constantly being backfilled with millions and millions of records of credit card information just doesn't work without the nolock, otherwise you'll be waiting a *long* time for results of the queries... :-/

  • Alan Burstein

    SSC Guru

    Points: 61019

    MikeS. (6/10/2016)


    It's funny you should bring up the subject of 'nolock' -- at most places I've worked, it's a big no-no. At my current job, you'll get your hand slapped if you *don't* use it.

    Trying to report off of tables that are constantly being backfilled with millions and millions of records of credit card information just doesn't work without the nolock, otherwise you'll be waiting a *long* time for results of the queries... :-/

    Nolock table hints are fine when you don't need to get the correct results every time you execute a specific query. In a Production environment where you must have accurate results every time you run a query then NOLOCK (AKA READ UNCOMMITTED) is a no-no.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code.  -- Itzik Ben-Gan 2001

  • Jeff Moden

    SSC Guru

    Points: 993884

    patrickmcginnis59 10839 (6/10/2016)


    RonKyle (6/9/2016)


    Besides, if you have to prepare for an interview, then you're not ready for the job.

    Disagree. It never hurts to review the basics even though you know them to ensure you can state them succinctly. I know what ACID is and what the normal forms are, but if I had an upcoming interview, I would review these topics and others to refresh my memory and ensure that if a question hit on these areas I would answer with no hesitation.

    Have to add my vote to that. Its good to prepare for the specific interviewer also, like show interest in the company by researching it if possible. If you feel theres a chance of questions in areas you don't routinely work in might want to be at least read up on them. Don't invent experience that doesn't actually exist, but knowing something is always better than not knowing something in my opinion.

    ACID, normal forms, isolation modes, declarative versus procedural code, those are always good topics to refresh up on, even if at your previous job your vendors used nolock LOL, heck for that matter study why nolock is a bad idea even if you've never used it. Never know what question you might get right?

    But, if you got caught flat footed and had to submit to being interviewed in the next 10 minutes, would you fail the interview just because you didn't have time to prepare for the interview? Probably not and that's what I meant by if you [font="Arial Black"]HAVE [/font]to prepare for the interview.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • RonKyle

    SSC-Dedicated

    Points: 31423

    But, if you got caught flat footed and had to submit to being interviewed in the next 10 minutes, would you fail the interview just because you didn't have time to prepare for the interview? Probably not and that's what I meant by if you HAVE to prepare for the interview.

    I would agree then. English is a fairly contextual language. There's often room for ambiguity.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    MikeS. (6/10/2016)


    Trying to report off of tables that are constantly being backfilled with millions and millions of records of credit card information just doesn't work without the nolock, otherwise you'll be waiting a *long* time for results of the queries... :-/

    I find it odd that people use that justification for nolock when read committed snapshot gives you the same ability to read without being blocked and doesn't also sometimes return too many/too few rows.

    Why go to the trouble of mandating nolock, when the DBA can enable read committed snapshot and everyone can query without needing isolation level hints? (or snapshot if you don't want the optimistic concurrency on by default)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • RonKyle

    SSC-Dedicated

    Points: 31423

    I find it odd that people use that justification for nolock when read committed snapshot gives you the same ability to read without being blocked and doesn't also sometimes return too many/too few rows.

    Why go to the trouble of mandating nolock, when the DBA can enable read committed snapshot and everyone can query without needing isolation level hints? (or snapshot if you don't want the optimistic concurrency on by default)

    For all I think I know about SQL Server, I'm always learning something that leaves me scratching my head wondering how I didn't know that before. Or maybe you just phrased it in a way that I've never seen before and didn't realize the fuller implications until now. Not that I've allowed NOLOCK, but now I have a better option.

  • Jeff Moden

    SSC Guru

    Points: 993884

    RonKyle (6/11/2016)


    But, if you got caught flat footed and had to submit to being interviewed in the next 10 minutes, would you fail the interview just because you didn't have time to prepare for the interview? Probably not and that's what I meant by if you HAVE to prepare for the interview.

    I would agree then. English is a fairly contextual language. There's often room for ambiguity.

    Especially in the short space used. Thanks, Ron.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • x

    SSC-Insane

    Points: 23349

    Jeff Moden (6/10/2016)


    patrickmcginnis59 10839 (6/10/2016)


    RonKyle (6/9/2016)


    Besides, if you have to prepare for an interview, then you're not ready for the job.

    Disagree. It never hurts to review the basics even though you know them to ensure you can state them succinctly. I know what ACID is and what the normal forms are, but if I had an upcoming interview, I would review these topics and others to refresh my memory and ensure that if a question hit on these areas I would answer with no hesitation.

    Have to add my vote to that. Its good to prepare for the specific interviewer also, like show interest in the company by researching it if possible. If you feel theres a chance of questions in areas you don't routinely work in might want to be at least read up on them. Don't invent experience that doesn't actually exist, but knowing something is always better than not knowing something in my opinion.

    ACID, normal forms, isolation modes, declarative versus procedural code, those are always good topics to refresh up on, even if at your previous job your vendors used nolock LOL, heck for that matter study why nolock is a bad idea even if you've never used it. Never know what question you might get right?

    But, if you got caught flat footed and had to submit to being interviewed in the next 10 minutes, would you fail the interview just because you didn't have time to prepare for the interview? Probably not and that's what I meant by if you [font="Arial Black"]HAVE [/font]to prepare for the interview.

    Its a good idea to prepare for interviews in my opinion. It shows interest in the company, and you are never harmed by reviewing and practicing on some questions and topics like I listed, and researching the company for the interview. It builds confidence and psychologically prepares you to participate in interviewing tasks.

    Normal forms for instance is something I don't regularily use, but its a convenient question for an interviewer. While your job hunting, why not make sure you have them down? Do you think that's somehow cheating?

    Obviously if a company demands I arrive in 10 minutes for an interview, I don't have advice in general for everyone, but I personally would probably give that one a pass, a company that lacks logistical ability to schedule interviews sort of fails one of MY interview questions, ie., can the company schedule meetings in a reasonable manner?

  • Eric M Russell

    SSC Guru

    Points: 124967

    MikeS. (6/10/2016)


    It's funny you should bring up the subject of 'nolock' -- at most places I've worked, it's a big no-no. At my current job, you'll get your hand slapped if you *don't* use it.

    Trying to report off of tables that are constantly being backfilled with millions and millions of records of credit card information just doesn't work without the nolock, otherwise you'll be waiting a *long* time for results of the queries... :-/

    READ_COMMITTED_SNAPSHOT comes with a cost in that it requires row versioning to isolate readers from writers, which consumes more I/O and TEMPDB storage. It can be rigt solution, but read up on the costs. An alternative is to bulk load into a separate staging table and then utilize partition switching to "move" the data into the base table, which requires only a relatively brief schema lock. Readers then have the freedom to query the base table while the the bulk load process is truely isolated within a separate table. Table partitoning is often times reccomended for the wrong reasons, but this specific "bulk loader blocks readers" scenario is where it shines.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • RonKyle

    SSC-Dedicated

    Points: 31423

    Trying to report off of tables that are constantly being backfilled with millions and millions of records of credit card information just doesn't work without the nolock, otherwise you'll be waiting a *long* time for results of the queries... :-/

    READ_COMMITTED_SNAPSHOT comes with a cost in that it requires row versioning to isolate readers from writers, which consumes more I/O and TEMPDB storage. It can be right solution, but read up on the costs. An alternative is to bulk loads into a staging table and then utilize partition switching to "move" the data into the base table, which requires only a relatively brief schema lock. Readers then have the freedom to query the base table while the bulk load process truly isolated within a separate table.

    Good point. And on more careful read of the original comment, how often does the back loading of "millions and millions" of rows occur? Sometimes the uncommon situation is used as a justification for a shortcut that really isn't warranted. The solution is the second point would seem to be one worth trying rather than surrendering to the NOLOCK.

  • Jeff Moden

    SSC Guru

    Points: 993884

    patrickmcginnis59 10839 (6/13/2016)


    Jeff Moden (6/10/2016)


    patrickmcginnis59 10839 (6/10/2016)


    RonKyle (6/9/2016)


    Besides, if you have to prepare for an interview, then you're not ready for the job.

    Disagree. It never hurts to review the basics even though you know them to ensure you can state them succinctly. I know what ACID is and what the normal forms are, but if I had an upcoming interview, I would review these topics and others to refresh my memory and ensure that if a question hit on these areas I would answer with no hesitation.

    Have to add my vote to that. Its good to prepare for the specific interviewer also, like show interest in the company by researching it if possible. If you feel theres a chance of questions in areas you don't routinely work in might want to be at least read up on them. Don't invent experience that doesn't actually exist, but knowing something is always better than not knowing something in my opinion.

    ACID, normal forms, isolation modes, declarative versus procedural code, those are always good topics to refresh up on, even if at your previous job your vendors used nolock LOL, heck for that matter study why nolock is a bad idea even if you've never used it. Never know what question you might get right?

    But, if you got caught flat footed and had to submit to being interviewed in the next 10 minutes, would you fail the interview just because you didn't have time to prepare for the interview? Probably not and that's what I meant by if you [font="Arial Black"]HAVE [/font]to prepare for the interview.

    Its a good idea to prepare for interviews in my opinion. It shows interest in the company, and you are never harmed by reviewing and practicing on some questions and topics like I listed, and researching the company for the interview. It builds confidence and psychologically prepares you to participate in interviewing tasks.

    Normal forms for instance is something I don't regularily use, but its a convenient question for an interviewer. While your job hunting, why not make sure you have them down? Do you think that's somehow cheating?

    Obviously if a company demands I arrive in 10 minutes for an interview, I don't have advice in general for everyone, but I personally would probably give that one a pass, a company that lacks logistical ability to schedule interviews sort of fails one of MY interview questions, ie., can the company schedule meetings in a reasonable manner?

    I absolutely agree... you're a fool not to prepare for an interview... especially if you want or need the job.

    Just to be sure, though, my point is that if you HAVE to prepare for an interview, especially when it comes to what may be asked concerning the primary skill required by the job, then you may not get the job even if you do prepare because you might not actually know enough about your primary skill.

    Heh... and to your point, I'd keep an eye peeled for cultural problems of any company that required a "show up right away or forget it" type of interview. It may be a warning of bad things to come or it may be that they want to scarf you up before anyone else does (which may or may not be a bad thing).

    There's also what I refer to as an "Accidental Interview" that starts with your next breath. These are happenstance interviews that could have their start in a chance meeting just about anywhere. There's no time to "prepare" for such a thing if you're not already prepared. Either you know what you're talking about or you don't.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • x

    SSC-Insane

    Points: 23349

    Jeff Moden (6/18/2016)


    patrickmcginnis59 10839 (6/13/2016)


    Jeff Moden (6/10/2016)


    patrickmcginnis59 10839 (6/10/2016)


    RonKyle (6/9/2016)


    Besides, if you have to prepare for an interview, then you're not ready for the job.

    Disagree. It never hurts to review the basics even though you know them to ensure you can state them succinctly. I know what ACID is and what the normal forms are, but if I had an upcoming interview, I would review these topics and others to refresh my memory and ensure that if a question hit on these areas I would answer with no hesitation.

    Have to add my vote to that. Its good to prepare for the specific interviewer also, like show interest in the company by researching it if possible. If you feel theres a chance of questions in areas you don't routinely work in might want to be at least read up on them. Don't invent experience that doesn't actually exist, but knowing something is always better than not knowing something in my opinion.

    ACID, normal forms, isolation modes, declarative versus procedural code, those are always good topics to refresh up on, even if at your previous job your vendors used nolock LOL, heck for that matter study why nolock is a bad idea even if you've never used it. Never know what question you might get right?

    But, if you got caught flat footed and had to submit to being interviewed in the next 10 minutes, would you fail the interview just because you didn't have time to prepare for the interview? Probably not and that's what I meant by if you [font="Arial Black"]HAVE [/font]to prepare for the interview.

    Its a good idea to prepare for interviews in my opinion. It shows interest in the company, and you are never harmed by reviewing and practicing on some questions and topics like I listed, and researching the company for the interview. It builds confidence and psychologically prepares you to participate in interviewing tasks.

    Normal forms for instance is something I don't regularily use, but its a convenient question for an interviewer. While your job hunting, why not make sure you have them down? Do you think that's somehow cheating?

    Obviously if a company demands I arrive in 10 minutes for an interview, I don't have advice in general for everyone, but I personally would probably give that one a pass, a company that lacks logistical ability to schedule interviews sort of fails one of MY interview questions, ie., can the company schedule meetings in a reasonable manner?

    I absolutely agree... you're a fool not to prepare for an interview... especially if you want or need the job.

    Just to be sure, though, my point is that if you HAVE to prepare for an interview, especially when it comes to what may be asked concerning the primary skill required by the job, then you may not get the job even if you do prepare because you might not actually know enough about your primary skill.

    Heh... and to your point, I'd keep an eye peeled for cultural problems of any company that required a "show up right away or forget it" type of interview. It may be a warning of bad things to come or it may be that they want to scarf you up before anyone else does (which may or may not be a bad thing).

    There's also what I refer to as an "Accidental Interview" that starts with your next breath. These are happenstance interviews that could have their start in a chance meeting just about anywhere. There's no time to "prepare" for such a thing if you're not already prepared. Either you know what you're talking about or you don't.

    I still think preparing is a good idea. When I started a job hunt recently, I decided to try to get a job as an SQL programmer because I enjoyed it, having programmed in various pc related products (think dbase, access), and had just started coding in SQL server the last few years, so at the start of the job hunt, I read up on SQL in general, and this is preparation in the general sense. I hadn't done administration, but I none the less boned up on backups, recovery models, etc, installed SQL express at home and messed with it and read up on other folks experiences. So even with the Accidental Interview, the preparation wouldn't have been all that useless because the preparation was on going. This doesn't mean that I didn't prepare for individual interviews, you always want to look at companies if you have a serious interview coming up, but even then most of my preparation was more toward the work I was interested in, and a significant portion of my effort was on going and not specific to any one interview.

    This DOES NOT MEAN I invented experience that wasn't there, but knowing something is better than not knowing something!

    I think a career should be managed like a really big long term project, and sometimes you have to do some research and development!

Viewing 12 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply