Free Webinars hosted by GroupBy

 

Developers, Query writers, and DBAs – Register for Free Webinars hosted by GroupBy.  You do not need to attend all the sessions.

https://groupby.org/

Sessions:

Friday, April 21:

• 8AM Eastern – How to Use Parameters Like a Pro and Boost Performance by Guy Glantser
• 10AM – Introducing the SQL Server 2016 Query Store by Enrico van de Laar
• Noon – Worst Practices & Less Known Limitations for Columnstore Indexes by Niko Neugebauer
• 2PM – Gems to Help You Troubleshoot Query Performance by Pedro Lopes
• 4PM – Crash Course on Better SQL Development by Vladimir Oselsky

Friday, April 28:

• 8AM Eastern – SQL Server 2016 Features for Performance Tuning Lovers by Matan Yungman
• 10AM – SAN Primer for the DBA by Doug Bernhardt
• Noon – Bringing DevOps to the Database by Steve Jones
• 2PM – Azure SQL Databases: A Guided Tour by Mike Walsh
• 4PM – Hacking SQL Server by André Melancia

Why is the Trans Log larger than the data inserted?

This was a fun question to figure out. At a SQL PASS meeting in Orange County this last week. One of the members asked all the DBAs, “When I use Select Into with Simple or Full recovery mode, why is the transaction log 4 times larger than the data inserted into the table?”

None of the DBAs could answer the question. So I spent after hours testing to find out.

This research paper includes the code to set up your own test. Great for learning more about the Transaction Log. See attached .pdf for the entire research paper: translog_selectinto_research

Transaction log size in each mode after the same “select into” statement was run. The log file was backed up and truncated between each test.

Type of Recovery Model	Log File size after transaction (MB)	Log file in use after Transaction (MB)	Max Log Row size after transaction	Number of log rows after transaction	Number of large rows after transaction
Simple	1023	132	11928	1225840	2
Full	4351	3278	12328	2048806	387090
Bulk-Logged	4351	222	12328	1327437	2

Summary:

The transaction log quadruples in size when running a “select into” with Full Recovery Model, because not only the data changes are recorded. Every memory change, lock, latch, split pages, etc. is recorded in the log. 

For the Simple model, the log file in use grew from zero to 132MB. Even though “Select Into” is minimally logged in the transaction log for Simple model, memory changes are tracked in the log. Items tracked in Simple model with a “Select Into” were page splits, memory allocation, latches, locks, and create table. Minimal logging does not mean no logging.