Hello, my name is Jason Hall with Dell Software. And today we're going to go over a feature of Spotlight on SQL Server Enterprise, index fragmentation monitoring. Index fragmentation monitoring is a new feature in version 10.0 of Spotlight that includes a couple of capabilities.
The first is an alarm that's going to track for you whether any large indexes require rebuilding. And then there's homepage metrics as well as a fragmentation drill down that will both show you fragmentation levels across your indexes and allow you to quickly generate a defragmentation script should any indexes require reorganizing or rebuilding. Let's take a look at this feature in action.
So first of all, here we can see the heat map in Spotlight. And specifically, I have a server over here on the right. And you'll notice by mousing over that server, it tells me that there's a large index that requires rebuilding. The alarm configuration is very simple. If we go into Configure Alarms and scroll down to I, you'll see a new index fragmentation alarm.
This alarm has default settings that fire a yellow or low level alarm when fragmentation levels fall between 5% and 30%, and a orange or medium level alarm when fragmentation levels fall above 30%. Those are our default thresholds. And as with all thresholds in Spotlight, those can be configured by the customer. Now, by default this does only apply to large indexes, and those are indexes of greater than 1,000 pages. So we're not going to flag alarms for smaller or less critical indexes.
Now that we see that alarm that's been fired, if we click into the home page for a server, you'll also notice two new home page metrics in the lower right hand corner. These track both the average fragmentation level of your large indexes as well as the maximum fragmentation level of any large index. So there we can see our orange level or medium level alarm that I do have a critically fragmented index.
Clicking into that alarm allows me to navigate into the diagnostics drill down. This drill down is also found in the databases drill down fragmentation indexes tab. And effectively, this drill down is going to show me the fragmentation levels of each of my large indexes on the system. Once the drill down loads, we'll be able to modify the settings as well to include any number of indexes we want to look at.
We can also filter by a particular database, if I'm only looking for indexes in a specific database, as well as set a minimum page count for indexes that we want to view. Looking at our Contoso retail data warehouse database, we can then see, again, our top 50 indexes of greater than 1,000 pages sorted by their fragmentation levels.
And once the drill down loads, you can see each of those indexes. You'll see some information about the index, including their fragmentation level and page count over here on the right hand side of the screen. And then based on that fragmentation level, whether it's above 30% or not, we'll recommend either a rebuild or a reorg of the index.
And you can quickly generate that defragmentation script using the button in the upper right hand corner or a right click action and Spotlight will generate for you the script needed to rebuild that object. And again, it will either be a rebuild or a reorg based on your thresholds. We can take the script, we can copy to clipboard, and then execute in Management Studio or schedule via a job if needed.