In my role as a consultant, it’s rare that I go on an interview anymore, though I occasionally get interviewed by a client, or interview potential DBAs for clients as part of my role. There are a number of these lists on the internet, but many of them are old and focus on trivia questions (there won’t be any questions on what’s clustered versus nonclustered index, but if you are interviewing for a senior role, you should know that anyway. I also like to focus on open ended questions, to gauge the depth of knowledge of the person I’m interviewing.
Here are some sample questions, and explanations:
What does TempDB do in SQL Server? This is probably my all time favorite SQL Server question, because almost anyone can answer it correctly, but at the same time I can gauge someone’s knowledge of SQL Server from a 100 level to “I worked on the product team and wrote part of the tempdb code” depending on the answer. The basic right answer is “stores temp tables” and the senior dba right answer involves spills, isolation levels, and version stores.
What would you change on a default (e.g. next, next, next, finish) installation of SQL Server? The answer to this question has changed in recent years as the product team has implemented most of the best practices around TempDB, MaxDOP, and Max Memory into setup (bonus points if they know which version those things were implemented in (TempDB (2016), MaxDOP (2019), and Max Memory (2019)). Other correct answers include enabling IFI, optimize for ad hoc, changing file locations, changing default growth rates. (The really good senior dba answer, is that I used DBA tools or a T-SQL script to automate my setup based on the machine size).
I didn’t want to make this a full list of questions and answers, but I will include a number of questions. You can go find the answers, or ask for help in the comments.
“Microsoft has a number of Platform as a Service database options in Azure. Can you tell me what those options are and a good example of a use case for each of those options?”
“Tell me about the various options for encrypting data in SQL Server, and how would you use each of them?”
“What are some system waits you have observed, and how what tuning steps did you take based on those wait statistics?”
“Tell me some ways you might improve backup and restore performance?”
“What types of workloads could benefit from a columnstore index and what are the tradeoffs?”
“How would you configure storage for a SQL Server VM running in Azure”
“Can you walk though the high availability and disaster recovery options, and the benefits and tradeoffs of each of those?”
“What is your methodology for troubleshooting a query that suddenly decreased in performance?”
“How would you diagnose a storage performance problem versus a poorly performing query?”
“What are the possible causes of database corruption?”
“You need to deploy a change to 3000 SQL Servers, tell me about your methodology?”
“How do you manage performance metadata across a large number of servers to provide baseline performance information?”
This is just a start–feel free to add questions in the comments or ask for help.
