You Need to know these DBA SQL Server Interview Questions

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.