SQL Server Interview Questions & Answers – Architecture

This series of SQL Server Interview Questions & Answers are based on real interviews. I have collected all these questions from multiple senior DBAs who are involved in taking interviews. This set of questions is specially for junior or mid level DBAs. I have covered SQL Server basics and architecture part in this series. I will publish similar interview questions &  answers for other topics in next articles.

SQL Server Interview Questions & Answers – Basics & Architecture

Question – What is Page and how many types of pages are in SQL Server?

Answer – Page is the fundamental unit of data storage in SQL Server. The size of Page is 8kb. Below is the type of pages available in SQL Server

1. Data
2. Index
3. Text/Image
4. Global Allocation Map
5. Shared Global Allocation Map
6. Page Free Space
7. Index Allocation Map
8. Bulk Changed Map
9. Differential Changed Map

Read Pages & Extent section of the attached article “Understanding Data files, Pages & Extents“ to learn more about all page types.

Question – What is an Extent?

Answer – Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents. There are two types of extents.

• Uniform extents
• Mixed extents

All eight pages belongs to same object in Uniform extents Whereas in Mixed extents, pages belongs to two or more objects. Each of the eight pages can be owned by a different object.

Question –Does log files write their logs in Pages?

Answer – No, Log files do not have any pages because they don’t store any data. Log files store a series of log records to maintain data integrity & point in time recovery.

Question – What is filegroups and their benefits?

Answer – File Group is the logical unit that works as a container for data files. We can place multiple data files in to a filegroup. Filegroups are very helpful in below cases:

1. If you have big database and you want to reduce the recovery time in case of any failure. You can plan filegroup backups that can be used for partial restores and data will be accessible while corresponding filegroup will be restored. Even you can run backup of an individual filegroup only.
2. You can segregate your critical data into separate filegroup and non-critical data into another filegroup. You can also place them on separate drives/RAIDs to reduce DISK IO.
3. Another advantage is that you can keep your filegroup in to read only mode. If you don’t want to run any DML statement on some of the tables, you can place all those tables into separate file group and change that file group into read only mode.

 Question – How many types of filegroup available in SQL Server and Log files belong to which file group?

Answer – There are two types of filegroup.

1. Primary Filegroup
2. Secondary Filegroup

A database will have only one Primary Filegroup and can have as much as 32767 secondary filegroups. When we create a database primary filegroup created automatically. Primary data file will be part of primary filegroup. You can add maximum 32767 data files in a single filegroup.

Log files does not belong to any filegroup.

Question–What is database files and how many types of database files are there in SQL Server Database?

Answer – Database files are the physical files that are created on OS drives when you create a SQL Server Database. These files are used to store actual data and log records of the transactions. There are two types of database files.

• Data File
• Log File

Data files further categorized into two types.

• Primary Data File
• Secondary Data File

Read below articles to get more information about database files.

• Understanding Data Files in SQL Server
• Understanding Transaction Log File in SQL Server
• What is Virtual Log Files in SQL Server?

Question – How SQL Server stores logs in transaction log file?

Answer – SQL Server stores logs serially in transaction log file.  Transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written with the incremental LSN that means a new log record will be assigned a LSN that is higher than the LSN of the record before it. Read attached article to Understand SQL Server Transaction Log Architecture that will help you to understand this topic.

Question – What is virtual log file?

Answer- Each transaction log file internally divided into several virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file.

Question – How do you get total no of virtual log files in a transaction log file?

The new DMF sys.dm_db_log_info introduced in SQL Server 2017 that will returns Virtual Log File information of the transaction log files.  If we will specify NULL or DEFAULT value, it will return VLF information of the current database. The built-in function DB_ID can also be specified to get details of a particular database. The sys.dm_db_log_info DMF replaces the DBCC LOGINFO statement from earlier versions. Read attached article to get that. Read attached article where I have described how to get total no of virtual log files in a transaction log file in detail.

Question – By default, your log file belongs to which filegroup?

Answer- Log files do not belong to any file group.

Question – Do you think multiple log files are useful in SQL Server?

Answer- Yes, it will be helpful in case you are dealing with space issues. You can add additional log files in another drive if you stuck by spaces issue in log file/drive during data load/Import. You can also create additional log file in to another drive if you are frequently facing space issue and cannot extent the existing log file drive.

Question – Explain Checkpoint operation in SQL Server?

Answer – A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk in data files. Read more about Checkpoint in attached article.

Question – How many types of checkpoint SQL Server Supports?

Answer – There are four types of checkpoint SQL Server supports.

1. Automatic Checkpoint
2. Indirect Checkpoint
3. Manual Checkpoint
4. Internal Checkpoint

I have explained Checkpoint Types in attached article. Please have a look on this to get more details.

Question – What is Instant File Initialization and its benefits?

Answer – SQL Server has a feature called Instant file initialization that allows fast data file allocations of the all data file operations. Instant file initialization reclaims used disk space without filling that space with zeros.Have a look at the attached article to Understand Instant File Initialization & How to enable it on SQL Server.

Question – How do you know that Instant File Initialization is enabled on your SQL Server Instance?

Answer- You can see the SQL Server error log file. When SQL Server starts, it captures this information whether Instant File Initialization is enabled for this instance or not. I have explained this also in above attached article.

Read More SQL Server Interview Questions

• SQL Server Interview Q&A on Alwayson Availability Group
• SQL Server DBA Interview Questions & Answers
• SQL Server Backup & Recovery Interview Questions
• SQL Server Interview Questions & Answers on Indexes

I hope these interview questions & answers will help you cracking your interviews. Please follow our facebook page and Twitter handle to get latest updates.