Esat Erkec
SQL interview questions and answer: Trivial execution plan

SQL Interview Questions for the second round

May 22, 2020 by

In this article, we will talk about SQL interview questions and answers that can be asked in the second-round technical stage. These interview questions are based on real-life experiences. Therefore, it can be a very useful source when preparing for the second round of SQL technical interviews.

Many companies conduct a technical interview to evaluate the knowledge of the candidates. Most of the time, this recruitment process does not just complete only one technical interview. The first interview mostly focuses on your capabilities and tries to figure out whether you meet the basic qualifications required for the job. After being invited for the second round of technical interviews, your questions will be very difficult than the previous one. For this reason, you should prepare in-depth questions for this stage. In this article, you will find some SQL interview questions that might be asked for the second stage and detailed answers.

1. What is the lock escalation?

When performing a transaction by SQL Server, the lock manager should lock database objects to ensure the consistency of the database. However, every locked object held by the lock manager consumes 96 bytes of memory. When working with a large number of rows, this circumstance can be required a significant amount of memory to locking the rows. To minimize this memory consumption, SQL Server uses a mechanism called lock escalation. When the row or page locks exceed a limit, lock escalation mechanism converts row or page locks into the table locks so it reduces the memory usage amount.

Now we will create a table and insert 10k row into this table to illustrate the working mechanism of the lock escalation on SQL Server.

In this second step, we will update one row and examine which objects are locked.

SQL interview questions and answers for a second-round interview.

Lock manager places intent exclusive (IX) locks on the data page and tables that contain the rows when the modified rows acquired exclusive locks. Lock Hierarchy in SQL Server starts at the database level and goes down to row-level.

Database –> Table –> Page –> Row

As we can see in the example, the database has acquired a shared (S) lock and this lock type indicates somebody used this database. The table (object) and page have acquired intent shared (IS) lock because the modified row (key) acquired an exclusive (X) lock.

We will now increase the number of rows that have been modified.

Lock escalation working mechanism.

As a result, when we increase the number of the rows that will be modified, the lock escalation mechanism will be triggered and the table will acquire an exclusive lock. This mechanism will eliminate the locked rows and increase memory consumption.

The lock escalation threshold is at least 5000 lock but this number can be changed according to several factors and it does not indicate the exact number. The lock manager considers some parameters such as the number of rows, row size, and the structure of the table to determine this number.

2. Why do we use the table hints in SQL Server and could you give one example about it?

This SQL interview question can be asked to test your knowledge about the query hints. Table hints are used to change the default behavior of the default working parameters so that we can handle the different issues. For example, TABLOCKX hint places an exclusive lock to the specified table until the transaction will be completed. When we look at the following example, the row-level exclusive lock is the default behavior but we will change this lock type to the table level with the TABLOCKX hint.

TABLOCKX hint usage method

To reduce the table’s concurrency is the main disadvantage of the TABLOCKX hint.

3. What is a trivial execution plan?

SQL Query Optimizer doesn’t want to deal with creating optimized query plans for simple queries to avoid time and resources consumption. For example, the query optimizer will generate a trivial execution plan for the following query.

We can see the created execution plan type in the Statement optimization level property of the Select operator.

Trivial execution plan

To disable generating trivial execution plans, we can use the trace flag 8757.

Disabling trivial execution plan

When we disable the creation of the trivial execution plans, the query optimizer has created a FULL optimized query plan and decided that this query requires an index so that it can be faster.

4. What is Batch Mode on Rowstore feature?

This SQL interview question may be asked to test your knowledge of the new versions enhancements of the SQL server.

This feature introduced with the SQL Server 2019 version and it helps to access hundreds of rows in one fetch instead of row by row fashion and it is particularly designed for the aggregation and sort operations. Now, let’s look at the actual execution plan of the following query.

Batch mode on rowstore

As we can see on the select operator properties Batch mode on row store used property is true. It indicates that some of the operators in the execution plan have performed batch mode execution on the row store. Now, we will open the properties of the clustered index scan operator.

SQL interview questions and answer: Trivial execution plan

The clustered index scan operator has accessed the data using the batch mode on the row store.

Batch Mode on Rowstore example

5. What is the functionality of the XACT_ABORT?

When the XACT_ABORT is enabled, if any SQL statement has an error on the transaction, the whole transaction is terminated and rolled back. If we disable the XACT_ABORT, when a statement returns an error, only the errored query is rolled back and other queries complete the operations.

In the following query, we will enable the XACT_ABORT, and then we try to insert a duplicate row, so the entire transaction will be rollbacked.


SQL interview questions and answer: What is the functionality of the XACT_ABORT

We will disable XACT_ABORT in the following query, so all insert statements will be performed successfully except one that raises an error.


How to disable XACT_ABORT?


In this article, we discussed a few SQL interview questions that can be asked in the second-round. These second-round interview questions will be more complicated and difficult. This round will be more challenging as it will include more technical questions rather than traditional interview questions.

Esat Erkec