Fragmentation assessment, rebuilds, reorganizing, and maintenance policy.
44 associated posts43 primary posts
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.
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.
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 it fails.
This week, Brent, Erik, Tara, and Richie discuss index fragmentation, Azure disk speeds, Availability Groups, database restores, optimize for ad hoc setting, forced parameterization, BI consultants, index views, SPN registrations, and more.
Here’s the video on YouTube:
This week, Erik - all by his lonely self - talks about troubleshooting remote queries, his thoughts on SQL Server 2017, licensing, index rebuilds, index fragmentation, why you shouldn't restart SQL server every day, and much more!
Here’s the video on YouTube:
This week, Brent, Richie, and Erik discuss the requirements for getting a Senior DBA or Database Architect job, data migration, encryption, fragmentation, differences between SQL Server 2016 and 2014, update stats strategies for large tables vs small ones, what it takes to work for Brent Ozar Unlimited, and more.
Here’s the video on YouTube:
This week, Brent, Richie, Tara, and Erik discuss vendor licensing, rebuilding indexes, analysis services, using trace flag 174, and the team's PASS Summit impressions and highlights.
Here’s the video on YouTube:
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…
Let's just get one thing out of the way first.
Yes, I understand that you, dear reader, probably hate maintenance plans. You associate them with everything dumb that you did early on in your career, like setting up a single plan that reorganized indexes, then rebuilt them, then updated their statistics. You are older and wiser now, and you swear by tools like Ola Hallengren's maintenance scripts or Minion Reindex.
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.
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.
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:
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:
[crayon-6ae69adb8d4e8145279965/]
The defaults on some of these parameters are a little tricky:
Index maintenance is confusing. Should you use maintenance plans or customized SQL Agent jobs? Join Kendra to learn the difference between index ‘rebuild’ and ‘reorganize’ commands, and how to avoid the pitfalls of maintenance plans in SQL Server.
https://www.youtube.com/watch?v=6nJKIeJC2lg
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…
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…
Data compression can have many uses and advantages, but it also has its drawbacks. It’s definitely not a one-size-fits-all strategy. One of the things to be aware of is that initial compression of a table or index can take quite some time, and will be resource-intensive. It also is an offline operation, so the object…
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 slower storage is handy.…
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:
https://www.youtube.com/watch?v=fD1CZVc6oUk
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 queries. Data was updated frequently throughout the day and index tuning was a…
