SharePoint 2013–Crawl DB Fragmented Indices

What is a Fragmented Index?

Well, you might be familiar with the term “fragment” when speaking in SQL tongue. If not, then very basically; data is stored at the table layer within SQL and when the location of that data is controlled the data is grouped in rows and index keys within the table, according to whatever scheme is applied to it. When an index has pages in a logical order that, depending on their key values, do not match the physical ordering inside the data file, it is considered “fragmented”, that is to say the “fragments” of data are “not in order”. In a healthy state, the physical ordering of the pages match the logical ordering, achieving maximum throughput for data retrieval and storage. An example of how an index can become quickly fragmented is when rows are removed with the DELETE statement, thus the data in the page is left as whitespace. This in turn, means less rows can be stored per page which ultimately leads to an increase in page reads. Still with me? SharePoint is a read-intensive beast when it comes to SQL, so performance can be significantly degraded as a result of those extra reads.
I digress, this isn’t a post about SQL fragmentation, it’s a big subject and maybe I’ll do a write up one day, but for now let’s get back on topic; your crawl database has fragmented indices and you want to know what to do about it.

How do you fix it?

Well, that bit is pretty simple.

  • Head over to Central Administration > Monitoring then click “Review rule definition” in the Health Analyzer group
  • Under the “Category – Performance” grouping, look for “Search – One or more crawl databases may have fragmented indices” and click Edit from the ribbon
  • Click the “Run Now” button and away it goes

But wait, you want to monitor it, right?

Yes, this is probably a good idea if fragmented indices are plaguing your environment. From the same Rule Definition as before, check the “Enabled” box and click “Save” from the ribbon. The Health Analyzer will now deliver reports of fragmented index issues right into your Review Problems and Solutions page. Sorted.

About the author