You Need to know these DBA SQL Server Interview Questions
Questions that’ll get you through the initial phone interview
My technical recruiter friends have emailed me the top questions they ask DBAs
Below is a compilation of those questions. I’ve made the answers in simple bullet points.
Print this page and have it handy for your phone interview. Think of it as a quick reference guide, so you don’t get stuck.
Note, they normally ask only 3 or 4 of these questions on a 1st or 2nd phone/video interview.
DBA Interview Questions & Answers
Q1: Can you explain the different types of database backups available in SQL Server?
Full Backup: This involves backing up the entire database, including part of the transaction log so the full backup can be recovered.
Differential Backup: This involves backing up only the data that has changed since the last full backup.
Transaction Log Backup: This involves backing up just the transaction log. It allows you to restore a database to a point in time.
Copy-Only Backup: This is a special type of full backup that doesn't affect the state of ongoing backup sequences.
Q2: What is a deadlock and how can it be avoided?
A deadlock is a situation where two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.
Deadlocks can be avoided by ensuring that transactions access resources in a consistent order, thus avoiding a circular wait.
Using the READ COMMITTED isolation level can help minimize the occurrence of deadlocks.
SQL Server can automatically detect and resolve deadlocks, choosing one process as a victim and allowing the other process to continue.
Q3: Can you explain the ACID properties in a database system?
Atomicity: This ensures that a transaction is treated as a single, indivisible unit, which either succeeds completely, or fails completely.
Consistency: This ensures that a transaction brings a database from one valid state to another valid state.
Isolation: This ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.
Durability: This ensures that once a transaction has been committed, it will remain committed even in the case of a system failure.
Q4: What is the difference between a clustered and a non-clustered index in SQL Server?
A clustered index determines the physical order of data in a table. There can only be one clustered index per table.
A non-clustered index doesn’t sort the physical data inside the table. Instead, it creates a separate object within the table that contains the index.
Clustered indexes are faster to read than non-clustered indexes, because they directly reference the data.
Non-clustered indexes are ideal for columns frequently involved in search and filter operations, but not necessarily in a specific sorted order.
Q5: How do you monitor the performance of a SQL Server instance?
SQL Server provides several tools for monitoring performance, such as Activity Monitor, SQL Server Profiler, and Performance Monitor.
Dynamic Management Views (DMVs) and Functions (DMFs) can provide valuable information about the health of a server instance.
Query Store feature in SQL Server allows you to track query execution plan performance over time.
Regularly checking and analyzing server logs can also help identify potential issues.
Q6: What is the purpose of the tempdb database in SQL Server?
The tempdb is a system database and it's available for the life of a SQL Server session.
It holds all temporary tables and temporary stored procedures. It also fills in whenever SQL Server is sorting data for processing.
It's recreated every time SQL Server is started so the system always starts with a clean copy.
As the tempdb database is heavily used, how it's configured can affect the performance of a SQL Server instance.
Q7: What is log shipping in SQL Server and why is it used?
Log shipping is a technique used for automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server.
It allows for a simple form of disaster recovery, and can also be used for database mirroring and replication.
The primary server logs transactions and creates a transaction log backup. The secondary server then restores the log backup and applies the transactions.
Log shipping provides a way to keep a backup server and a way to offload query processing from the primary server.
Q8: How does SQL Server handle transactions and what is the role of the BEGIN, COMMIT, and ROLLBACK commands?
Transactions are sequences of operations performed as a single logical unit of work.
A transaction begins with a BEGIN TRANSACTION statement. This signals the start of the transaction.
The COMMIT command saves all transactions to the database since the last BEGIN TRANSACTION statement.
The ROLLBACK command rolls back an uncommitted transaction to the beginning of the transaction, or to a savepoint inside the transaction.
Transactions can help maintain data consistency and recover from errors or failures.
Q9: What are the different types of replication in SQL Server and what are they used for?
Snapshot Replication: Takes a snapshot of the data on one server and moves that data to another server. It's best for small amounts of data which change infrequently.
Transactional Replication: Starts with a snapshot, and then subsequent data changes and stored procedure executions are replicated as they occur. It's best for maintaining copies of data.
Merge Replication: Changes from the publisher and subscribers are tracked, and then merged together. It's best for mobile applications or distributed server applications that have possible data conflicts.
Q10: Can you explain what a SQL Server Agent is and what it is used for?
SQL Server Agent is a component of Microsoft SQL Server that is used to automate tasks.
It runs scheduled jobs, monitors SQL Server, and processes alerts and operators.
Jobs can be set to run on a schedule, in response to a specific event, or on demand.
SQL Server Agent uses SQL Server to store job information, and it's integrated with SQL Server Management Studio.
It's an essential tool for managing and executing administrative tasks in SQL Server.
Q11: What is a database schema and what role does it play in SQL Server?
A schema is a collection of database objects, including tables, views, indexes, and procedures, associated with a database.
Schemas provide a logical classification of database objects and act as namespaces to ensure the uniqueness of these objects within a database.
It helps in managing permissions at a more granular level by allowing different database roles to have different access to certain schemas.
Schema changes can impact the overall design and performance of the database system.
Q12: Can you explain the purpose of the SQL Server Profiler?
SQL Server Profiler is a graphical user interface that allows system administrators to monitor events in SQL Server.
It helps troubleshoot problems in SQL Server by capturing and storing data about each event to a file or a SQL Server table to be analyzed later.
Events include specific occurrences such as the execution of a SQL Server process or changes to data in a table.
Profiler can help find performance bottlenecks, monitor the performance of SQL Server to tune workloads, and audit and review past activities.
Q13: How does SQL Server handle security and authentication?
SQL Server supports two authentication modes: Windows Authentication and SQL Server and Windows Authentication mode (Mixed mode).
Windows Authentication leverages Active Directory user accounts or groups when connecting to SQL Server, and it's the default and recommended method.
Mixed mode supports authentication both by Windows and by SQL Server.
SQL Server uses a role-based security model. Permissions can be granted at different scopes (server, database, schema, and object level).
Data encryption options, such as Transparent Data Encryption (TDE), can be used to protect sensitive data.
Q14: What is a SQL Server cursor and when would you use one?
A cursor in SQL Server is a database object used to retrieve rows from a result set one at a time, instead of the T-SQL commands that operate on all the rows at one time.
Cursors are used when rows need to be updated or processed individually.
There are four types of cursors in SQL Server: dynamic, static, forward-only, and keyset-driven.
Cursors can be resource-intensive and slow compared to set-based operations. They should only be used when necessary.
Q15: How does SQL Server use the Buffer Cache?
The Buffer Cache is a portion of memory that holds data pages that have been read from the disk.
SQL Server uses this to minimize disk I/O and improve database performance because accessing data in memory is faster than disk.
The Buffer Manager is responsible for moving pages in and out of the buffer cache.
It's important to monitor buffer cache hit ratios to ensure SQL Server is effectively using memory.
Q16: Can you explain the concept of a fill factor in SQL Server?
Fill Factor is a setting that can be configured at the index level and is used to control the amount of free space left in a page when creating or reorganizing an index.
It determines the percentage of space on each leaf-level page to be filled with data, reserving the rest as free space for future growth.
A lower fill factor will leave more space in the index pages which reduces page splits, but increases the size of the index.
Choosing the correct fill factor can have a big impact on performance.
Q17: What are SQL Server statistics and why are they important?
SQL Server statistics are a collection of data about the distribution of values in one or more columns of a table or indexed view.
The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result, which enables the optimizer to create a high-quality query plan.
They are created automatically when an index is created on a column, or they can be created manually.
It's important to keep statistics updated for the query optimizer to make good decisions.
Q18: What is a Linked Server in SQL Server and when would you use one?
A Linked Server in SQL Server is a reference to a server and database outside of the current instance.
It allows you to run distributed queries to work with data from multiple remote data sources.
You can configure a SQL Server instance to connect to other SQL Server instances or other database servers like Oracle, Access, and Excel.
Using linked servers can simplify the management of data in different databases, but it must be done carefully due to potential security and performance issues.
Q19: How would you handle database corruption issues in SQL Server?
First, identify the corruption using tools like DBCC CHECKDB, which checks the logical and physical integrity of all objects in the specified database.
If possible, restore the database from a good backup. This is the safest and fastest way to handle corruption.
If no backup is available, you can use the REPAIR option of DBCC CHECKDB but it might result in some data loss.
Prevent future corruption by implementing a good backup strategy, regularly checking for hardware issues, and monitoring SQL Server error logs.
Q20: What is the role of a Database Compatibility Level in SQL Server?
The Database Compatibility Level corresponds to the version of SQL Server with which the database is supposed to be compatible.
It determines certain database behaviors to be compatible with the specified version of SQL Server.
It allows a database to be migrated to a newer version of SQL Server without changing the application.
It's crucial to test the application thoroughly when changing the Database Compatibility Level as it might affect performance and behavior.
Q21: How does SQL Server implement data compression?
SQL Server provides two types of data compression: Row Compression and Page Compression.
Row Compression changes the format of the data stored in rows to use less space. It's a non-lossy operation, meaning no data is lost during the compression.
Page Compression is a more advanced level of compression, which includes row compression and also compresses multiple rows on a data page.
Compression can save disk space, reduce I/O, and improve performance. However, it uses more CPU.
Q22: What is a Database Snapshot and when would you use it?
A Database Snapshot is a read-only, static view of a SQL Server database (the source database). The snapshot is transaction-consistent with the source database as of the moment of the snapshot's creation.
Database snapshots can be used for reporting purposes, to safeguard data while users are making changes, and to recover from a user error.
When a database snapshot is created, it's empty and grows as changes are made to the source database.
It's not a replacement for a database backup, as it resides on the same server as the source database and will be lost if the server fails.
All the Best.