sql architecture question?

Question

sql architecture question?

vsamantha35

SSChampion

Points: 11551
More actions
June 3, 2017 at 1:51 am

#338066

Hi SQLAdmins,
What is the reason behind having 2 different architecture's for storing sql data (.mdf) and log data (.ldf)? Why not 8KB page architecture for both data file & log files?
Thanks
Sam

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

Log file is written synchronously. If you commit a transaction, SQL Server won't reply until it gets confirmation from file system that log records are flushed. Now if you are on a slow system and there's only 3.5K of log records pending, you don't wwant to wait until another 4.5K arrives before the block is written and your transaction completes, Hence no 8K pages for log.

Data file is written asynchronously. Data is buffered in memory and flushed when needed. Here throughput is the important factor, hence SQL Server optimizes by writing 8K pages (or even 64K extents) at once.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Also, the data is written to the MDF and the log is written to the LDF. SQL first logs (LDF) the fact that it's creating, deleting or updating something. It then does the work (MDF) and marks the log records (LDF) as being done. This is the essence of durability in SQL Server. It's also how the engine knows what to commit or rollback when you do it.

In a nutshell, the files are storing different things for different purposes.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Perry Whittle SSC Guru Points: 234064 More actions · Answer 3

Ed Wagner - Saturday, June 3, 2017 9:04 AM
SQL first logs (LDF) the fact that it's creating, deleting or updating something. It then does the work (MDF) and marks the log records (LDF) as being done.

Actually, it's the pages in memory that are immediately updated, as mentioned previously the data on disk is modified later, possibly sometime later. The log file rolls back or forward the changes that occurred to the database pages that were updated in memory but maybe not on disk

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉