Wednesday, March 11, 2015

Software Interview Nightmares

Nowadays it seems that the bigger the company is, the bigger the paranoia to hire one of these Secretly  Terrible Engineers. In a desperate attempt to filter out these STEs, interviewers tend to always ask the same old, back-to-basics,  algorithms/data-structures related questions, even when interviewing experienced engineers. Unfortunately, as recognized by many (including Google, Facebook), this interview strategy result in a significant false negative ratio.

Software companies try to improve this awful ratio by asking/expecting engineers to prepare to these interviews by reading books like “Cracking the  Coding Interview” and practice weeks or months on solving problems (in most  cases) completely unrelated to their daily work and past experience. .

To be honest, ever since I joined Microsoft, I’ve based most of my interviews on these questions. I wrote down all the questions (and  answers) that I have asked and being asked, it’s all right here: Get Ready for Software Interview.
As an interviewer, algorithms questions are the easiest to ask. You don’t have to sweat nor think too much. You know the questions and  answers by heart, and you are always surprised to see engineers with great resume struggle when they try to solve these questions with their back against the whiteboard (except for the ones that solved a similar question in their recent past).

What I have learned, mainly by watching internal candidates that we rejected thrive in other teams, is that cutting off developers based on their ability to quickly solve algorithms questions on the whiteboard makes you pass on the best of talent, the talent that you are so desperately looking to find. Sadly, other than my personal experience and conversations with my colleagues, I have no solid data to prove this. It's easy to collect data on the success/failure of engineers that got hired, but it's extremely hard to measure the % of good talent that a given team missed on.    

However if you ask me, hiring managers should know better by now. Instead of settling on these awful odds, they should refresh their interview strategy. They should hand pick the interviewers based on the candidate current set of skills. They should guide the interviewers to focus less on whiteboard coding (come on already!) and more on good old, one-on-one software related conversations. A good interviewer, that has similar background as the interviewee, should be able to asses the quality of the latter simply by talking with him/her for 15-30 minutes. Show the candidate existing code and see if he/she can tell what's wrong with it and how it can be improved. Talk about his/her past experience and check if he/she became an expert in the areas that he/she worked on. Every single interviewer that participate in the interview loop should carefully read the candidate resume and ask coding questions related to the latter past experience! It makes little sense to base an interview on algo questions when interviewing an engineer that spent the last 5 years building web applications focusing on HTML and java-script (and then rationalize the no-hire decision on his/her lacking 'core' engineering skills)

Some of the best developers I know have degrees in Electronics, Physics and Art (some don’t have a degree at all). They have been developing software since puberty. They are passionate about it. It’s their hobby. They would work for free. Some of them don’t know what’s the Big O of Merge Sort (god forbid!), but they have been rockstars in every company that they worked on, the  kind of talent that you don’t want to miss.

If you search for topics currently asked in Software interviews, you will find the following: binary search, tree traversal (pre/in/post), sorting   algorithms (merge/quick/and some O(n^2) ones), recursion/iteration, graph   search, dynamic programming, breadth first search, depth first search, stacks,   queues, hashtables, heaps, priority queues, and linked lists   (single/doubly/circular).
We expect all candidates to solve these questions on the spot, under  pressure, irrespective of their past experience.
The problem is that 95% of  developers don’t get a chance to implement most of the above in their daily work. They reuse existing algorithms or services encapsulated nicely in most modern frameworks. So, you are optimizing your interview to find the 5% that implement these algorithms in  their daily work, plus 5% collage grads that just finished the Introduction to  Algorithms class, and maybe another 20% that spent couple of weeks preparing to these interviews.
The rest 70% are in serious disadvantage. They might or might not pass your tests. It’s more than likely that hiding in this group are the 100x multipliers, the ones that can ramp up quickly on the most complex codebase, the ones that write beautiful and maintainable code, the ones that can design, the ones that can test, the ones that can lead, the ones make the difference between  successful and unsuccessful  projects. Isn’t that what you are looking for?!

Having said all that, I have no fantasy that interviews will stop using the  whiteboard so extensively any time soon. It's just too easy. Plus, software  engineering is spread across so many areas (web, mobile, SQL, OO, concurrency,  distributed systems, cloud, big data, etc) - that algorithms seems like the only common denominator and the best measure for engineers 'core' engineering skills. Just that it isn't.

The above is from my Quora answer to Has Cracking the Coding Interview made it more difficult for recruiters to evaluate software dev…

Saturday, January 24, 2015

Could not obtain exclusive lock on database 'model'? Here’s how you find the smoking gun..

If your team owns a process that provision SQL Server databases in masses, you probably encountered the below exception in one of your test clusters, or even in production:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for Database '***'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

This happens because when your process attempted to create a database, some other process was using the ‘model’ database. It’s possible that someone started a session with ‘model’ via SQL Server Management Studio, or maybe a SCOM process was using it.

Anyways, the important thing is that you find the smoking gun to unlock your process and make sure that this doesn’t happen again...

If you can repro the problem, you can find the culprit simply by running: ‘EXEC sp_who2’, you will get a list of all the processes/users that use any database in your server, including the ‘model’ database.

Or, you can run the following query in order to get info on the processes that use the ‘model’ database:

(    SPID INT,
    Status VARCHAR(MAX),
    HostName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
INSERT INTO @Table EXEC sp_who2 
SELECT * FROM  @Table WHERE DBName='model'

Alternatively, you can use:

SELECT nt_domain, nt_username, program_name, cmd, status 
FROM sys.sysprocesses 
WHERE DB_NAME(dbid)='model'

I prefer the last option. The results will look something like this:


However, if your process is running somewhere in the cloud, it might be helpfully to get the blame list programmatically. Here’s a sample code that detects ‘model lock’ type error and adds details on the culprit processes to the exception.

    class Program
        static void Main(string[] args)
            var builder = new SqlConnectionStringBuilder()
                DataSource = "localhost",
                IntegratedSecurity = true

            var sqlConnection = new SqlConnection(builder.ConnectionString);
                Server server = new Server(new ServerConnection(sqlConnection));

                string name = "test" + Guid.NewGuid().ToString("N");
                Console.WriteLine("Creating db " + name);
                Database database = new Database(server, name);
                    Console.WriteLine("No Failure");
                catch (FailedOperationException e)
                    HandleModelLockException(server.Databases, e);
            catch (Exception e)

        private static void HandleModelLockException(DatabaseCollection databases, FailedOperationException e)
            bool modelLockException = ModelDatabaseLockedAnalysis.IsModelLockException(e);

            if (!modelLockException) return;

            string message = ModelDatabaseLockedAnalysis.Analyze(databases);
            var s = FormatErrorMessage(message);
            throw new ModelDatabaseLockedException(s, e);

        private static string FormatErrorMessage(string message)
            var builder = new StringBuilder();
            builder.AppendLine("Failed to create database since the system database 'model' is locked. " +
                               "Here's a list of processes that currently use the 'model database:");
            builder.AppendLine(message.Replace(" ", string.Empty));
            string s = builder.ToString();
            return s;

The code above creates a database, incase of an error it calls HandleModelLockException. The latter check if the exception (or one of its InnerExceptions) indicates ‘model locked’ type of error. In that case, it calls ModelDatabaseLockedAnalysis.Analyze that queries for the processes that use the ‘model’ database. Than, it simply add the list to the exception.

Here’s the implementation of ModelDatabaseLockedAnalysis:

    public class ModelDatabaseLockedAnalysis
        private const string query =
            "SELECT nt_domain, nt_username, program_name, cmd, status FROM sys.sysprocesses WHERE DB_NAME(dbid)='model'";

        public static bool IsModelLockException(FailedOperationException e)
            const string messageIndication = "lock on database 'model'";

            Exception exception = e;
            while (exception != null)
                bool modelLockException = exception.Message.ToLower().Contains(messageIndication);
                if (modelLockException)
                    return true;
                exception = exception.InnerException;
            return false;

        public static string Analyze(DatabaseCollection databaseCollection)
            var masterDb = databaseCollection["master"];
            DataSet dataSet = masterDb.ExecuteWithResults(query);
            IEnumerable<LockingProcessInfo> processInfos = Parse(dataSet);
            var builder = new StringBuilder();
            foreach (var processInfo in processInfos)
            return builder.ToString();

        private static IEnumerable<LockingProcessInfo> Parse(DataSet dataSet)
            DataTable dataTable = dataSet.Tables[0];
            var list = new List<LockingProcessInfo>();
            foreach (DataRow row in dataTable.Rows)
                string domain = (string) row["nt_domain"];
                string username = (string) row["nt_username"];
                string programname = (string) row["program_name"];
                string cmd = (string) row["cmd"];
                string status = (string) row["status"];
                list.Add(new LockingProcessInfo(domain, username, programname, cmd, status));
            return list;
    public class LockingProcessInfo
        public LockingProcessInfo(string domain, string username, string programname, string cmd, string status)
            this.Domain = domain;
            this.Username = username;
            this.Programname = programname;
            this.Cmd = cmd;
            this.Status = status;

        public string Domain { get; private set; }

        public string Username { get; private set; }

        public string Programname { get; private set; }

        public string Cmd { get; private set; }

        public string Status { get; private set; }

        public override string ToString()
            return string.Format("Domain: {0}, Username: {1}, Programname: {2}, Cmd: {3}, Status: {4}", Domain, Username, Programname, Cmd, Status);

    internal class ModelDatabaseLockedException : Exception
        public ModelDatabaseLockedException(string message, Exception exception)
            : base(message, exception)

In order to test the code, open SQL Server Management Studio, create a new query on the ‘master’ database, and run:

use model

That would lock the ‘model’ database until you close the query window.

Now, run the program and you will get something like:


You can see clearly that the user ‘avezra’ is using the database using SQL Server Management Studio.