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…
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 finding.One of those things I hate finding is bad fillfactor settings. Fillfactor can…
I bet you're worried about your index fragmentation.
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 phone book organized by last name, first name.
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 5% fragmented
Monthly - 17% fragmented
Weekly - 3% fragmented
Daily - 6% fragmented
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?
