Please read the Introductory Post before continue reading interview question and answers.
Malathi Mahadevan who is known SQL Server Expert has written following guest blog post to keep alive the spirit of Interview Questions and Answers Series. I encourage all the readers to read her excellent blog and follower her on twitter.
One of the questions i was asked – and a regular at most interviews where i work is
‘What is the toughest challenge you have faced at your present job and how did you handle it’?
Before looking at various responses I will explain some reasons why this question is asked…#1 reason is that we don’t believe in grilling a person technically…it is the one thing most people dread in interviews, and many times people who even know the subject well get totally flustered and upset if they cannot answer something.On the other hand it is vitally important to test an individual’s technical skills, especially when the company cannot afford a big learning curve, and person needs to get started on the job right away on production system. The easiest way to gauge a person’s expertise is to get them talking about what they did…they may not know all that they are needed to know but as an interviewer one can clearly understand how much and what this person is capable of.
Always try to be honest about this question. There is definitely a risk involved, particularly if you are a junior DBA trying to transition into a more senior level..and the company is looking for senior level expertise already. Experience shows – in the technical jargon we use, how comfortable we are describing what we did, in everything. Sometimes just the way you answer can impress the interviewer and get you the job. But never try to pass off something you read or someone else’s experience as your own. You may not be able to get very far with that, and it rarely fools a smart interviewer.
Some answers I have given and heard:
1 I had to do an in-place upgrade on a critical production system overnight. It was an active active cluster on SQL 2005 and was upgrading to 2008. I had to read clearly the differences between clustering installation in both and had to be very sure i understood. After going through test installs we decided this was too risky and went with new hardware, new os, it was still an in place install since they required us to have the same server name. So had to extensively document the older system and rebuild the new system from scratch overnight.
2 Had two fellow dbas leave in quick succession, had to handle work of 3 dba’s. Learnt how to multi task and automate monitoring, buy and use tools…Also automated the dba daily checklist to the extent possible so that i dont have to manually check processes running, event logs, job failures and so on. Used powershell/vbscript to achieve this.
3 Had to convince application team to learn and use SSIS instead of TSQL for several ETL processes. Ther was lot of resistance as SSIS was not easy for them to learn and they were very used to writing TSQL code. But i had to arrange several demos and show them advantages – particularly in terms of performance with data transforms, eliminating the need for intermediate tables, filtering bad data and exception handling. Had to talk to the manager and migrate couple of packages initially, did it all myself..when they saw how it performed they started learning it and came on board totally. The main lesson i learnt was to understand their resistance and deal with it maturely instead of forcing opinions.
4 Had a database that was using GUIDs heavily and suffering performance issues due to high fragmentation. Tried to mitigate the issue by reindexing more often – to some extent it helped. Then we moved to sequential GUIDs and it got little more better. But the main issue i had was to force them to adopt integer keys – they kept insisting it would result in i/o hotspots and this and that..but proved that wrong too by recreating the database using integer keys and using multiple files to avoid contention. When they saw performance improvement they were sold!
In some cases you may be allowed a few minutes to describe the situation like this and answer – in some cases interviewee would just cut you short and say ‘ok, what did you do for this or that’, or ‘what if they still refused to moved to integer keys instead of guids’ and so on…the best way is to practice the answers at home repeatedly, perhaps have a friend or family member stop you at every sentence and ask questions. Also try to talk easily and fluently in technical terms, stammering with technical terms is often times a clue to what the person knows and does not know.
Reference: Malathi Mahadevan, Pinal Dave (https://blog.sqlauthority.com)