In this article, I am giving a quick overview about SQL Server Database Engine. After completing this article you will understand:
What is SQL Server Database Engine?
What is the use of Master Data file and Transaction Log file?
How does SQL Server Perform DML Operation?
Please give your valuable suggestions and feedback to improve this article.
What is SQL Server Database Engine?
In SQL Server, Database Engine is the core service for storing and processing and securing data in either a relational format or xml documents.
Whenever you create a new database SQL Server creates two data file.
1. Primary Data File (Master Data File or .mdf file)
2. Transaction Data File (Log File or .ldf file)
SQL Server can also have secondary data file along with Primary Data File. Generally .ndf file extension is used for secondary data file.
What is the use of Master data file and Transaction Log file?
Primary Data File
SQL Server stores our data in 8KB chunks called data pages. SQL Server gathers all 8KB data pages into a single file which is generally called as Master Data File (Primary Data File). Master Data File has .mdf file extension. The extension for Master Data File is not fixed, it can be anything, and SQL Server will automatically recognize its Data file if you give different file extension. Disk I/O operations are performed at the page level. SQL Server reads and write whole data pages.
When data is written into the disk an entire row of it must fit within the 8KB data Page. It is possible to share a Data Page among multiple rows, but a row can’t span multiple pages.
Let’s understand this with one simple example.
A Data page size is 8KB, if I convert 8KB into bytes it becomes 1024*8 = 8192 Bytes. Out of 8192 Bytes 132 bytes will be used by the data page to store system information about the data page i.e Page Number, the amount of free space on the page, allocation id of the object etc.
Available space in a Data page to store data will be 8192-132 byes = 8060 Bytes.
Suppose we create a table, which has only one column for which data type is Int. As you know Int data type needs 4 bytes space to store the information. Which means a data page can contain 8060/4 = 2015 rows.
Transaction Log File
The transaction log file is a critical component of the database. It contains the transaction log that records all transaction and the database modification made by each transaction. Everything that happens in the SQL Server occurs only through the Transaction log for example In case of DML operation first data modification gets records in transaction file, if transaction gets committed then it goes to Data pages and becomes permanent part of database.. Generally .ldf file extension is used for the Transaction log file, but it can be anything. SQL Server database engine will automatically recognize the transaction log file if other file extension will be used.
In the event of system failure, the transaction log might be required to bring your database back to a consistent state. SQL server has automated recover mode that kicks when you start you database engine after failure. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.
How does SQL Server Perform DML Operation?
When SQL Server is told to do something using SQL, then SQL Server internal component Query Optimizer looks at the query and creates best query execution plan. SQL Server query optimizer has several techniques to create best execution after looking into SQL query. Using the best query execution plan SQL Server executes the query.
When we perform any DML operation in that case SQL Server finds the required data page from the disk and reads it into the memory and makes the change into it.
SQL Server never writes back the data page into the disk just after the data modification made in memory; it keeps changed data page in the memory. It is because SQL Server waits for some more time thinking that there might be more changes coming up and it’s faster to keep them in memory for a bit. When memory gets full or close to full at that time SQL server automatically writes the data pages into disk. But keeping data pages in memory puts data at disk, in case of power failure or system failure we may lose our important data. To come up from this problem SQL Server uses Transaction log. Transaction log keeps track of data modification that has been made and not written into the disk. In case of powered failure or system failure SQL Server automatically recovers the data from the transaction log.
Let’s understand the above concept with example
In the below image on the top left we have SQL Server memory, which currently doesn't have anything. On the top right we have disk drive where files are physically stored and it is permanent. In case of power failure or system failure data stored in the disk will not be lost. At the bottom we have the Transaction log file which is also stored in the disk. In below Image as you can see we don’t have any data page in memory and there is no log information in Transaction log file. There are three data pages available in disk drive.
After reading the data page into Memory, SQL Server makes necessary changes that can be anything Insert, update, delete. In below image you can see data modification has been made into the data page which is present in the memory. Orange mark indicates modification has been successfully made in the data page.
Just after making the data modification in the data page in Memory, SQL Server quickly records the same data modification in the Transaction log file. In below image you can see same data modification (Orange) has been logged in Transaction log file.
SQL Server never writes back the data page into the disk instantly just after the data modification made in memory.It keeps changed data page in the memory and in the Transaction log.SQL Server waits for some more time thinking that there might be more changes coming up for that data page and it’s faster to keep them in memory for a bit. Below Images shows that.
Now suppose SQL Server needs to make another data modification. Once again it grabs the required data page from the disk and reads it into the memory. As you can see from below image another data page has been read into Memory.
After reading the second data page into Memory, SQL Server makes necessary changes that can be anything Insert, update, delete. In below image you can see data modification has been made into the data page which is present in the memory. Green triangle mark indicates modification has been successfully made in the second data page. Just after making the data modification in the data page in Memory, SQL Server quickly records the same data modification in the Transaction log file. In below image you can see same data modification (Green mark) has been logged in Transaction log file, which is also present in the disk.
Suppose SQL Server is running out of memory, so it will write the data page in Disk and frees Memory. As you can see from the below Image first data page (Orange) has been written into the disk.
Once data page moves into the disk, in Transaction log it will be marked that data page has been written into the disk and in future in system failure or crises there is no need to write the same data modification in to the disk. In below image you can see in Transaction log Orange has been marked.
Now assume that something went wrong i.e system failure, power failure occurs. In that case whatever our memory has will be lost. From both the below Images you can notice Memory is becoming empty.
In the event of system failure in above case, the transaction log might be required to bring your database back to a consistent state. SQL server has automated recover mode that kicks when you start you database engine after failure. Once SQL Server starts again it looks into the Transaction log and check whether it needs to write any data modification into the disk which was not written. In above case it will find one record (Green) that needs to be modified into the disk drive. It will write the data modification into the disk and marks in Transaction log that this record has been written into the disk. From the below image you can clearly see that, even after the system failure SQL Server has successfully recovered the data modification and saved it into the disk.
