You’re a data professional working with (or at least applying to work with) a company using the StackOverflow database (I’m using the March 2016 version today). Your users are complaining that this stored procedure is slow:
They didn’t give you parameter 26837 – I’m just giving you that so you can see an execution plan.
You don’t have to talk me through the query itself, or what you’d want to do to fix it. In fact, I want you to avoid that altogether.
Instead, tell me what things you need to know before you start tuning, and explain how you’re going to get them.
I’ll follow up in a few days with my thoughts.
Update 2016/05/28 – 71 comments, holy smokes! One thing is clear: you folks like interviewing for jobs. Because there were so many comments, here’s what I’ll do this week: I’m going to start by talking about what I had in mind when I wrote the interview question, without looking at your answers, then I’m going to read yours because I bet you had even better ideas than I did.
For me, the most important part is, “Can you follow directions?” It’s so tempting to jump in and start fixing the query, but I asked two very specific questions, and I was looking for the answers to those.
Before I start tuning this query, I want to know:
- What parameters make this query run slow?
- What does “slow” mean – in concrete time terms?
- Is it always slow for those same parameters, or does the time vary widely?
- How fast does it need to run when I’m done? Or, how long should I spend tuning it? (Typically, I ask the user to tell me if I should spend 15 minutes, 1 hour, or 1 day tuning the query.)
- How often does it run?
- Could we cache the data in the application layer?
To get these answers, I’m going to:
- Talk to the users for speed guidance
- Query the execution plan cache using sp_BlitzCache® to see if this query shows up in our top 10 most resource-intensive queries, and if so, does it have warnings for Frequent Executions, Parameter Sniffing, and/or Long-Running Queries
- Look at the execution plan to see what parameters it was compiled for
- Talk to the developers to see if caching in the app tier is an option
Now, let’s see what you submitted, and look for neat ideas.
- Mazhar wanted to know the table’s size – great idea! You’d want to tune indexes on a 1GB table differently than you’d tune a 1TB one.
- Raul @SQLDoubleG was one of the first to point out that this code and execution plan are a perfect example of a parameter sniffing problem, good job.
- Mike Taras asked who’s running this proc, users or apps? Good question – you might tune end-user facing code differently than service-based code. He also asked if we really need to return all of these fields.
- Russ suggested zooming out and checking the server’s health overall. A+! That’s the first step in my BE CREEPY tuning process, blitzing the box with sp_Blitz®.
- James Anderson turned it around on the users and said, how do you know this is the proc? Are you running traces to find out that it’s slow? I like James. I bet he has ways of making his users talk.
- Thomas Pullen asked if it was lunchtime yet. I’ll meet him at the pub for a pint and we can laugh about the slow queries in our shops.
- Mike F wondered what the users’ SET operations are, because that’s one of the things that can make troubleshooting parameter sniffing harder.
- Jason Strate emphasized getting real numbers because without that, it’s like “knocking on doors at random in Chicago hoping to find Brent’s swag penthouse.” For the record, I have a doorman, but you’re on the guest list, Jason.
- Stephen Falken wants to know what’s changed on that server recently, ask who has elevated permissions on the box, and what they changed.
- Chintak Chhapia asked how frequently data is added & updated to this table, and what columns are updated. Very good question because it determines your indexing strategy.
- And If_Luke_Skywalker_Was_A_Troll gets very high marks for asking excellent followup and challenge questions throughout, getting folks to think deeper through their suggested answers.
After writing about “For Technical Interviews, Don’t Ask Questions, Show Screenshots”, lots of folks asked what kinds of screenshots I’d show. Here’s this week’s example.
I show each screenshot on a projector (or shared desktop) to the candidate and say:
- What’s this screen from?
- What does the screen mean?
- If it was a server you inherited from someone else, would there be any actions you’d take?
- What questions might you want to ask before you take those actions?
- Would there be any drawbacks to your actions?
- What would be the benefits of your actions?
After a few days, I’ll follow up with my own thoughts.
UPDATE 2016/05/20 – Great thoughts, everybody. This one was fun because it stems from real-life scenarios I’ve seen several times. You wouldn’t believe how long it takes folks to recognize this screen in real-life interviews – often it takes DBAs tens of seconds to realize they’re looking at TempDB. (They often start by talking about some other database because the file name tempdev is so deceiving.)
The DBA heard that they were supposed to create a file for each core, but they misunderstood the difference between cores and processors. The server had 2 processors, each with 4 cores – but they created 2 data files originally.
They had a super-fast SSD attached to the SQL Server as E:, and it’s got a relatively limited amount of space – say 128GB – so they started with small file sizes and let them autogrow.
At some point, the SSD ran out of space, so the DBA added another emergency overflow file on a slower drive (M:). Maybe they shrank it back manually, or maybe they have a job to shrink it – in either case, I get a little suspicious when I see small file sizes because there’s probably shrinking going on.
I got a chuckle out of the answer about the server being a dev box because the database file is named tempdev – even though I see a ton of servers, the default “tempdev” makes me pause every time because it was such an odd file name choice by Microsoft. Funny how everybody’s just as bad at naming conventions as I am.
So to answer the questions:
3. Would I take actions? I’d check to see if there are shrink jobs set up on TempDB, and if so, I’d start by disabling those. I might consider adding more TempDB data files, although if it only had one data file, I’d be a little more careful because it can have a funny side effect.
4. What questions would I ask? What wait types is this server facing? Is the E drive actually a good spot for TempDB? How are the file stats looking on that drive? Have we had a history of running out of space here? How big are the user databases? Are we sorting indexes in TempDB?
5. Any drawbacks? If TempDB is getting regularly hammered, and it runs out of space and needs the overflow file, I might not know it due to the shrinks. I’d start by disabling the shrink jobs so that I can see if this thing grows, and what it ends up growing to. That’ll help me plan for capacity.
6. Benefits to my actions? Some folks mentioned adding files or pre-growing files can make it faster for end users, but be really careful there. Anytime you say something will be faster, then as an interviewer, I’m going to challenge you to define what you would measure, and how it would change. If you don’t have metrics at the ready, then I’m going to suspect cargo cult programming.
On today’s episode of the SQL Server Radio podcast, I talk with Guy Glantser and Matan Yungman about what we look for when we’re hiring.
In the broadest sense, don’t think junior or senior:
- I’m hiring someone for what they already know, or
- I’m hiring someone for their capability to learn
(In reality, it’s usually a blend of both, but just think big picture for now.)
If I’m hiring you for what you already know, then I’ve got a list of skills, and I want to see your proficiency in those skills. If one of those skills includes communication, then I’m going to judge you based on how you communicate your mastery of the other skills. For example, I might be looking at your blog posts, presentations, or webcasts about the topics you’re great at.
If I’m hiring your excellent learning skills, then I want to see what you’ve been interested in learning in the past, and how you’ve gone about learning those topics. It doesn’t have to be technical, either – maybe you were interested in perfecting an Eggs Benedict recipe. Show me what resources you used, your preferred style of learning, what lessons you picked up along the way, and how you would recommend that I learn that same thing as fast as possible.
To hear more about my philosophies on that, and hear how Guy and Matan approach hiring for their own companies, check out the half-hour SQLServerRadio podcast.
Last month’s post “For Technical Interviews, Don’t Ask Questions, Show Screenshots” was a surprise hit, and lots of folks asked for more details about the types of screenshots I’d show. Over the next few weeks, I’ll share a few more.
Normally I’d show this query as a screenshot, but for easier copy/pasting into comments, I’m showing it as code here.
CREATE PROC dbo.usp_ByCategory @Category NVARCHAR(20) AS IF @Category = NULL SET @Category = 'Default' SELECT i.itemID, i.itemName, COALESCE(po.Price, i.Price, 0) AS Price FROM Items I LEFT OUTER JOIN PriceOverrides po ON i.itemID = po.itemID AND po.SaleStartDate >= GETDATE() AND po.SaleEndDate <= GETDATE() WHERE i.Category = @Category
I’d say to the job candidate, “You’ve been asked to take a quick look at this code as part of a deployment. Explain what the business purpose of the code is, and tell me if there’s anything that concerns you.”
After a few days, I’ll follow up with my own thoughts in the comments.
This year, we’re trying something new: every Monday, we’re going to show you one module out of our online training classes.
It’s the whole module, not a sneak peek, and it’s available the whole week. But you’d better not put it off – this post will disappear the following week, and get replaced by a new post.
In this week’s free video training sample, Kendra Little grills you with 25 core DBA skills questions, 22 infrastructure/platform questions, and 23 performance tuning questions:
UPDATE – the video has expired!
(If you don’t see the video above, you’re reading this somewhere that doesn’t support video embedding. You’ll need to read the post on our blog.)
The full course covers resume-building for DBAs, 5 things you should always do in a DBA interview, how to get in front of hiring managers, and much more. Go check it out, and if you’re an Everything Bundle subscriber, you can start watching the training course now.
When it comes to hiring a SQL Server developer, we can pepper candidates with HR-type questions (“Tell me about a time when you had a conflict with a co-worker and how you resolved it.”) but that doesn’t give us a good sense of a developer’s T-SQL skill level. Here are five questions that will give you a good idea how experienced and skilled a developer is.
1. “WHEN IS IT OKAY TO USE A CURSOR?”
Cursors are the most widely misused T-SQL tool out there because beginning developers writing them haven’t yet learned to think in sets. Once we learn how cursors really behave, it’s tempting to swear them off for good. Senior developers ought to be able to provide an example of when it’s really okay, such as a script that does index maintenance or DDL commands.
- Junior Developer answer: “Anytime.”
- Developer answer: “Never.”
- Senior Developer answer: “When there’s no way to accomplish the task in a set but you have to go through multiple iterations.”
2. “WHAT ARE THREE CODE CHANGES YOU’D MAKE TO SPEED THIS QUERY UP?”
The trick here is having code that might be passable but still leaves plenty of room for improvement. This isn’t a question a manager can ask; an experienced developer will have to come up with the code and decide if the suggested improvements will work. (If you’re the one writing the sample code, here are a few ideas:
user-defined functions, table variables, unnecessary sub-queries, and non-SARGables in the WHERE clause.)
- Junior Developer answer: “I dunno, looks good.” (reaches for something insignificant to change)
- Developer answer: “Oof! Who wrote this?” (goes on to list examples)
- Senior Developer answer: “Here are a few things I see…”
(goes on to list examples and sprinkles in a story about how they learned that by experience)
Although it’s nice to get contextual questions back (like if any indexes exist that can be leveraged) but really it’s about spotting purely code-based inefficiency.
3. “WRITE CODE TO CHECK IF ANY RECORDS EXIST.”
A very common code scenario involves checking for the existence of any records that match certain criteria (or just for a table as a whole). The trick here is that all we care about is a yes/no answer. We want that check to be quick and cheap.
- Junior Developer answer: “SELECT COUNT(*)…”
- Developer answer: “SELECT COUNT([field name])…” (Bonus points if they say the field is indexed.)
- Senior Developer answer: “SELECT TOP 1 1” or “EXISTS (SELECT [fieldname]…)”
4. “WHAT IS PARAMETER SNIFFING?”
When it goes bad, parameter sniffing is a tricky problem to identify for developers. It can make a query perform like Dr. Jekyll one minute and Mr. Hyde the next. There are some things you can do to mitigate risk with parameter sniffing, but just understanding its nature is a big step. Bonus points to anyone who explains how they dealt with parameter sniffing in the past.
- Junior Developer answer: “I’ve never heard of it.”
- Developer answer: “It’s something to do with the query guessing wrong.”
- Senior Developer answer: “It’s when a query uses a plan based on a different parameter value than the one being passed in and the resulting execution is potentially awful.”
5. “DESCRIBE HOW YOU RESEARCH A PROBLEM YOU’VE NEVER ENCOUNTERED BEFORE.”
I’ve yet to meet a good developer who wasn’t also a good researcher. Our success depends on our ability to fill in our knowledge gaps quickly. Books Online is great for syntax but not insight. Technical books are only good if they address your exact problem and you can’t copy/paste directly from them. (Speaking of copy/paste, “search online for the exact error text” is a great answer too.) StackOverflow and Twitter are high-traffic places where people are eager to help you.
- Junior Developer answer: “I go to Books Online or look in a technical book.”
- Developer answer: “I search online, especially on StackOverflow or MSDN forums.”
- Senior Developer answer: “I check sites like StackOverflow or throw the question out on twitter with the #sqlhelp hashtag.”
These are the big questions that came to my mind. What questions have you found helpful in hiring developers? Let us know in the comments!
Hey SQL Server DBAs — we’re hiring!
Here’s a quick five question quiz. Give yourself one point for every ‘yes’:
- Have you been a database administrator for a few years?
- Does planning how to keep an application reliable and safe from disasters sound like fun?
- Do you love helping people make SQL Server go faster?
- Would you like to have a cartoon character of yourself?
- Can you see yourself dropping into our Office Hours sessions occasionally to help people for free?
If you got five points, you just might be the next Brent Ozar Unlimited employee!
Here’s what to do next:
Read more about the job here to find out what we’re looking for and what benefits we offer. Then tell us about you! We plan to accept applications through Friday, March 6, but don’t wait too long to submit — life moves fast sometimes.
Update March 7 – applications are closed, and we’re interviewing candidates. Thanks!
Hiring a DBA? Need to get a job description for the human resources folks? Here’s how to get started.
First, decide whether it’s a production or development DBA. Think of the database in terms of a fridge. When you run a restaurant, you need at least one (and probably several) refrigerators to keep your raw ingredients and your prepared dishes cold.
Your chefs rely on the fridges to get their jobs done. They have tons of training to pick the right ingredients to put in the fridge, prepare the food correctly, and know when to take things in & out of the fridge.
If your restaurant absolutely, positively cannot go down, you’ll end up hiring a handyman or facilities guy. He has to know how fridges work, and if a fridge can’t keep the food cold enough, he steps in to diagnose and fix it.
The chefs are your developers.
When you have a LOT of chefs, you hire a development DBAs to organize the fridge and clean it out. They don’t usually write code, but if they do, the code is inside the database – they’re not writing presentation-layer code in C# or Java.
The handyman or facilities guy is your production DBA. He’s more concerned about the back side of the fridge than the front side. He doesn’t do any
They all work with the fridges, but the similarities end there. Small shops might indeed hire one guy to buy the food, put it in the fridge, cook it, and fix the fridge when it breaks. However, those shops aren’t going to win any awards for food quality, and when the fridge breaks, the cooking stops while he fixes the fridge.
Sample Production Database Administrator Job Description
This position’s job duties and responsibilities include:
- Ensure all database servers are backed up in a way that meets the business’s Recovery Point Objectives (RPO)
- Test backups to ensure we can meet the business’ Recovery Time Objectives (RTO)
- Troubleshoot SQL Server service outages as they occur, including after-hours and weekends
- Configure SQL Server monitoring utilities to minimize false alarms
- As new systems are brought in-house, choose whether to use clustering, log shipping, mirroring, Windows Azure, or other technologies
- Install and configure new SQL Servers
- Deploy database change scripts provided by third party vendors
- When performance issues arise, determine the most effective way to increase performance including hardware purchases, server configuration changes, or index/query changes
- Document the company’s database environment
To do a great job in this position, experience should include:
- On-call troubleshooting experience with at least one production SQL Server for a year. You don’t have to be the only DBA or have DBA in your job description, but you should have been the one person that the company would call if the SQL Server service stopped working.
- Finding DMV queries to answer questions about server-level performance
- Using free tools like sp_Blitz® and sp_WhoIsActive to diagnose server reliability and performance issues
The following skills aren’t strictly necessary, but will make you a well-rounded candidate for bonus points:
- Tuning T-SQL queries to improve performance
- Troubleshooting hardware using tools like Dell OpenManage, HP System Insight Manager, and IBM Director
Sample Development Database Administrator Job Description
This position’s job duties and responsibilities include:
- Ensure that new database code meets company standards for readability, reliability, and performance
- Each week, give developers a list of the top 10 most resource-intensive queries on the server and suggest ways to improve performance on each
- Design indexes for existing applications, choosing when to add or remove indexes
- When users complain about the performance of a particular query, help developers improve the performance of that query by tweaking it or modifying indexes
- Conduct SQL Server lunch-and-learn sessions for application developers
- Advise developers on the most efficient database designs (tables, datatypes, stored procedures, functions, etc)
To do a great job in this position, experience should include:
- Writing and improving SQL Server T-SQL queries for at least a year. You may have technically had “C# Developer” or “Java Developer” on your job title, but you were known amongst the office as the go-to person for T-SQL questions.
- Designing tables and picking datatypes
- Using Profiler traces and other tools to find the most frequently run queries
- Using free tools like sp_BlitzIndex® and DMV queries to answer questions about index usage
The following skills aren’t strictly necessary, but will make you a well-rounded candidate for bonus points:
- On-call troubleshooting for SQL Server service outages
- Deciding whether clustering, log shipping, mirroring, replication, etc are the right fit to solve a business problem
Things I Didn’t Include In These DBA Job Descriptions
If you’re using any of the following technologies, mention it in your job description so that the candidates know what to expect:
- Failover clustering, SAN replication, and other high availability technologies
- SQL Server merge, peer to peer, or transactional replication
- LINQ, Entity Framework, NHibernate, or other ORMs
- Service Broker
- Analysis Services, Integration Services, or Reporting Services
There’s nothing wrong with having your production or development DBA work with those technologies, by the way – but they’re special technologies that require prominent placement in job descriptions.
Learn More: Our DBA Job Interview Q&A Kit
Good interviewing is not a talent: it’s a skill you build with study and practice. This set of online videos trains you to be a rockstar in SQL Server database administration job interviews by giving you practice video interviews where you can warm up with 100 real-world questions. You get:
- 70 technical SQL Server DBA interview practice questions (and answers)
- 10 “situational” DBA interview practice questions with strategies on how to answer
- 20 questions for YOU to ask your prospective employer
Buy SQL Server Interview Training
18 months of access for $29
- 5 things to always do in a DBA interview
- 3 Rules of a good DBA resume: how to write your DBA story
- Resume Anti-Patterns to avoid
- 3 Ways to Get in Front of Hiring Managers
- How to prepare for technical screenings and in-person inteviews
- 3 things to NEVER do in a DBA interview
- How to handle a DBA job offer
Buy it now and get started! For $29, you can watch it for 18 months on your desktop, laptop, iPad, or iPhone.
Don’t you just hate updating your resume? It feels so awkward writing about yourself and trying to describe yourself in a single page.
We convinced a few SQL Server professionals to submit their resumes for review, and we’ll help do an Extreme Resume Makeover. Our tips for their resumes will help you too.
Ever dreaded writing a resume so much that you put it off until the last minute? When you finally wrote it in desperation, were you happy with it? Are you kind of afraid to look at it now?
I know what that’s like, because I’ve been there. But I’ve written two resumes in the last week without any of the pain using a method I’ve found that really works. The method breaks down into five steps.
Step 1: Get the Facts, and Only the Facts
First, the dirty work. Compile a list of where you worked and when:
- Company names you worked at (with locations)
- Your job titles
- Start and end dates
If you’ve been working a while, stick to the last 5-7 years. I know it’s hard to leave out the cool stuff you did in the 90’s, but just post the pictures on Facebook– hiring managers don’t want to read about it.
Optional items that can set you apart:
- URLs for relevant online profiles (Think http://dba.stackexchange.com/— even if you’ve just asked questions, that can still say something very positive to an employer.)
- Certification Titles (be precise)
- Degrees and graduation dates
You can be selective about how much detail you put on your resume. You do not need to list your graduation year, for example, if you are concerned about age discrimination.
Step 2: Make a Date with a Friend and a Text Editor
Here’s where we take the pain out of writing your resume. For most geeks, it’s much harder to write about yourself than it is to talk to a friend about what you did.
Identify a friend who can type and who you feel comfortable with. Ask them to spend an hour with you helping you rebuild your resume. This person doesn’t have to be a geek at all, they just need to be able to take notes and ask you a set of questions. For safety reasons, pick someone who’s not a significant other or family member.
To prepare for your interview, open a simple text editor. Create a file and enter your name and employment history, with spaces in between. Make a basic template:
##DBA, Stormtroopers International##
Portland, OR (2010-present)
## Helpdesk Supervisor, This Is a Legitimate Business, LLC ##
Seattle, WA (2009-2010)
## Helpdesk Tech, This Is a Legitimate Business, LLC##
Seattle, WA (2007-2009)
Step 3: Get Interviewed on Your Work History
At the appointed date and time, sit down with your friend, open your text file, and hand over the keyboard. Take a few deep breaths. Relax. Your friend’s mission is to interview you about each job and take notes. For each job they should ask you:
- What did you learn at the job?
- What are you proud of doing at the job?
- What technologies did you use?
- What processes were important to success?
Start at the job that’s farthest back because that’s probably a job where it’s OK to have the fewest details. By the time you get to your current job, you should be in the swing of things. At the end of the interview you should have a giant brain dump of details in a text file about your work history.
Step 4: Craft Your Story
Wait a day or so after the previous step. Then save a new copy of the file and start editing your “Work Mess” with gusto.
Stay in your text editor in this step! Don’t think about formatting or presentation.
Make your edits based on one big question: What story do I want to tell about where I’m going in my career? There are an infinite amount of details you could give about yourself, but you need to limit yourself to only a page.
You may choose to add an “extra” feature to the top of your resume after you finish your work history. If you’re going to do this, just pick one “extra” resume feature, and make sure you it really works for you. Here are some items that can work:
- List of specializations. This one is common and a bit expected, but it puts buzzwords at the top of your resume. That helps with recruiters and HR. A word to the wise: only put technologies in the list that you’re great at and want to be grilled on, or this will go terribly terribly wrong.
- Short stories of what you’re proud of. I put three recent awesome projects at the top of my resume.
- Something playful, but relevant. Jeremiah Peschka put a countdown of information about himself on his resume. That can be risky, but if you pull it off then it’s awesome.
- How You’re Teaching Yourself. If you’re transitioning or just starting out in an industry, you can include information on what you’re doing to build up your skills before you get a job. I encourage learners to do work in virtual lab environments— if you’re doing that, you have stories you can tell.
There are a few pitfalls that I see commonly. Avoid a statement about what job you’re trying to land. Those statements come off as predictable and meaningless. The top of your resume is the most valuable real estate on it– you need to make a great impression there, so don’t waste it on something forgettable.
Step 5: Get Feedback from a Trusted Advisor
At this point your resume is still in a text file. That’s great. You’ve been focusing on content, and guess what? Many companies are going to make you submit a text version (or a very simplified version) for their database.
Before you worry at all about formatting, get feedback on your story— that’s your resume— from a trusted advisor. After you get feedback, apply some polish and make yourself happy with the content. At long last, you can use a word processor if you’d like.
More Help: Our DBA Job Interview Kit
Good interviewing is not a talent – it’s a skill you build with study and practice. Our training kit gives you 70 technical DBA interview questions and answers, 10 situational questions with strategies on how to answer, and 20 questions for YOU to ask your prospective employer. Learn more now.