Index Maintenance

Index Maintenance

dba, development

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

July 29, 2016

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

May 25, 2016

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

April 22, 2016

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

March 14, 2016

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?

January 28, 2016

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?

December 29, 2015

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

January 14, 2015

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…

Tweaking the Defaults for Ola Hallengren’s Maintenance Scripts

December 17, 2014

Index Maintenance, Ola Hallengren's Database Maintenance Scripts

120

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?

November 18, 2014

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)

April 5, 2014

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

January 29, 2014

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?

October 9, 2013

Index Maintenance

Kendra with her Not-A-SAN This question came up in the pre-show chat for our weekly webcast: when you rebuild a nonclustered index offline, does it impact only the nonclustered index? Or does it impact the entire table? Showing is more fun than telling! Let’s take a look. First I restore AdventureWorks to slow storage Sometimes…

Why Index Fragmentation Doesn’t Matter [Video]

September 23, 2013

Index Maintenance, SQL Server, Videos

Are you still rebuilding all of your indexes every week – or heaven forbid, every night – and still having performance problems? Odds are, you’re not actually making things better – you’re making them worse! I explain why in this video: For more, read my post: Stop Worrying About Index Fragmentation.

Rebuild or Reorganize: SQL Server Index Maintenance

September 5, 2013

106

Index maintenance compacts a structure *and* changes physical order– both can be important! Editors Note: this post was updated in June 2014 to link to an online index rebuild bug that can cause corruption. Once up on a time, there was a database server with 500GB of data and a heavy read workload of dynamic…

5 Things About Fillfactor

April 30, 2013

Index Maintenance

Are you a page half full, or a page half empty kind of person? I’ll never think “I’ve seen it all” when it comes to SQL Server– there’s just always someone waiting around the corner who’s found a weird new thing to do with it. But there are some things I really wish I could…

Stop Worrying About SQL Server Fragmentation

August 14, 2012

131

I bet you’re worried about your index fragmentation. If your database was a phone book, it would look like this one. And I bet you’re wasting your time, missing the real point. What is Index Fragmentation and How It Happens Let’s take a step back for a second and pretend that your database is a…

Index Fragmentation Findings: Part 2, Size Matters

February 10, 2009

Brent Ozar Unlimited Team

Last week, I blogged about the basics of SQL Server index fragmentation: why it happens, how to fix it, and how often people are fixing it. I left you with a cliffhanger: it seemed that the frequency of defrag jobs didn’t appear to affect fragmentation levels: Databases with no index defragmentation were an average of…

Index Fragmentation Findings: Part 1, The Basics

February 6, 2009