Search Results for &#8220;index fragmentation&#8221;

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…

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

Kendra Little

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…

Why Index Fragmentation Doesn’t Matter [Video]

Last Updated February 13, 2017

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.

Index Fragmentation Findings: Part 2, Size Matters

Last Updated July 13, 2017

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

Last Updated July 13, 2017

We asked the community for index fragmentation information for data mining purposes, and the results are in – and interesting. Over the next couple of weeks, I’ll talk about some of the results we found in the field. What Is SQL Server Fragmentation? SQL Server stores data in pages. To make things easy, think of…

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…

[Video] Fragmentation Explained in 20 Minutes at SQLBits

Last Updated August 30, 2022

Index Maintenance, Videos

What does fragmentation mean? How does it happen? Can you fix it with fill factor? Should you rebuild your indexes to fix it? At the SQLBits conference, I tried a new way of explaining it using markers and pieces of whiteboard paper as database pages, and doing inserts live on the fly. What you see…

Meme Week: Setting Fill Factor to Fix Fragmentation

Last Updated February 19, 2022

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,…

DBA Training Plan 9: My 3 Index Guidelines

Last Updated August 15, 2019

DBA Training Plan, Indexing

In our last episode, we used sp_BlitzIndex @Mode = 2 to get to know the contents of our database, sorted our indexes from biggest to smallest, and started asking questions about leftover backup tables that we probably didn’t need to keep around anymore. Now that you’ve taken out the trash, let’s have a closer look…

Index Tuning Week: Fixing Nonaligned Indexes On Partitioned Tables

Last Updated September 28, 2018

SQL Server

Unquam Oblite This post will not change your life, but it will help me remember something. When you decide to partition a table to take advantage of data management features, because IT IS NOT A PERFORMANCE FEATURE, or you have an existing partitioned table that takes advantage of data management features, because IT IS NOT…

When Does Index Size Change Index Choice?

Last Updated January 13, 2018

Brent Ozar Unlimited Team

Indexing, SQL Server

Size Matters? One thing I hear a lot is that the optimizer will take the size of an index into account when choosing which one to use. The tl;dr here is that it may make more of a difference when it comes to index width (think the number of columns in an index) than it…

1.1 Indexes: How to Think Like the Engine (110m)

Last Updated December 27, 2017

When you pass in a query, how does SQL Server build the results? We’ll role play: Brent Ozar will be an end user sending in queries, and you’ll be the SQL Server engine. Using simple spreadsheets as your tables, you’ll learn how SQL Server builds execution plans, uses indexes, performs joins, and considers statistics. This…

You’ll need a membership to access this course. Check out my training classes.

Rebuilding Indexes Can Slow Queries Down

Last Updated December 18, 2017

Index Maintenance

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…

Index Maintenance Madness

Last Updated December 1, 2017

Tara Kizer

SQL Server

When we look at a new server, we check out their database maintenance jobs, making sure their server is in a safe place before we dig into their pain points. We verify if their backups and DBCC CHECKDB jobs can meet their RPO/RTO goals. We also check out their index maintenance jobs, if any. When…

Good Reasons to Rebuild or Reorganize Indexes

Last Updated November 29, 2017

Brent Ozar Unlimited Team

Index Maintenance

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…

3.6 Index Maintenance for Enterprise Environments (43m)

Last Updated March 20, 2021

You’ve learned over time that maintenance plans are the wrong way to go, and you’ve implemented index maintenance scripts from Ola Hallengren, the MidnightDBAs, or Michelle Ufford. However, you’re not really sure exactly what you’re fixing, or if it’s the least invasive way to improve performance. Learn the difference between internal and external fragmentation, see…

Indexes For Kids

Last Updated February 13, 2017

Indexing, SQL Server

Kid Tested When you work with something a lot, you tend to extrapolate concepts from it out to everything you see. That one person collecting tickets is a serial zone. That food truck line is a HEAP. The empty subway car that smells like a toilet is Replication. Stuff like that. If you have one…

Getting Started With Oracle Week: Creating Indexes and Statistics

Last Updated June 4, 2016