Aunt Kathi's SQL Server Home

What’s the difference between reorganizing and rebuilding an index?

Posted on 2015/10/22 by Kathi Kellenberger

I was teaching a group of women in St. Louis about database maintenance. When I began talking about index maintenance, I used an interesting analogy to explain the difference between reorganizing and rebuilding of an index. The technical answer is that the reorg only removes fragmentation from the leaf level while the rebuild completely recreates the index.

Index maintenance can be automated in one of two ways. Either you can use the built-in Maintenance Wizard, or you can use a scripted solution. Most experienced DBAs use scripts for maintenance instead of the wizard, but the wizard is a good choice in some cases. When using the wizard, you must choose between reorg and rebuild, and the choice affects all indexes in the database. Scripting allows tables that are not fragmented to be skipped, and the action to be performed can be controlled by the fragmentation level.

One of the mistakes I have seen with wizard generated maintenance plans is selecting both reorg and rebuild. Imagine that you have a wall in a room that hasn’t been painted for quite some time. Maybe the wall is dirty, but you can fix that by just painting. If the wall is in really bad shape, you may need to knock the wall down and rebuild it. There is no point in painting a wall right before you rebuild it.

If you do intend to use the Maintenance Wizard for index maintenance, I suggest that you Rebuild. If your databases are larger than a couple of gigabytes, you should probably consider a scripted solution instead. You don’t even need to create the solution yourself because there are plenty of free scripts available. Check out Ola Hallengren’s solution that has been used by thousands of databases administrators around the world.

Posted in SQL Server Administration | Leave a comment

Database Con?

Posted on 2015/09/11 by Kathi Kellenberger

In 1970, a group of comic book fans started an event in San Diego called Comic-Con™. Last year, over 130,000 attended the convention. There are spin-off events around the US and around the world attracting millions of people who love comic books and science fiction. The events feature top names in the industry, panels, demonstrations, educational sessions, and vendors selling related wares. You will also see attendees dressed up as their favorite super heroes or in kilts. This phenomenon is not limited to comic book aficionados; there are “cons” for fans of zombies, Star Trek, furry costumes, Lego® bricks, and many more.

One day, I realized that PASS Summit was actually a con for SQL Server and Microsoft BI stack enthusiasts. The event features top names in the industry, panels, demonstrations, educational sessions, and vendors selling related wares. You will also see attendees dressed up as their favorite super heroes or in kilts.

While PASS Summit can’t claim the numbers seen in San Diego, it is the largest SQL Server and BI convention in the world with over 5000 registrations in 2014. We are there to learn, get advice, and network. But there is something more. We go to geek-out over the technology we love. Working with data is much more than a job to many of us.

I’ll be attending my 12th PASS Summit this year, and I am honored to say that I was chosen as a speaker for the third year in a row. At Summit, I will connect with my friends, learn about SQL Server 2016, and talk to the folks who actually build SQL Server. I will come home with renewed enthusiasm and fantastic new ideas about ways I can help my customers get the most from SQL Server.

Most of us love the work we do, and we are generally compensated well. As a way to give back and make a positive difference in the world, Argenis Fernandez (b|t) started a fundraiser for Doctors Without Borders. Expect to see Argenis in some crazy costume at Summit if his goal is met.

There are dozens of reasons to attend PASS Summit 2015, and one of them is that it is just a ton of fun.

Photo courtesy of Steve Jones (b|t)

Posted in Life in IT | Leave a comment

New Article on Simple-Talk

Posted on 2015/08/31 by Kathi Kellenberger

Not only do I write here on my blog and for Apress, I sometimes write for Simple-Talk.com and SQL Authority. I just completed a new article for Simple-Talk . Back in 2009, Simple-Talk asked me to write an article explaining the solution to a contest called T-SQL Speed Phreakery. Now that time has passed, are there any new T-SQL features that can make solving the puzzle easier? Find out by reading “T-SQL Window Functions Speed Phreakery!”