In this article, we’re going to be taking a look at Index Manager for SQL Server, from Devart. See what it has to offer for users like you and me and if it is a tool that you can integrate into your professional workflow.
There will be times when query performance problems are going to be tied to indexes, so this tool is a perfect fit to help you navigate that road. Every professional DBA should have a way to know the status of the index structures within all the databases under his/her umbrella; that way, he/she can adopt a proactive approach instead of a reactive one to help prevent performance issues due to fragmentation within the index structures.
Keep in mind that this targets on-premise deployments of SQL Server, Azure SQL Managed Instance, and/or SQL Server deployed in an Azure VM and Azure SQL DB. Also, if your database infrastructure is heavily based on Solid State Drives, then index fragmentation will be the least of your problems (it’s costly but totally worth it).
Installation
You can get SQL Index Manager by clicking on the “Get Trial” button. Doing so will also include the whole suite of SQL Server Management Studio extensions, which adds a lot of extra value.
Note: Make sure you have installed SQL Server Management Studio 2012 or higher to proceed.
After you completing the installation, head straight to SQL Server Management Studio and open it. Once opened, establish a connection to a SQL Server instance and go to any of your databases, and right-click on it.
This is what you’ll be seeing:
As you can see, the only option you get is “Manage Index Fragmentation”. The “Help” one is simply information about the product, nothing pragmatical within the tool itself.
Once I click on “Manage Index Fragmentation”, this is what I get:
You can immediately see that, for my particular database, found no fragmented indexes, so no actions are required. I know that’s not very helpful, so let me prepare a better sample database to show you what the tool offers (at least you get to see what output you get when everything is fine ;) ).
Ok, so this is what I now see after a 2nd attempt:
I created a new table, created a non-clustered index over one of the columns, set the fill factor to 50%, and inserted a bit more than a million records of purely random data. So now, the tool shows us an item that must be addressed, so let me cover that and the rest of the visible elements within the tool.
So, in the “Action required” section, you can see that it’s telling me that the non-clustered index I created has a 99.01% of fragmentation and that the “Fix Type” suggested is a Rebuild. Let me make a quick pause here because I have to mention something important here:
I’ve seen that many states that the ideal course of action is to reorganize your indexes if the fragmentation percent is between 10% - 30% and to rebuild if the fragmentation is higher than 30%. However, I’d suggest you decide that based on your particular scenario; regardless, if you click the “Options” button in the application, you’ll see that Devart has these same values set right out of the box, so make sure to test thoroughly.
*Be very careful with that “Online” checkbox because if it’s not ticked, then an index rebuild operation will block the entire table to make it happen, and that could negatively impact your business operations. If you’re running this in a sandbox environment, that shouldn’t be that big of a deal.
For demonstration purposes, I’m going to execute the index operations with the values as they are:
*By the way, if you click on the “Export to CSV” button, you can generate a report that you can either share with your Manager, fellow DBAs, or simply for yourself.
With that out of the way, to proceed with my index operations, I click the checkbox on each one and hit the “Fix” button:
You can also script the actions that the tool intends to apply so that you can address them at your convenience.
After the index operation is complete, you can hit the “Reanalyze” button to see a new status:
And as you can see, the previously reported fragmented index was taken care of, and everything is looking good for my indexes ;).
Final thoughts
● dbForge Index Manager gives you suitable options to manage your index structures. Index Management is a crucial task that every DBA should have within their umbrella, and this tool comes right to the rescue.
● I’m not going to lie, I would’ve liked to see something to automate index management within my SQL Server instance, but I also know that the Devart team is constantly making improvements to their entire suite, so I’m sure it will only be a matter of time.
● If you’d like to get even more information, you can head to the official documentation center.
● Something that I didn’t cover during the development of this post, is the possibility that the tool provides to perform operations using the command line. This opens the door for you to automate index operations using PowerShell and/or Windows Scheduled Tasks, cool!
Top comments (0)