Index Maintenance

I just shake my head when I see people setting fill factor in an attempt to “fix” fragmentation. The default fill factor is 100%. That means during an index rebuild, SQL Server packs 100% of your 8KB pages with sweet, juicy, golden brown and delicious data. But somehow, some people have come to believe that’s bad,…

Free Webcast: Help! My SQL Server Maintenance is Taking Too Long!

Last Updated January 22, 2021

Backup and Recovery, Company News, Index Maintenance, Production Database Administration

“Time for a smoke break.” You manage growing SQL Server databases with shrinking nightly maintenance windows. You just don’t have enough time left each night to do the necessary backups, corruption checking, index maintenance, and data jobs that your users and apps want to run. Cloud storage isn’t helping the problem, either. Stop playing Tetris…

[Video] What Percent Complete Is That Index Build?

Last Updated March 24, 2020

Index Maintenance, SQL Server 2017, Videos

SQL Server 2017 & newer have a new DMV, sys.index_resumable_operations, that show you the percent_completion for index creations and rebuilds. It works, but…only if the data isn’t changing. But of course your data is changing – that’s the whole point of doing these operations as resumable. If they weren’t changing, we could just let the operations…

DBA Training Plan 10: Managing Index Fragmentation

Last Updated May 7, 2020

DBA Training Plan, Index Maintenance

In the last episode, we looked at your index designs with the output of sp_BlitzIndex. You might have gotten a little overwhelmed what with all the different warnings (and all the learning resources!) You might have thought, “Is there an easier way?” “Is there a way I can just run a job, and have something…

Does the Rowmodctr Update for Non-Updating Updates?

Last Updated May 20, 2019

Get Notebook – FAQ Update May 20 – make sure to read to the end for an update. Okay, look, it’s a mouthful of a blog post title, and there are only gonna be maybe six of us in the world who get excited enough to check this kind of thing, but if you’re in…

Rebuilding Indexes Can Slow Queries Down

Last Updated December 18, 2017

Today, Jonathan Kehayias wrote about how fragmentation impacts execution plans. I’m really into performance tuning, so I read the post carefully, really interested to see how it impacted query runtime. You should read it too – it’s a great post with a solid demo. But oddly, he didn’t mention the query speeds. I thought that was…

Good Reasons to Rebuild or Reorganize Indexes

Last Updated November 29, 2017

No, not that It’s not the F word. Not like this embarrassing page that was somehow updated in 2017, which still tells you to reorg at 5% and rebuild at 30% fragmentation, like your data is still on a drive that looks like a record player and might still start making a clicking noise when…

How To Fix Forwarded Records

Last Updated May 4, 2020

Tara Kizer

Some of our clients have very high forwarded record counts and aren’t aware of it until they run sp_BlitzFirst and get an alert about high Forwarded Records per Second. Some of these clients are using Ola Hallengren‘s IndexOptimize stored procedure to maintain their indexes. This brought up a question of whether or not rebuilding a…

Spring Cleaning Your Databases

Last Updated February 13, 2017

Tara Kizer

Even with lots of monitoring in place, we should perform periodic checks of our SQL Servers. Think of this like “Spring Cleaning”, except I would recommend that it be more frequently than just once a year. Doing it monthly might be a bit ambitious due to our busy schedules, but quarterly could be achievable. Below are…

Stats Week: Only Updating Statistics With Ola Hallengren’s Scripts

Last Updated June 17, 2017

Index Maintenance, Ola Hallengren's Database Maintenance Scripts

I hate rebuilding indexes There. I said it. It’s not fun. I don’t care all that much for reorgs, either. They’re less intrusive, but man, that LOB compaction stuff can really be time consuming. What I do like is updating statistics. Doing that can be the kick in the bad plan pants that you need…

Why most of you should leave Auto-Update Statistics on

Last Updated February 13, 2017

Index Maintenance, SQL Server, Statistics

Oh God, he’s talking about statistics again Yeah, but this should be less annoying than the other times. And much shorter. You see, I hear grousing. Updating statistics was bringin’ us down, man. Harshing our mellow. The statistics would just update, man, and it would take like… Forever, man. Man. But no one would actually…

Should I Worry About Index Fragmentation?

Last Updated February 13, 2017

Doug Lane

Here’s a handy flowchart for whenever you find yourself worrying about fragmentation: *Talk to your doctor about whether fragmentation is a problem for you. Seek help for index maintenance lasting longer than four hours. Serious side effects may occur if all index maintenance is ignored completely. Brent says: pop quiz: what are you doing more…

Does index fill factor affect fragmentation?

Last Updated March 12, 2018

Everybody wants to know about index fragmentation It is an inescapable vortex of malaise and confusion. Like that swamp in The Neverending Story that killed the horse. Sorry if you haven’t seen that movie. The horse wasn’t that cool, anyway. Neither is index fragmentation, but it’s not worth losing sleep over. Or a horse. I…

Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

Last Updated February 13, 2017

Index Maintenance, Locking, Blocking, and Isolation Levels, SQL Server

One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M). Here’s one way this can become a big problem: An online index rebuild starts against a large index A…

How to Configure Ola Hallengren’s IndexOptimize Maintenance Script

Last Updated April 14, 2023

Index Maintenance, Ola Hallengren's Database Maintenance Scripts

128

If you’re a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren’s free database maintenance scripts. They’re better than yours (trust me), and they give you more flexibility than built-in maintenance plans. However, the index maintenance defaults aren’t good for everyone. Here’s how they ship: Transact-SQL CREATE PROCEDURE…

When Does Index Fragmentation Matter?

Last Updated February 13, 2017

Jeremiah Peschka

A while back, we posted about how SQL Server index fragmentation doesn’t matter. That blog post generated a lot of heated discussion and has served to make people aware that fragmentation may not be the source of their woes. Sometimes, though, there are data access patterns where fragmentation really does matter. Perpetual Fragmentation Some data…

Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video)

Last Updated February 13, 2017

Do you rely on index rebuilds to make queries run faster? Or do you always feel like statistics are “bad” and are the cause of your query problems? You’re not alone– it’s easy to fall into the trap of always blaming fragmentation or statistics. Learn why these two tools aren’t the answer to every problem…

UPDATE STATISTICS: the Secret IO Explosion

Last Updated February 13, 2017

Index Maintenance, SQL Server, Statistics

I first knew something was up when I looked at the job history for a simple maintenance plan. It had two steps: Rebuild all the indexes in the database – this took 10 minutes each night. Update statistics – this took 2-3 hours each night. What was going on? Statistics in SQL Server are small,…

How Much is Offline During an Index Rebuild?

Last Updated February 16, 2017