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!
A sampling of things overheard at our 2015 Performance Troubleshooting Class in Denver:
- “I’m not a GUID hater.”
- “Oh, God help me. I’ve clicked on XML.”
- “Reporting Services looks around at the available memory, makes itself at home, maybe takes its pants off.”
- “Where would I go to check if I — hypothetically — accidentally set my fill factor to 10%?”
- “I use the Database Tuning Advisor for humor.”
- “SQL Server gives you this great set of [partitioning] tools and then sprinkles broken glass all over them.”
- <sirens outside> “The partitioning police are here!”
- “If the index fits, I sits.”
- “You don’t need foreign keys in a data warehouse. Because you have a source system.”
- “This is the title slide. It is also the agenda slide.”
- “It sounds like a bad ad, but the more you query the more you save!”
- <Execution plan recognizes an indexed view and swaps it in> “Let it be known that for once, SQL Server did the right thing!”
- “Great questions. You should have prizes. We don’t have any prizes left, so have some smug.”
- “When there’s no uninstall, there’s your clue that this feature may not be a delicious lava cake. It may in fact be a disease.”
- Attendee: “So how do we know Hekaton ran out of memory?”
Kendra Little: “It stops working.”
- “If your query lasts more than four hours, discontinue using Hekaton.”
- “Tune queries with this one simple trick: TRUNCATE TABLE”
- “There’s a lot of empty lines at the end of this file. I keep scrolling down, expecting to find a bonus album track or something.”
- “Even SHAREPOINT knows better!”
- “The SQL Server is hung. We need a back door.”
Over the years, I’ve done all kinds of awful things with T-SQL and made countless mistakes. Some were harmless; others were borderline catastrophic (exciting times!). I was curious what kind of horrible mistakes other people make, so I threw the question out to Twitter.
Every answer I got was unique, which was both telling (so many ways for developers to mess up) and fascinating (no consensus which was worst). Since I didn’t get any winners by popular vote, here are the top three answers I agree with most, with the worst mistake first:
#1: CODING FOR TODAY
We’ve all been there — we just need a script or stored procedure to run and we’re under the gun to get it deployed. The problem here is that as soon as it works and passes QA — you do have QA, don’t you? — we call it a success and don’t look back. We don’t account for any kind of future growth in data size or user base. Instead of moving on, this is the perfect time to check the working copy into source control and then start refactoring to improve performance.
If we don’t revisit and revise our code, we end up with a server full of code that “ran well enough at the time” but now won’t scale. Future-proofing is never at the top of anyone’s list of priorities but it’s just like exercise and healthy eating. We all know we need to do it. It’s a matter of making the commitment to write leaner, healthier code before it clogs the SQL Server’s arteries or gives it a heart attack outright.
There is no better time to refactor than when you get it to meet requirements. If you don’t start refactoring right away, you’ll forget all of the nuance and context involved with the code, you probably won’t remember how/why you arrived at the code that’s there now, and you may not even get permission to spend time on it later. Future You will thank Present You for it.
THE FIX: Buffer your development time to include refactoring, and make sure you have a performance goal in mind. For example: “We need this procedure to return the top five recommendations for the specified account in under 1500 milliseconds.”
Do you care if your query results are wrong? No? Congratulations, NOLOCK might be right for you!
The trouble with NOLOCK is twofold: developers usually don’t fully understand the risks involving dirty reads, and when they do, they often leave it in code because it’s better to risk sketchy results than move back to the suburbs of Slowville.
There are appropriate circumstances for NOLOCK, but developers often add it blindly as a performance enhancer, not a legitimate solution.
THE FIX: If the risks that come with NOLOCK aren’t acceptable, you can usually fix those performance problems either with code or index changes. For example, if a SELECT and UPDATE are fighting over the same index and locking each other out, an index tailored to the SELECT statement will prevent it from waiting for the UPDATE to release its lock.
Cursors do terrible, terrible things to performance. Every developer has this moment of enlightenment at some point. As soon as we learn cursors are performance killers, we hunt them down like a starving owl in a field of mice. We shouldn’t be embarrassed that we wrote them in the first place; we were solving the problem the best way we knew how at the time. Still, there’s a universal sense of shame when one developer sees another developer’s cursor, as if the neighbor passed by our open garage and spotted the recycling bin full of cheap beer cans and Cat Fancy magazines.
Like NOLOCK, there are times it’s appropriate to use a cursor, but those occasions are very rare.
THE FIX: Write code that operates in sets, rather than one row at a time.
- Overuse of dynamic SQL
- Security by obscurity
- No indexes
- Incorrect data types, leading to implicit conversions
- Not following their own best practices
- The same mistakes they made two years ago
- Hundreds of columns in a table
As a developer, one of the things I can no longer live without is a date table. Who wants to type DATENAME and DATEPART over and over again? Not this guy, and once you have a date table, you won’t want to either.
In this 16-minute video, I’ll give you four reasons why you should stop writing so many date functions and concatenations, and start looking them up in a table instead.
In this video, Doug talks about how changing a column’s data type in SQL Server can be dangerous when there are Access clients using it, and a reference table to help avoid type mismatches when planning data type changes.
Click here to see the list of SQL Server data types according to Access.
Tune in here to watch our webcast video for this week! To join our weekly webcast for live Q&A, make sure to watch the video by 12:00 PM EST on Tuesday, September 23! Not only do we answer your questions, we also give away a prize at 12:25 PM EST – don’t miss it!
It’s hard to keep up with what your Report Server is doing, especially if your only tool is Report Manager. Now there’s an easier way, using the newest member of the sp_Blitz family: sp_BlitzRS! In this webcast, you’ll learn how sp_BlitzRS can help you stay informed of how well your SSRS installation is running, who gets what subscriptions, which reports would benefit from preprocessing, and more!
Have questions? Feel free to leave a comment so we can discuss it on Tuesday!
In this 5-minute video, Doug tells about the time he deleted the Reporting Services encryption key, and what it took to fix the damage. You’ll learn about what the SSRS encryption key protects, as well when to:
- Back up the encryption key
- Restore the encryption key
- Change the encryption key
- Delete the encryption key
In this 5-minute video, Doug explains how he kept filling SQL Server’s transaction log during ETL development, and how minimal logging could have prevented a lot of the log writes. You’ll learn about:
- What minimal logging is
- How to turn minimal logging on
- How to tell if minimal logging is working
- Using minimal logging for schema changes
- Words of caution about minimal logging
In this 5-minute video, Doug tells a story about a DBA whose SQL Server got fed up with being misconfigured and decided to do something about it. Topics include CPU settings like MAXDOP and Cost Threshold for Parallelism, memory and disk configuration.
You’re a developer, and you want to know:
- What do DBAs value?
- What do DBAs need from me?
- What does my DBA want to help me with?
- How big should that server be?
- Is the DBA judging my code?
- What happens if I get in trouble with my DBA?
Learn the answers in this 30-minute video: