This article will help you to prepare for SQL Server interviews. I have explained various popular SQL Server interview questions and answers in this article. If you want to get more information, visit all the attached links.
All interview questions have been segregated between the below headings. Please give your feedback in the comment section if you want more articles on additional topics of interview questions.
- SQL Server Architecture
- SQL Server Administration
- Backup & Recovery
- Migration & Upgrades
- Performance Tuning
- High Availability & Disaster Recovery (Always on availability groups, Log Shipping, SQL Server Replication, SQL Server Failover Cluster Instances)
This article will cover interview questions on the first 3 topics and the remaining 3 topics will be covered in the next article of this series.
Before going ahead with the actual SQL Server Interview Questions & Answers, I would suggest you go with the general interview questions as it is important for being shortlisted for your desired job.
SQL Server Architecture
Q: How is data stored in SQL Server?
A: When we create a SQL Server database, two physical files get created to store data and log records for that database. These two physical files are Primary Data File which is also known as the MDF file and the Transaction Log File which is also known as the LDF file. Users can create additional data files that are known as Secondary Data Files i.e. NDF files.
These data files (Primary and Secondary data files) are logically divided into Pages in sequence order. All reads and writes operations are performed at a page level so we can say actual data is stored in SQL Server on Pages (which are 8KB in size) inside these data files. The collection of 8 continuous Pages is known as Extents. All pages are organized into extents that are used to efficiently manage pages.
Q: How does SQL Server know how much free space is available on a page?
A: The fundamental unit for data storage in SQL Server is a page. There are multiple types of pages to store various information for better manageability. One of the page types is known as Page Free Space. The PFS (Page free space) page type records the space utilization of each page. It also records whether a specific page is allocated and how much space is free on each page.
Q: What is Buffer Management in SQL Server?
A: Storing and retrieving data are core characteristics of SQL Server. Reading and writing data on a disk (Disk IO) is a resource-intensive and time-consuming task. Microsoft has designed buffer management to address this issue and create an enterprise-level product like SQL Server to handle the highly transactional workload and complex mission-critical systems easily in a faster way.
Buffer management is a crucial component in SQL Server which helps improve the efficiency of IO operations. It consists of two modules: Buffer manager and Buffer cache. The main functionality of the buffer manager is to read and write database pages whereas buffer cache is used to improve the IO operations by caching data pages in RAM. The buffer cache is also known as buffer pool in SQL Server. Buffer manager reads data or index pages from disk into the buffer cache and writes it back to disk after any modifications.
Read more about understanding memory settings in SQL Server in the attached article.
Q: What are the circumstances under which a CHECKPOINT operation will be initiated?
A: CHECKPOINT is an internal process that flushes dirty data pages from the buffer cache to the database disk. The checkpoint can occur under the following circumstances:
- Automatic checkpoint is being performed internally by SQL Server
- Manually execution of checkpoint operation
- Any bulk operation is performed under the bulk-logged recovery model
- Database files have been added or removed from any databases
- Any database or SQL Server instance shutdown operation
Read more about Database Checkpoint in SQL Server.
Q: Tell me which SQL Server components cannot use more than 64 CPUs?
A: Only SQL Server database engine can use more than 64 CPUs. SQL Server Reporting Services, SQL Server Integration Services, SQL Server Analysis services, service broker, full-text search, and even SQL Server agent cannot use more than 64 CPUs.
Q: What points should be considered during Index designing or recommending Index creation?
A: Index designing requires a lot of considerations and analysis to get an optimum performance result. Below there are some points you must consider while designing or recommending any new Index on your database table.
- Understand the nature of the database whether it is OLTP or OLAP. Several indexes are specifically created considering the nature of the database. If you are a data warehouse or OLAP system, a column store index will add a significant value to your query’s performance whereas if you are running a highly transactional system then memory-optimized tables and indexes could be a good choice. You need to evaluate your query and requirement before recommending any index creation.
- Next thing is to look at your target queries that are running slow and for which you are recommending an index.
- Choose Appropriate Indexes. Cluster or non-cluster or column store etc.
- Analyze Index storage allocation. You can leverage segregating indexes into another filegroup on a different disk or you can consider appropriate partitioning to improve its performance.
Have a look at the attached article to understand Index management in SQL Server.
SQL Server Administration
Q: Can we move master database files from one location to another?
A: Yes, we can move database files of any database including the master database. Moving database files of the master database are different than file movement of other databases. Below is the high-level process you need to follow to move master database files.
- Check the existing location of master database files.
- Identify your target location where you want to place master database files
- Launch SQL Server configuration manager. Open the properties window of the target SQL Server instance by accessing its SQL Server service running in the configuration manager.
- Click at the Startup parameters of the properties window. You can see 3 entries for each file. Path showing with -d is for the data file, -e for the error log file, and -l for the log file.
- Change the database file location as you have identified in step 2
- Stop SQL Server service
- Copy master database physical files (master.mdf and mastlog.ldf) from the existing location to the target location which you set in the above steps in startup parameters
- Once files are copied, start SQL Server service in SQL Server configuration manager
- The last step is to change the SQLDataRoot value given under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\Setup to the new path. Although SQL Server will be successful without performing this step, patches and cumulative updates will be failed if you will miss this registry change.
Read the detailed article in the attached link on this topic to learn its step-by-step process.
Q: What is the difference between running sp_updatestats and UPDATE Statistics statement to update statistics in SQL Server?
A: Both T-SQL statements are used to run update statistics. Even when you run sp_updatestats, it will also run the same T-SQL statement UPDATE STATISTICS in the background. The difference between them is, when you run sp_updatestats it will update statistics for all objects which has at least 1 row modified. It reads modification_counter information in the sys.dm_db_stats_properties catalog view and performs update statistics on all those database objects. You would have the flexibility to update statistics at the object level for a specific object using UPDATE STATISTICS whereas sp_updatestats execute at the database level where it will perform update statistics for all required objects.
Q: What is the significance of tempdb in SQL Server?
A: Although it is known as a temporary database its significance in SQL Server is very crucial. Tempdb is used by all users connected to that SQL Server instance. We need careful planning for its capacity and performance otherwise we might face poor performance for all user connections or even We may face an outage in case there is not enough space available to accommodate all user connections workload. We must plan its appropriate capacity and optimize its performance for mission-critical systems.
Q: Why SQL Server process is taking more memory than the configured value of max server memory server-level configuration?
A: SQL Server acquires memory based on the workload on their instance. Memory configured under max server memory represent buffer pool allocation. The buffer pool is used by SQL Server internal components but there are some external components like linked servers, some dlls, etc. which use their memory allocation outside the buffer pool, and that finally resultant as the additional memory on top of max server memory configuration.
Q: How can we change the collation of a SQL Server Instance?
A: Changing the collation setting of any existing SQL Server instance is a challenging job. We need to rebuild master databases with a new collation setting to change the collation of the SQL Server instance. I would recommend you to read the attached article where I have explained a step-by-step process to change the collation of running SQL Server instances.
Backup and Recovery
Q: Give me 5 reasons for backup failure that is running using SQL Server Agent Job
A: Below there is a list of main reasons for backup job failure running using SQL Server Agent.
- The job owner does not have appropriate permission
- Wrong target backup file path mentioned in the backup job
- Target backup file storage is inaccessible
- Space issue in the backup file location
- Database is inaccessible
Q: I run weekly full backup, daily differential backup at 10 PM, and every 15 minutes transaction log backup. What is the quickest way to recover this database till 8.03 AM if something happened to the database at 8.04 AM after 4 minutes since the last successful transaction log backup?
A: We need to run a tail log backup of the impacted database. If a tail log backup is successful, we can easily recover this database till 8.03 AM considering all backups are healthy and available. If the tail log is not successful and we failed to run any log backup post 8.00 AM, then we have to bear a data loss of 3 minutes and can recover this database till 8.00 AM. Below are the steps to perform the recovery.
- Restore weekly full backup with NORECOVERY mode
- Apply the latest differential back up with NORECOVERY that was taken one day before the issue at 10 PM
- Apply all subsequent transaction log backups with NORECOVERY starting from 10 PM a day before after differential backup till 8 AM
- Now, apply tail log backup mentioning restore point at 8.03 AM in case you have successful tail log backup
Q: Consider the above scenario where you must recover the database till 8.03 AM, but you came to know that the last day differential backup is not healthy and cannot be restored then how will your recovery process impact or there will not be any impact on this database recovery?
A: Database recovery will be impacted if last night differential backup is not healthy. We need to use a day before yesterday’s differential backup and all subsequent transaction log backups since that differential backup till recovery point 8.03 Am considering we have successful tail log backup at 8.04 AM.
Q: Can we restore a backup of the master database taken on the lower version to a higher version of SQL Server?
Ans: No, we cannot restore a master database backup to a different version of SQL Server. Even we cannot restore it on a different patch level of the same SQL Server version.
Q: Explain the steps to restore the master database
A: Restoring the master database is quite different than other databases. We need to first shut down the SQL Server service, then restart SQL Server in single-user mode, and then we can perform the master database restore.
Migration & Upgrades
Q: Tell me high-level steps for SQL Server migration from one server to another.
A: SQL Server migration requires a lot of analysis and planning. You must divide all tasks into 3 parts.
- Pre-migration
- Migration
- Post-migration
The pre-migration phase includes analysis and planning about all aspects like whether our target system is hosted on new hardware or the same hardware from a compatibility and supportability standpoint. Make sure you have appropriate disk types and structures for your database files along with adequate space. Make sure the application has full connectivity to the target database systems. Gather the list of logins, agent jobs, and other dependent objects like linked server, any advanced feature is configured like replication, CDC, auditing, AOAG, etc.
Additionally, you need to finalize your migration approach in this phase whether you want to migrate using backup restore with an acceptable downtime or you want it using AOAG or log shipping or replication to minimize the downtime, etc. Finalize your migration approach considering the cutover time window. Ensure to create run books and documents which will be followed during the actual migration. Capture each step carefully and do not miss anything.
Once all planning is done then the Migration phase will start where we need to perform actual migration based on the decision, we have made in the pre-migration phase. Make sure to run a full backup before starting the actual migration to protect your data. Here you need to perform a cutover where you and the application team will work together to switch all workload to the new system. Make sure to shut down the application or disable all SQL Server logins during cutover to ensure there is no data mismatch.
If you are using backup restore, then take a log backup on the source database after applications shutdown and apply it to your target database with recovery mode. I assume you have already restored your database in no recovery mode along with creating all depended objects like login, linked server, jobs, etc., and waiting for cutover to apply only logs. If you are using AOAG then you need to synchronize both replicas and then failover to the target system and same with other options as well.
The Post-migration phase includes monitoring of systems and fixing the performance and user connection issues.
Q: How do you apply patches on a distributed availability group with minimum downtime?
A: Distributed availability groups are configured between two availability groups. Let’s assume we have a distributed availability group between 2 node replica availability groups AG1 and AG2. AG1 is configured as global having 2 replicas REPLICA1 and REPLICA2 whereas AG2 is configured as a forwarder having 2 replicas REPLICA3 and REPLICA4. If we have to patch this configuration then we will first patch secondary replicas of both availability groups like apply patches on REPLICA4 and reboot this server, then will patch REPLICA2 and reboot the server. Now both secondary replicas from both availability groups have been patched.
Next, we will failover AG2 from REPLICA3 to REPLICA4 and apply patches on REPLICA3 followed by a server reboot. Once this is done then do the same for primary replica REPLICA1, failover AG1 from REPLICA1 to REPLICA2 and apply the patches on REPLICA1 followed by a server reboot.
Now all replicas participating in the distributed availability group have been patched successfully. If you want to failback both availability groups to their original primary replicas, then before you can do that here. Failback AG2 from REPLICA4 to REPLICA3 and AG1 from REPLICA2 to REPLICA1. Now, you are done with the patching distributed availability group configuration. Also, make sure to run a full backup of all your databases before starting this activity.
Q: What are the points we need to take care of before upgrading our databases?
A: We must consider the below points if we are going to upgrade our databases from a lower version to the higher one.
- Make sure applications are compatible with our new target versions
- Target system where the upgraded database is hosted must be accessible to applications in case of side-by-side upgrade
- Make sure you have a full backup of your database in case something goes wrong you can revert them to the previous state
- Gather all details about dependencies of this database like linked server, Jobs, logins, reports, replication, auditing, etc.
- Make sure to capture all database related properties configuration and replicate same to the target system
Q: Can we upgrade a database running on SQL Server 2008 R2 instance to SQL Server 2019?
A: Yes, we can directly upgrade a database running on SQL Server 2008 R2 instance to SQL Server 2019 using the side-by-side upgrade approach. We need to take a backup of the source database that is running on SQL Server 2008 R2 and restore it on SQL Server 209 instance. Make sure to copy all related objects of this database.
Performance Tuning
Q: How do you troubleshoot a performance issue in SQL Server?
A: Performance issue troubleshooting requires a deeper analysis to get it fixed. There are numerous reasons for a database performance issue. It could be slow network issues, network packet drops, application issues, patching, poor code issue, poor database configurations, outdated statistics, or some resource-intensive transaction running on the database, etc. We need to start analyzing the behavior of database transactions and the nature of incidents to isolate the issue by excluding other possibilities and get the correct root cause. We should also track whether an ongoing performance issue is observed post executing any change request or applying any OS or DB patches. If all systems have received a specific change or patch, then you can decide to roll back that change or patch to restore the normal business processing.
Next, we can engage the application team for their analysis of whether they have applied any changes at the application or web layer that is resultant in poor response time. We will also look at database transactions whether there is any blocking, or any poor performing querying is running, or any resource-intensive transaction is eating most of the resources. Here we need to closely work with the development team or application team. Blocking issues can be addressed with the help of the application team whether to kill it or let it run based on the nature of transactions.
If any poor-performing query is identified, then we can gather its execution plan and see the scope of improvement by looking into it.
Parameter sniffing could be another aspect of query regression. We can analyze it using the Query store and fix it by recompiling the impacted object or performing update statistics on the impacted objects. We will gather SQL Server wait types to analyze what was the issue where is most of the resources were waiting. We can also exclude network-related possibilities by looking into network dashboards or engaging a network administrator to ensure everything is fine from their end.
Q: How to improve the performance of the tempdb database?
A: Tempdb database is very crucial to run all database transactions in a healthy manner that is why we must give importance to designing a tempdb configuration. Below there are some high-level points you must consider getting better tempdb performance.
- Tempdb database files must have adequate size and space to accommodate all database workloads
- Enable Instant file initialization to speed up data file autogrowth
- Place tempdb files on distinct drives with the same no of files as you have the number of CPU cores
- Set database file growth increment to the reasonable size to prevent too much auto grow operations
- Configure memory-optimized tempdb metadata for a system running with a high transaction workload
Q: What could be the reason behind having poor IO performance or we can say why IO takes a longer time to complete their operations?
A: Any long-running IO which is pending for 15 seconds will be logged in the SQL Server error log file. Long IO indicates pressure on the disk subsystem. There could be multiple reasons for this:
- Heavy SQL Server workload being performed on the disk subsystem
- Inappropriate disk type to handle exiting SQL Server workload
- Disk latencies because of other reasons like hardware, driver, or firmware issues
Q: What are T-SQL processing modes in SQL Server?
A: SQL Server process any T-SQL statement using either of below two processing modes.
- Row mode execution
- Batch mode execution
In row mode execution, SQL Server processes each row that is being accessed using the T-SQL statement. Row mode execution is very efficient for OLTP workload but it will not give adequate results if you have to process a large number of rows.
In batch mode execution, SQL Server processes multiple rows together in a batch. This mode of execution is very efficient for OLAP workload where a large amount of data needs to be processed.
Q: What do you understand if a specific transaction is showing in a sleeping state?
A: Sleeping state of a SQL Server transaction means work assigned under that transaction has been completed and the connection is waiting for the next command to receive from the client.
High Availability & Disaster Recovery Solutions
Q: What all options are available in SQL Server to achieve high availability and disaster recovery?
A: SQL Server offers various options to achieve high availability and disaster recovery. Below is the list of such features.
- Always on availability groups
- Always on failover cluster instances
- Log shipping
- Database Mirroring
- Replication
- Backup & Restore
Q: What is the difference between availability group and SQL Server replication?
A: Both features are designed for two different purposes.
- Replication is used to replicate data based on your selection of articles at the object level in the database whereas availability groups replicate data for a complete database or group of databases.
- We can control which table will be replicated to the secondary site in replication whereas we don’t have any control at table level in the availability group.
- An availability group supports failover modes whereas replication does not support any failover mode. We can easily switch between replicas in the availability group whereas switching is not possible in replication.
- We need a windows cluster configuration for availability groups whereas replication does not have any dependency on the windows cluster feature.
Q: What is the process to add a data file to a log shipped database?
A: The process of adding a data file to log shipped database is the same as a normal database if both servers (primary and standby) have the same directory structure and files are placed in the identical path. If database files are placed on distinct directory locations on both servers then we need to manually restore the log backup taken after adding the data file on the primary database WITH MOVE statement on the standby database to reinitiate the subsequent logs to apply smoothly.
Q: Can we change the availability mode of the existing Always on availability group configuration?
A: Yes, we can change availability mode using properties window of identified availability group or we can also do it using ALTER AVAILABILITY GROUP statement.
Q: Suppose you have 2 node failover cluster instances with shared storage to support high availability. Let’s assume your storage has become inaccessible then what would be the impact on SQL Server instances?
A: SQL Server databases will become inaccessible as we have only one set of database files hosted on shared storages in the failover cluster configuration.
Q: How do you patch SQL Server cluster instances?
A: We will follow the rolling upgrade approach to apply patches on cluster instances.
- First, make sure to run a full backup of all databases
- Ensure all roles are online from the active node (Node1).
- Apply patches on passive node (Node2)
- Reboot the server (Node2)
- Failover active node (Node1) to newly patched passive node (Node2) to make it active node
- Apply patches on new passive node (Node1) now which was earlier active node
- Reboot the server (Node1)
- Failback SQL Server to the original active node (Node1)
Conclusion
This article has explained SQL Server DBA interview questions and answers on migration & upgrades, performance tuning, and high availability & disaster recovery topics. Please share your feedback in the comment section, also you can let us know if you are looking for interview questions on any specific SQL Server topics.
Tags: database administration, sql server Last modified: November 06, 2023
