Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Technical Interview Questions for a Senior SQL Server DBA


By:   |   Read Comments (8)   |   Related Tips: More > Professional Development Interviewing

Problem

We need to hire a new senior SQL Server DBA.  The SQL Server DBA team will focus on the candidate's technical skills while our HR team will take care of other things, like team match, culture fit, etc. What are some of the questions we could ask to host a quality technical interview to assess a candidate?

Solution

Interviewing is an interesting topic and different interviewers may prefer different questions and approaches. For DBA positions, most of time, I prefer asking questions that can be demonstrated through coding.

There are a few reasons why coding answers is preferred:

  1. Concise and straight: code itself is a language that is less likely to be misunderstood than a narrative description.
  2. Objective and standard: the final answer will be executable code with expected results, so our evaluation on candidates can be more objective.
  3. Higher and stricter criteria: to answer a question by using examples, there is a lot work to do, i.e. conceiving the test case, preparing the test data, coding the script and doing the presentation and explaining the result), a candidate must have real hands-on experience and thorough knowledge on the topic.

The following are some of my favorite questions which are designed to test a DBA's knowledge, and the answers do not require long T-SQL scripts (ie. 50+ lines).

  1. Can you please demo scenarios of SQL Server locks and deadlocks?
  2. Can you please demo scenarios of SQL Server dirty reads, non-repeatable reads and phantom reads?
  3. Can you please demo scenarios of SQL Server nested loop / hash / merge join operations?
  4. Can you please demo various SQL Server locks (S / U / X / IX etc)?
  5. Can you please demo ACID properties of a SQL Server transaction?
  6. Can you please demo the difference between DELETE and TRUNCATE in SQL Server?

In my opinion, these questions are suitable for intermediate to senior DBAs.

Sample Answers to the Questions Above


1. Can you please demo scenarios of SQL Server locks and deadlocks?

Answer: In SSMS, we will open 4 windows, named W1, W2, W3 and W4.

-- In W1, we prepare the data
use tempdb
if object_id('dbo.t', 'U') is not null
 drop table dbo.t;
create table dbo.t (a int primary key, b varchar(30))
go

insert into dbo.t (a, b) values (1, 'hello'), (2, 'world');
go

-- In W2, we start an update trans without committing
-- this session spid = 55
use tempdb
-- demonstrate the blocking scenario

begin tran
update dbo.t set b = 'hello 2'
where a = 1;
--commit tran

-- in W3, do a select, and it will be waiting, this is called blocking
-- this session spid = 56
use tempdb
select * from dbo.t

-- in W4, we can check the locks of the sessions (spid 55, 56) 
exec sp_lock 56
exec sp_lock 55

In the following snapshot, we can see the W3 session (spid 56) is waiting for a Shared Key lock.

SQL Server Blocking

Now we will demo the deadlock scenario, we will use the previous four SSMS windows. We first cleanout W2 and W3, and then enter new code as follows:

-- This is W2, we start an update trans without committing
use tempdb
-- demonstrate the deadlocking scenario
-- first run the first two lines in W2 and then switch to W3 to run the script in W3
begin tran
update dbo.t set b = 'hello 2' where a = 1;

-- after the script in W3 run, return here and run the following select
-- this will cause a deadlock on this session
select * from dbo.t 
where a = 2;

After the update is run in W2, we switch to W3 and run this script.

-- this is W3
-- run the following after run the update in W2
begin tran
update dbo.t set b = 'world 2' where a = 2; -- this will block the SELECT statement in W2

select * from dbo.t with (xlock) where a = 1  -- this is blocked by W2 UPDATE statement
-- return to W2 to run the SELECT statement and see the deadlocking occur

Now we return back to W2 and run the SELECT statement, we will immediately get a deadlock message, and the W2 session is rolled back as shown below:

SQL Server Deadlock

The reason is in the W2 session, the UPDATE is blocking the W3 SELECT while its own SELECT is waiting for a lock currently held by the W3 UPDATE. At the same time, the same thing happened to the W3 statements, i.e. the W3 UPDATE is blocking the W2 SELECT while the W3 SELECT is waiting for a lock held by the W2 UPDATE. This immediately caused a deadlock.


2. Can you please demo scenarios of SQL Server dirty reads, non-repeatable reads and phantom reads?

Answer: When talking about dirty reads and the like, we are actually talking about transaction isolation level and a candidate is expected to fully grasp this basic DBA knowledge.

We will still use the SSMS windows we created before, delete all code in W2 and W3 and re-enter the following code:

-- This is W2, we start an update trans without committing
use tempdb
-- demonstrate the dirty read
begin tran
update dbo.t set b = 'hello 2' where a = 1;

select * from dbo.t where a = 1; -- uncommitted tran for a = 1;

waitfor delay '00:01:00' -- wait for 1 min, so W3 script can finish

rollback 

-- this is W3
-- run the following to show the dirty read
use tempdb
set transaction isolation level read uncommitted
select * from dbo.t where a = 1 -- we will get b='hello 2' which is uncommitted
go

-- this is W3
-- run the following to show the non-repeatable read issue

use tempdb
set transaction isolation level  READ COMMITTED 
begin tran 
select * from dbo.t where a = 1
waitfor delay '00:00:30' -- wait for 30 SECONDS so the W2 UPDATE can finish
select * from dbo.t where a = 1
commit tran
-- after run the script, immediately switch to W2 and run the update
go

-- This is W2, we start an update trans without committing
use tempdb
-- demonstrate the non-repeatable read

update dbo.t set b = 'hello 2' where a = 1;

In W3, we expect the two SELECTs to return the same data, in reality it does not as shown below:

SQL Server Dirty Reads

This non-repeatable read can be easily solved if we set the transaction isolation level to REPEATABLE READ in W3, so the W2 UPDATE will wait until the W3 transaction is done.

To demo the phantom read issue, we will do the following:

-- this is W3
-- run the following to show the phantom read
-- solve the issue by
-- set transaction isolation level serializable
use tempdb
set transaction isolation level  REPEATABLE READ
begin tran 
select * from dbo.t where a != 1
waitfor delay '00:00:30' -- wait for 30 SECONDS so the W2 INSERT can finish
select * from dbo.t where a != 1
commit tran
-- after run the script, immediately switch to W2 and run the insert
go

-- This is W2, we insert a record to cause the phantom read
use tempdb
-- demonstrate the phantom read
insert into dbo.t (a, b) values (3, 'phantom read?')

In W3, we see the two exact SELECTs in the same transaction return two different results, i.e. phantom reads:

SQL Server Phanton Reads Example

To prevent phantom reads, we should set the isolation level in W3 to SERIALIZABLE.


3. Can you please demo various SQL Server locks (S / U / X / IX etc)?

Answer: This is pretty straight-forward, but to catch a U lock is a little bit tricky because a U lock is more like a lock in transition, it will automatically switch to a X lock when the condition is right, so to display it I have to use table hint UPDLOCK explicitly to demo it.

use tempdb
set transaction isolation level  repeatable read -- we need this isolation level to make the S lock is held until transaction is over
begin tran 
select * from dbo.t with (updlock) where a = 2; -- U lock on Key a = 2
select * from dbo.t where a = 1; -- S lock on Key a = 1
update dbo.t set b = b where a = 3 -- X lock on Key a = 3;

-- show the lock
select resource_type, resource_description, request_mode, request_type 
, request_status, request_session_id
from sys.dm_tran_locks
where request_session_id = @@spid
order by request_session_id;
rollback tran


SQL Server Lock Types

4. Can you demo ACID properties of a SQL Server transaction?

Answer: I think every DBA has heard of ACID, even if they don't know the exact definition (Atomicity Consistency Isolation Durability). A DBA should be able to show test cases with code.

We will design a test case here by first creating a Salary table.

We will open a new SSMS window and create sample table as follows:

use tempdb
if object_id('dbo.EmployeeSalary', 'U') is not null
 drop table dbo.EmployeeSalary;
create table dbo.EmployeeSalary (Name varchar(100), Salary money check (Salary < 100000) );
go

--populate with two records
insert into dbo.EmployeeSalary (Name, Salary) values ('john', 50000), ('mary', 60000.0);
go

We then create another SSMS window and do the demo to show Atomicity:

-- demo Atomicity
-- say we need to give each person a salary increase of 10%
use tempdb
begin tran
update dbo.EmployeeSalary set Salary = Salary * 1.10 where Name = 'john';
select * from dbo.EmployeeSalary -- you will see john's salary is already updated from 50000 to 55000

waitfor delay '00:00:30' --doing something else, if anything unexpected happens during this time to disrupt this session, there will be no update to anyone

-- let's say the current session id is 55, we can open another SSMS window, and runs kill 55 to simulate this current session is disrupted
update dbo.EmployeeSalary set Salary = Salary * 1.10 where Name = 'mary';
commit tran

-- since the session is killed, nobody's salary is updated, john's salary will be rolled back to 50000 

If this current session is killed before the whole transaction is done, we can do a SELECT * from dbo.EmployeeSalary to see that no one got the 10% salary increase.

To demo Consistency, we can open another window and do the following:

-- demo Consistency
-- say we need to give each person a salary increase of 10%, but by mistake, we update Mary's salary from 
-- Salary * 1.10 to Salary * 11.0;
-- this will make Mary's salary to 660,000 instead of 66,000, and thus violates the business rule that 
-- everyone's salary is less than 100,000
-- so Mary's transaction will not go through.
update dbo.EmployeeSalary set Salary = Salary * 11.0 where Name = 'Mary';

Isolation can actually refer to what we demoed above for DIRTY READs, because we can set the ISOLATION to several different levels to show the transaction behavior, so using the previous example to showcase this ISOLATION feature should be sufficient.

Durability is hard to demo via code, all I can think of is to commit an insert/update transaction then simply kill the SQL Server service or power off the computer (to simulate a system crash) and then once the SQL Server service restarts, search for the inserted/updated records, they should show up. This proves the DURABILITY property, i.e. once committed, it will survive any malfunction of the system.


5. Can you please demo scenarios of SQL Server Nested Loop, Merge and Hash join operators?

Answer: This is to test a DBA's knowledge about why the SQL Server optimizer chooses different join operators and the reason behind the decision.

use tempdb
if object_id('dbo.t', 'U') is not null
 drop table dbo.t
if object_id('dbo.s', 'U') is not null
 drop table dbo.s
go
create table dbo.t (id int primary key, a int);
create table dbo.s (id int, a int);
go

; with X1 as (select 1 as c union all select 1)
, X2 as (select x1.c from X1 inner join X1 x on x1.c = x.c)
, X3 as (select x2.c from X2 inner join X2 x on x2.c = x.c)
, X4 as (select x3.c from X3 inner join X3 x on x3.c = x.c)
, X5 as (select x4.c from X4 inner join X4 x on x4.c = x.c)
insert into dbo.t (id, a)
select row_number() over (order by x5.c), row_number() over (order by x5.c)  from X5

; with X1 as (select 1 as c union all select 1)
, X2 as (select x1.c from X1 inner join X1 x on x1.c = x.c)
, X3 as (select x2.c from X2 inner join X2 x on x2.c = x.c)
, X4 as (select x3.c from X3 inner join X3 x on x3.c = x.c)
, X5 as (select x4.c from X4 inner join X4 x on x4.c = x.c)
insert into dbo.s (id, a)
select row_number() over (order by x3.c), row_number() over (order by x3.c)  from X3

go
set showplan_all on
go
-- "Nested Loops" occurs when one table is small and another big and the big table has its joined column indexed.
select * from dbo.t 
inner join dbo.s on s.id = t.id
go
set showplan_all off
go

use tempdb
drop table dbo.t
drop table dbo.s
create table dbo.t (id int primary key, a int);
create table dbo.s (id int primary key, a int);
go

; with X1 as (select 1 as c union all select 1)
, X2 as (select x1.c from X1 inner join X1 x on x1.c = x.c)
, X3 as (select x2.c from X2 inner join X2 x on x2.c = x.c)
, X4 as (select x3.c from X3 inner join X3 x on x3.c = x.c)
, X5 as (select x4.c from X4 inner join X4 x on x4.c = x.c)
insert into dbo.t (id, a)
select row_number() over (order by x5.c), row_number() over (order by x5.c)  from X5

; with X1 as (select 1 as c union all select 1)
, X2 as (select x1.c from X1 inner join X1 x on x1.c = x.c)
, X3 as (select x2.c from X2 inner join X2 x on x2.c = x.c)
, X4 as (select x3.c from X3 inner join X3 x on x3.c = x.c)
, X5 as (select x3.c from X4 inner join X4 x on x4.c = x.c)
insert into dbo.s (id, a)
select row_number() over (order by x5.c), row_number() over (order by x5.c)  from X5

go
set showplan_all on
go
-- "Merge Join" occurs when both tables are big and the joined columns are indexed / sorted.
select * from dbo.t 
inner join dbo.s on s.id = t.id
go
set showplan_all off
go

use tempdb
drop table dbo.t
drop table dbo.s
create table dbo.t (id int , a int);
create table dbo.s (id int , a int);
go

; with X1 as (select 1 as c union all select 1)
, X2 as (select x1.c from X1 inner join X1 x on x1.c = x.c)
, X3 as (select x2.c from X2 inner join X2 x on x2.c = x.c)
, X4 as (select x3.c from X3 inner join X3 x on x3.c = x.c)
, X5 as (select x4.c from X4 inner join X4 x on x4.c = x.c)
insert into dbo.t (id, a)
select row_number() over (order by x5.c), row_number() over (order by x5.c)  from X5

; with X1 as (select 1 as c union all select 1)
, X2 as (select x1.c from X1 inner join X1 x on x1.c = x.c)
, X3 as (select x2.c from X2 inner join X2 x on x2.c = x.c)
, X4 as (select x3.c from X3 inner join X3 x on x3.c = x.c)
, X5 as (select x3.c from X3 inner join X4 x on x3.c = x.c)
insert into dbo.s (id, a)
select row_number() over (order by x5.c), row_number() over (order by x5.c)  from X5

go
set showplan_all on
go
-- "Hash Match" occurs when two big tables are not indexed on their join columns
select * from dbo.t 
inner join dbo.s on s.id = t.id
go
set showplan_all off
go

If you run the whole script, you will get the following result, and you can see different join operators.

Detailed technical info can be found at MSDN.

SQL Server Join Operators

If a candidate can show you this, it means s/he is very likely to be experienced in performance tuning.


6. Can you demo the difference between DELETE and TRUNCATE in SQL Server?

Answer: Everyone knows DELETE can have a WHERE clause while TRUNCATE cannot, but experienced DBA may tell you more.

For example, I am happy to hear a candidate tell me that if a table is replicated, you cannot truncate it, it would be even better if the candidate can give a reason.

-- prepare environments

use tempdb
if object_id('dbo.tblChild', 'U') is not null
 drop table dbo.tblChild;
go

if object_id('dbo.tblParent', 'U') is not null
 drop table dbo.tblParent;
go

create table dbo.tblParent (id int identity primary key, a varchar(30));
create table dbo.tblChild (id int identity primary key, b varchar(30), pid int references dbo.tblParent (id))
go

-- difference 1, with both tables empty, DELETE and TRUNCATE behave differently
delete from dbo.tblParent -- succeeds,  (0 rows(s) affected)
truncate table dbo.tblParent; -- fails, Cannot truncate table 'dbo.tblParent' because it is being referenced by a FOREIGN KEY constraint.

-- now let's populate some table
insert into dbo.tblParent (a) values ('dad'), ('mom'), ('uncle');
insert into dbo.tblChild (b, pid) values ('son', 1), ('daughter', 2)
go

-- difference 2, DELETE can remove record 'uncle' with a where clause while TRUNCATE cannot
delete from dbo.tblParent where a='uncle'; -- succeeds
truncate table dbo.tblParent where a = 'uncle' -- syntax error

-- difference 3, identity column is reset for TRUNCATE while not so for DELETE

select IDENT_CURRENT('dbo.tblChild'); -- returns 2

delete from dbo.tblChild -- succeeds

select IDENT_CURRENT('dbo.tblChild'); -- returns 2, notice before and after DELETE, the current identity value remains unchanged

-- re-insert the deleted records
insert into dbo.tblChild (b, pid) values ('son', 1), ('daughter', 2)
go
select IDENT_CURRENT('dbo.tblChild'); -- returns 4

truncate table dbo.tblChild -- succeeds 

select IDENT_CURRENT('dbo.tblChild'); -- returns 1, notice before and after TRUNCATE, the current identity value is changed from 4 (before) to 1 (after)

-- other differences, performance, trigger firing, replication-involved table. log size, lock required, parallel work (in delete, another transaction can still insert while 
-- this is not possible, if truncate is ongoing)
-- these may need more sophisticated test environment setup, I just ommit here on purpose.

One theory I'd like to hear is that a TRUNCATE is equal to a DROP and RE-CREATE of a table and with this theory, we can explain most of differences, such as why an Identity value is reset, why a table cannot be truncated if it is being referenced, etc.


Summary

This tip proposes questions for a technical interview for DBAs. It aims to make the interview tough, so only candidates who are strong in theory and hands-on experience will survive.

Because no one can memorize all the T-SQL syntax, a local copy of Books Online is recommended to be available for candidates, but not access to the internet.

Next Steps

All the above-mentioned questions are not difficult to answer verbally for a senior DBA, but to answer via code is not as simple and only those who have rich hands-on experience will stand out.

I purposely left out the SNAPSHOT isolation level in my answer of Q2, so try to code an example that demos the transaction behavior under the SNAPSHOT isolation and explain its benefits.

Prepare your own "codeable" questions for new hires and please share your thoughts on how to conduct quality interviews for DBAs!

The following list of articles provide technical details for the questions we have discussed:



Last Update:






About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, July 14, 2017 - 6:18:41 PM - Uday Back To Top

The query for merge join example has a type in it:

, X5 as (select x3.c from X4 inner join X4 x on x4.c = x.c)

 

Correct syntax:

, X5 as (select x4.c from X4 inner join X4 x on x4.c = x.c)

 


Thursday, January 19, 2017 - 4:05:49 PM - Fred Woolverton Back To Top

 Are these questions aimed at a Database DEVELOPER or a Database ADMINISTRATOR?

 


Thursday, January 19, 2017 - 12:27:42 PM - jeff_yao Back To Top

 Thanks @Louis for your comment.

The "theory" of "drop/recreate table = truncate table" can help to explain lots of things (of course objectid change is NOT included), while "truncate is DDL with alter table privilege" cannot (at least not so obviously). If you use "drop/recreate" theory, you can tell easily why an identity column is reset and why you cannot have a where clause or truncate a table when there is FK reference.

Of course, what is the internal implementation of "truncate table" seems never documented. It would be great if you hear / see anything and can share here with us.

 

 


Thursday, January 19, 2017 - 11:31:50 AM - Louis Back To Top

 For TRUNCATE vs DELETE *, I would prefer the following differences,  TRUNCATE is a DDL command and requires ALTER TABLE privileges, while DELETE is a DML statement. Also, TRUNCATE is not exactly the same as DROP/CREATE TABLE, since the object id will be different. 

 


Wednesday, January 18, 2017 - 11:22:06 AM - jeff_yao Back To Top

Thanks for all your comments @Ishtiaq, @Steve and @Carsten.

@Ishtiaq, I agree performance tuning is a good topic, but for HA/DR and Security, it seems not easy unless we can provide an environment (which may be more complex).

For locking/deadlocking, you actually raise a goog suggestion, i.e. we can ask the candidate to come up with a 2+ blocking chains and then find the root (or head) of the blocking chain.

@steve, SQL Server configuration is indeed an essential skill for DBAs, but all most all configuration can be done via wizard, which means, it can hardly distinguish a senior DBA from a not-so-senior dba.

@Carsten, thanks for your info, I need to play with that in sql server 2016.


Wednesday, January 18, 2017 - 9:41:30 AM - Ishtiaq Ahmed Back To Top

 For any Seinor DBA questions  I would ask questions on (1) Performance tunning (2) HA & DR (3) Seucurity (minimanl) .

 

For Deadlocks & locks question : I would ask for how to find the root cause and fix it like Placing proper Indexes ( by this i will also testing the science of Indexing) and other good pratices of coding. 

 


Wednesday, January 18, 2017 - 9:09:33 AM - steve sofar Back To Top

These questions are more Senior Developper DBA orriented

I would rather ask for SQL server configuration 

demo : DB mirroring

demo : ASYNC replication

Demo : DB snapshot

etc ...

 


Wednesday, January 18, 2017 - 7:49:01 AM - Carsten Back To Top

 
It is different with TRUNCATE TABLE in vers. 2016, if you truncate a partition!

 


Learn more about SQL Server tools