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.
Conclusion
I have explained SQL Server interview questions and answers on its SQL Server Architecture, SQL Server Administration, and Backup & Recovery. Stay tuned for my next article in which I will explain interview questions and answers on SQL Server migrations & upgrades, performance tuning, and high availability & disaster recovery solutions.
Last modified: November 01, 2022
