SQL: Understanding Database Fragmentation

It is no secret that I am new to supporting, maintaining, and monitoring SQL databases.  I have been a sponge recently trying learn all that I can.  I figure that maintenance plans were the most basic yet essential place to start.  As many of you already know there is a tremendous amount of technical information out there, but I found I was spending a lot of time bouncing from page to page wishing it would break it down the very basics and spoon feed it to me.  I got a copy of “Brad’s Sure Guide to SQL Server Maintenance Plans” by Brad McGehee, from Amazon, as a place to start.  He also offers the book in a downloadable pdf on his blog.

This book is a very good place to start if you have technical skills but not specifically in SQL maintenance.  He really details every step to set up basic maintenance plans and send email reports of the results.  It is exactly where I needed to start.

Let me back up a little… I learned several weeks ago through the grapevine from some coworkers that an important database was showing as very fragmented.  A sweet tool by SolarWinds told us that, but I couldn’t find an entry-level, beginners method to handle the fragmentation.  Apparently I wasn’t Googling the right terms so I felt overwhelmed.  As any good and wise technical person knows, you don’t just run any script from the internet on your database. 🙂

Today I spent the day reading Brad’s book and figuring out how to address our fragmentation issue.  Through a variety of resources, I’ve learned to setup database mail, reorganize the indexes, update statistics, rebuild the indexes, identify the duration for the rebuild, then how to schedule it run weekly or even nightly.  (Note: I learned reorganizing and rebuilding but learned not to do them back to back as well as to run update statistics with reorganize but not rebuild.)  I write up all of this as a way to share some of the cool stuff that I used to see the issue, measure the issue, address the issue, and proactively manage it with a recurring solution.

Resource 1: Brad’s Sure Guide to SQL Server Maintenance Plans

Resource 2: Index Fragmentation Findings: Part 1, The Basics by Brent Ozar

Resource 3: Index Fragmentation Findings: Part 2, Size Matters by Brent Ozar

Resource 4: SQL query on the sys.dm_db_index_physical_stats object where I used information written by Arshad Ali’s Blog and also MSDN.

Select database_id
,object_id
,index_id
,partition_number
,index_type_desc
,alloc_unit_type_desc
,index_depth
,index_level
,avg_fragmentation_in_percent
,avg_fragment_size_in_pages
,page_count

From sys.dm_db_index_physical_stats (db_id(N'database_name'), NULL, NULL, NULL , 'DETAILED') IPS --replace database_name with the name of your database

Where page_count > 8
and avg_fragmentation_in_percent <> 0

This query took several minutes to run on my 42 GB database so don’t expect a rapid response.  I was able to take this out to Microsoft Excel to really analyze the data and sort by avg_fragmentation_in_percent field.  I was able to see substantial change in the statistics after performing a rebuild.  I learned that the page count matters.  If the page count is small then high fragmentation is necessary, and there isn’t really anything that you can do about it.  That is why in the code above I actually filtered out when the page_count was less than 8.  I also don’t want to wade through all of the additional rows of results where the avg_fragmentation_in_percent was equal to zero.   On Monday, I will incorporate all of this stuff into a maintenance plan on a recurring schedule.

I hope that this information makes sense and helps you in your journey.

Thank you.  Be safe.