My latest Pluralsight course has been published!
It’s called SQL Server: Index Fragmentation Internals, Analysis, and Solutions and is just over 2.5 hours long.
The modules are:
- Introduction
- Index Structure and Index Uses
- Types of Index Fragmentation
- Causes of Index Fragmentation
- Detecting Index Fragmentation
- Avoiding Index Fragmentation
- Removing Index Fragmentation
Check it out here.
We now have more than 130 hours of SQLskills online training available, all for as little as $29/month through Pluralsight (including more than four thousand other developer and IT training courses). That’s unbeatable value that you can’t afford to ignore.
4 thoughts on “New course: Index Fragmentation Internals, Analysis, and Solutions”
Hey, Paul! Thanks a lot for all the stuff you post out there for free.
I have implemented many cool things based on your posts and experiments.
Now there is one question i cannot find an answer to: sometimes I see indexes with a lot of free space on pages like 40-60%. this is bad, I know, as it leads us to wasted space on disks and in Ram. But ‘smart’ reindex scripts (like Ola Hallengren’s) do not address avg_page_space_used_in_percent, they address avg_fragmentation_in_percent. But fragmentation there is ok (like 5-10%) and a lot of free space. So the question is – should I write my own script (or are there any?) to take emty space on pages into account and how to do that correctly (as scanning everythin will take ages)?
I will really appreciate your professional answer. I couldn’t find one anywhere or even at dba.stackexchange.com…
If you think that having the free space is causing a perf issue, then yes. See https://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/
Thanks a lot!!! But one part of the question is still confusing to me. If I scan the entire dataase to find empty space it will probably cost me much than just leave it as it is… Are there workarounds?
Scan – do you mean running the DMV? That’s a tiny cost compared to the potential ongoing problems from having all the wasted space. But it’s up to you.