Good Questions for SQL DBA Applicant

SQL Server Administration
1

HI experts,
Do you have a few good questions that could be asked of a SQL DBA applicant during the interview?
Maybe a mix of administration questions along with a couple of development questions as well.
Thanks for any suggestions.

2

Assuming the DBA is expected to have some level of experience:

  1. What is the difference between a view and an index? (A detailed DBA might point out that they can be the same thing, i.e., an indexed view).

  2. How do you get information about what SQL Server considers to be missing indexes? (If they say "query plan", ask them how to get it outside of a specific query.)

  3. When should you consider data compression for a table/index?

1 Like
3

Thanks @ScottPletcher Yes we want a good amount of admin experience, not so much in the development area. Thanks for your suggestions.

5

Does anyone else have any ideas?

6

I'm a little confused here. Your handle is "DBAforever". I don't mean for this to sound snarky and it's not intended that way but what is preventing you from coming up with your own set of questions?

As for what to ask about, I'd concentrate a whole lot on security both at the database level and the server level and how Windows Active Directory Groups come into play. If this is actually for in the cloud, I would imagine that there are similar tools there.

As about xp_CmdShell and how to correctly set it up and how to correctly use it. It's not a security risk... the people that set it up and allow its usage incorrectly are the only security risk. People CAN run stored procedures that run xp_CmdShell and other goodies without those people being able to run xp_CmdShell themselves. NEVER grant individual users the privs to execute it, PERIOD. There's lot's more on that an related subjects and it's very worth while exploring even if you don't ask questions about it.

As about things like what the "Trustworthy" setting on databases is for, what it provides, when is should and should not be used.

Ask about the "Best Industry Wide Practices" about who should own databases and jobs in SQL Server Agent and why.

Ask about backups AND restores. For example, what settings can be used to make backups faster even for single file backups, what can make them more sure, and how to automate test restores every night.

Ask about DBCC CheckDB and things like the "Suspect_Pages" table and how that provides an "early warning".

I would also ask who ever does interview to explain why Microsoft removed the old "Reorg between 5 and 30% logical fragmentation and rebuild after 30" and what their plan to do instead is. If they need a couple of days to study that, it'll be worth it for you folks. Those old supposed "Best Practices" were never meant to be "Best Practices". The guy who came up with those numbers, Mr. Paul Randal himself, said so way back in 2009 in the following article... and, yep... most people have been doing index maintenance wrong for decades starting with SQL Server 2000.

You should also ask about server side and database configuration settings like Cost Threshold of Parallelism, Max Degree of Parallelism, back settings both for performance and integrity and how to make file groups for lobs instead of storing them in the Primary file group. Also ask them how to REBUILD a really large index without leaving the same amount of FREESPACE on the file (hint... temporary swap'n'drop filegroup and file and REBUILD with DROP EXISTING).

Also ask them why Row Compression can create massive fragmentation problems where there were non on the table before and what other compression that would affect.

Also, if you want someone that has done some studying, ask them how REORGANIZE really works and why it should NEVER be used on Random GUID indexes and why converting Random GUIDs to NewSequentialID is actually a pretty bad idea in most cases. And don't guess at the answer... if you don't know, the watch the following....

I agree with the others about asking about indexes. They do need to know.

The DO also need to know some T-SQL so they can, at least, figure out which tools to NOT use and understand what things like DBATools does without getting into trouble.. Unless you folks have a full up monitoring tool, they going to need to know how to "do stuff".

Then, there are things like how to setup a new database and do restores of a single database to a point-in-time or how to possibly recover a single table. Ask them how they find the "worst" queries and how they usually differ from the "longest running" queries. As them about sp_WhoIsActive and what they could look at in SQL Server to find the top 10 worst CPU guzzing and IO using queries without using any code!

There's a raft of other questions for good DBA interviews and discussions.

I almost never use Extended Events but that might be a good question to ask. If they don't use Extended Events and use something else (like I do), ask them what they do.

Also, ask about famous authors. If a non-developer DBA doesn't know about Brent Ozar and some of the other heavy hitters, be very careful.

There's so much more to a DBA interview... I'll say do a search for some DBA interview questions specifically for SQL Server. If you don't know the answers to the questions, don't necessarily believe the answers because some of the people writing those things are so far out in left field, you couldn't see them in the dark if you set their clothes afire. :smiley:

And, again... if you aren't really qualified to conduct such an interview, recommend that you hire a known good DBA for a few hours to conduct the interviews for you.

3 Likes
7

Thanks Jeff. We hired our 3rd DBA last week. But I'm looking to add to my list of questions for future reference. My list already includes dozens but you (and Scott) have given me some good ones to add.
We can close this thread.