How to monitor index fragmentation in Spotlight on SQL Server Enterprise

Hello, my name is Jason Hall. 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 home page metrics, as well as a fragmentation drilldown 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 the 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 drilldown. This drilldown is also found in the Databases drilldown Fragmentation Indexes tab. And effectively, this drilldown is going to show me the fragmentation levels of each of my large indexes on the system. Once the drilldown 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 drilldown 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 the clipboard, and then execute in Management Studio or schedule via a job, if needed.