The Curious Case of… too few log records for an offline index rebuild

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

I had an email question today asking why there were too few log records for an offline index rebuild the person was doing. They were concerned that the index they were rebuilding in the full recovery model wasn’t being rebuilt properly because instead of seeing at least one log record per row, they were seeing far fewer.

There’s a very simple answer: SQL Server will always try to generate as little log volume as possible.

Let’s say you have an index with 100,000 rows, and 100 rows fit on each index leaf page. When doing an offline rebuild in the full recovery model, SQL Server will not log an LOP_INSERT_ROWS log record for each index row, as that’s too inefficient, given the 100+ bytes of overhead in each log record. Instead it will log about 1,000 LOP_FORMAT_PAGE log records, where each log record contains a full page image with 100 rows in it. That saves the overhead of 99 log records for each 100 rows inserted into the new index – for a total saving of 99,900 x log-record-overhead in log volume (as it would still have to log the LOP_FORMAT_PAGE log records for the page header of each of the 1,000 pages created).

And if you think about it carefully, that approach gives just the same ability to recover the transaction in the event of a crash, as the net effect of each approach is the same.

SQL Server does as much as it can to limit what gets logged for efficiency, which is a good thing if you think of all the places that log is used (including backups, restores, log shipping, replication, mirroring, AGs).

SQLintersection Fall 2019

As we head towards our SQLintersection Fall 2019 in November (our 14th show), I’m excited to say that it’s once again our most diverse, complete, and information-packed show yet!

One of the pieces of feedback we hear over and over is that attendees love SQLintersection because it’s a smaller, laid-back show, where you get to actually spend time talking with the presenters 1-1. I have to say that’s one of the reasons why we love the show so much; *we* get to spend time talking to attendees, rather than being mobbed by hundreds of people after a session ends. And we only pick presenters who we know personally, and who we know to be humble, approachable, and eager to help someone out.

We have 2 pre-con days at the show and with our post-con day, there are 8 full-day workshops from which to choose. We have 32 technology-focused (NOT marketing) sessions from which to choose, plus two SQL Server keynotes, multiple industry-wide keynotes by Microsoft executives, and the ever-lively closing Q&A that we record as a RunAs Radio podcast (you can listen to the recording from our Spring 2019 show here.)

You’ll learn proven problem-solving techniques and technologies you can implement immediately. Our focus is around performance monitoring, troubleshooting, designing for scale and performance, cloud, as well as new features in Azure and SQL Server 2016, 2017, and 2019. It’s time to determine your 2008 / 2008 R2 migration strategy – should you upgrade to 2016/2017/2019 directly? Should you move to Azure? This is the place to figure that out!

If you’re interested in how we got here – check out some of Kimberly’s past posts:

  1. SQLintersection: a new year, a new conference
  2. SQLintersection’s Fall Conference – It’s all about ROI!
  3. Fall SQLintersection is coming up soon and we can’t wait!
  4. SQLintersection Conference and SQLafterDark Evening Event – what a fantastic week in Vegas

And Kimberly recorded a Microsoft Channel 9 video where she discusses the 2017 Spring show – see here.

SQLafterDark

With minimal to no marketing filler, we’ve largely kept our conference focus on ROI and technical content (performance / troubleshooting / tales-from-the-trenches with best practices on how to fix them ) but we’ve also added even more social events so that you really get time to intersect with the conference attendees and speakers. The addition of the SQL-specific, pub-quiz-style evening event SQLafterDark was wildly popular from our past shows and that’s returning for Fall!

SQLintersection: Great Speakers!

Once again, I think a great show starts with great speakers and current / useful content. All of these speakers are industry-experts that have worked in data / SQL for years (some can even boast decades) but all are still focused on consulting and working in the trenches. And, they’re good presenters! Not only will you hear useful content but you’ll do so in a way that’s digestible and applicable. Every speaker is either an MCM (Master), a past/present SQL Server MVP, or a past/present Microsoft employee (or a combination of all three!) But, regardless of their official credentials, ALL are focused on providing the most ROI that’s possible in their session(s) and/or their workshops, and ALL have spoken for SQLintersection multiple times.

Check out this phenomenal list of speakers:

  • Aaron Bertrand – MVP, SentryOne
  • Ben Miller – MCM, MVP, past Microsoft, Consultant
  • Bob Ward – SQL Server team at Microsoft
  • Brent Ozar – MCM, past-MVP, Consultant
  • Buck Woody – SQL Server team at Microsoft
  • David Pless – MCM, Microsoft
  • Grant Fritchey – MVP, RedGate
  • Jonathan Kehayias – MVP, MCM, MCM Instructor, SQLskills
  • Kevin Farlee – SQL Server team at Microsoft
  • Kimberly L. Tripp – MCM Instructor, MVP, past SQL Server team at Microsoft, SQLskills
  • Pam Lahoud – MCM, SQL Server team at Microsoft
  • Paul S. Randal – MCM Instructor, MVP, past SQL Server team at Microsoft, SQLskills
  • Pedro Lopes – MCM, SQL Server team at Microsoft
  • Tim Chapman – MCM, Microsoft
  • Tim Radney – MVP, SQLskills

You can read everyone’s full bio on our speaker page here.

SQLintersection: When is it all happening?

The conference officially runs from Tuesday, November 19 through Thursday, November 21 with pre-conference and post-conference workshops that extend the show over a total of up to 6 full days. For the full conference, you’ll want to be there from Sunday, November 17 through Friday, November 22.

  • Sunday, November 17 – pre-con day. There are three workshops running:
    • Getting Started as a Data Enginer in 2019 with Tim Chapman
    • SQL Server Indexes: What, Why, and How! with Kimberly L. Tripp
    • Leveling up with PowerShell for the DBA with Ben Miller
  • Monday, November 18 – pre-con day. There are three workshops running:
    • Management, Adminstrations, and Best Practices for the Hybrid DBA (SQL Server 2017+, Azure SQL Database, and Managed Instance) with Tim Radney and David Pless
    • Performance Troubleshooting using Waits and Latches with Paul S. Randal
    • Zero to Hero: Faster SQL Query Performance with Jonathan Kehayias
  • Tuesday, November 19 through Thursday, November 21 is the main conference. Conference sessions will run all day in multiple tracks:
    • Check out our sessions online here
    • Be sure to check out our cross-conference events and sessions
    • Get your pop-culture trivia and techie-SQL-trivia hat on and join us for SQLafterDark on Wednesday evening, November 20
  • Friday, November 22 is our final day with three post-conference workshops running:
    • Developer’s SQL Server Recipe Book with Brent Ozar
    • Performance Tuning and Optimization for Mondern Workloads (SQL Server 2017+, Azure SQL Database, and Managed Instance) with David Pless and Tim Radney

SQLintersection: Why is it for you?

If you want practical information delivered by speakers that not-only know the technologies but are competent and consistently highly-rated presenters – this is the show for you. You will understand the RIGHT features to troubleshoot and solve your performance and availability problems now!

Check us out: www.SQLintersection.com.

We hope to see you there!

PS – Use the discount code ‘SQLskills’ when you register and receive $50 off registration!

Calling all user group leaders! We want to present for you again in 2019!

So far this year, we at SQLskills have presented remotely (and a few in-person) to 42 user groups and PASS virtual chapters around the world, and we have 6 more scheduled!

Now we’re into the second half of 2019, we’d like to schedule some more presentations through the rest of the year.

We’d love to present remotely for your user group in 2019, anywhere in the world, as long as the time/timezone works reasonably. It’s not feasible for us to travel to user groups or SQL Saturdays unless we’re already in that particular city, but remote presentations are easy to do and are becoming more and more popular. We haven’t had any bandwidth problems doing remote presentations in 2018 to groups as far away as Australia and India, plus Israel, Canada, Ireland, UK, Slovakia, Czech Republic, Ukraine, South Africa, Denmark, Croatia, and Poland. This way we can spread the community love around user groups everywhere that we wouldn’t usually get to in person.

Note: we have our own Webex accounts which we generally use, or we can use your GoToMeeting or Webex, but you must use computer audio – we won’t call in by phone as the sound quality is too poor. We also will not use Skype/Lync as we’ve had too many problems with it around user group laptops and sound.

So, calling all user group leaders! If you’d like one of us (me, Kimberly, Jon, Erin, Glenn, Tim) to present remotely for you in 2019 (or maybe even multiple times), send me an email and be sure to include:

  • Details of which user group you represent (and if sending from a shared user group account, your name)
  • The usual day of the month, meeting time, and timezone of the user group
  • Which months you have available, starting in September 2019 (a list of available dates would be ideal)
  • Whether you’d like just one or multiple

And I’ll let you know who’s available with what topics so you can pick. We have around 20 topics across the team that we can present on.

What’s the catch? There is no catch. We’re just continuing our community involvement next year and we all love presenting :-)

There’s no deadline for this – send me an email at any time and we’ll see what we can do.

We’re really looking forward to engaging with you all!

Cheers