Index Maintenance

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

August 13, 2019

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?

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

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

November 28, 2017

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…

How to Configure Ola Hallengren’s Maintenance Scripts

December 17, 2014

Index Maintenance, Ola Hallengren's Database Maintenance Scripts

125

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

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