According to Microsoft SQL Master, Brent Ozar, he’s made some terrible database performance tuning decisions throughout his career. Luckily for us, we can benefit from his mistakes and don’t have to figure everything out on our own. He’s shared his hard-earned wisdom for free during Quest’s Database Training Days webcast series.
In one of his sessions, we learned “Why defragmenting your indexes isn’t helping.” In fact, it could be making your database performance worse, and Brent filled us in on why. Along the way, he emphasized the importance of knowing what you’re measuring when it comes to optimizing SQL Server performance.
Internal vs. external fragmentation
Brent gave us a quick tutorial on the way SQL Server stores data in 8 KB “pages.” In a new or rebuilt index, the pages are all full and stored in order. But, as more data gets added, pages become split: not all pages are full and they occur out of order. This is the vital difference between internal and external fragmentation:
- External fragmentation – refers to pages being out of order
- Internal fragmentation – refers to the empty space on a page
Focusing less on-page splits
Many database professionals focus on page splits as a measure of database fragmentation, however, Brent explained that this number is meaningless because page splits occur both when adding a new row to an empty table and when adding a new page. So, it’s not helpful after all.
How external fragmentation can make things worse
In this session, Brent asserted that external fragmentation is not a useful measure of database performance since page order doesn’t have much of an impact on the speed of maintenance tasks, running queries in RAM or reading data from disk. So, database professionals who try to fix external fragmentation by reorganizing and rebuilding indexes are actually making performance worse by inflating backups and using up more maintenance window time.
Database professionals who try to reduce external fragmentation by leaving room on pages through setting a fill factor are also causing a problem worse than the one they’re trying to fix. This is largely because you almost never have to insert data at a point mid-way in the index. So, trying to keep pages in order by putting less data on each individual page is actually causing internal fragmentation.
Monitoring wait time
What should you do instead? Brent advises setting the fill factor to the default of 100% (or at least 80% or higher) and then rebuilding the indexes to pack them again. Next, focus on monitoring the right performance tuning number – wait time. One of the best ways to view various aspects of wait time across your database instances is by using a performance monitoring tool to pinpoint exactly where processes are bogging down.
For even more of Brent’s insights on index fragmentation, wait time stats and what you should be doing about index maintenance, listen to the webcast on-demand.
You can also access more expert advice on database performance through Quest’s Database Training Days.
