Red Gate – SQL Index Manager – Beta

Red Gate logoOnce again, those lovely chaps at Red Gate have come up with a new tool for the DBA – SQL Index Manager, and Grant “Scary” Fritchey (blog|twitter) has even done a video about it on YouTube.

So, let’s download it and fire it up.

I’ve obscured the name of the server I’m connecting to…

Shortly after starting the tool, it has analyzed all the databases on that server to which I have access, and returns the results:

As you can see, there are two options for action – “Rebuild” or “Reorganize”.  Click on those problems that you would like to fix, and hit the “Fix Indexes” button.  This gives you the option to run the recommended actions immediately, or will give you the SQL script so you can run it as & when you want.  If you click “Fix Now”, it’ll take you to the results grid where you can see what’s going on.

And that’s it – nice and easy.

However…

As it stands, there are no options to change behaviour (eg by setting up your own thresholds, or just selecting one database to scan).  As such, this feels either like an early Beta, or a tool that is aimed more at the “accidental” or new DBA.

The thing that I find confusing is also the “Index fragmentation %” – I would (personally) have assumed that a higher number is indicative of a more fragmented index, but apparently this tool thinks otherwise.  Checking the TechNet documentation linked above indicates that MS seems to be on my side…

There are a few other behavioural hiccups that need addressing, and I’ve sent a little list to send back to Red Gate.  You can help too – download the free beta, play with it, and use the “Give feedback” button to, erm, give feedback via email.

And now to see if it’ll play nicely with SQL Server 2000… I may be some time…Game over rather quickly. Heigh ho.

Bother.

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , . Bookmark the permalink.

6 Responses to Red Gate – SQL Index Manager – Beta

  1. Hi! I’m one of the developers who worked on SQL Index Manager at Red Gate. First of all, many thanks for taking the time to try it out and submit feedback. Let me give you some background: every few months we have a “down tools week” where we stop working on our day to day stuff and spend a week doing whatever we want. During one such week, a few of use decided to make SQL Index Manager on the back of a suggestion made by Grant for a tool to manage some of the basics for Accidental DBAs. The team consisted of a small number of developers, *no* testers (not the norm for development at Red Gate), and some input from a couple of dedicated User Experience people. Unfortunately the first day was a bit of a wash out – we ran into some unexpected problems working with our build system. So for the remaining four days, we basically threw together what has become the beta download. As we were very rushed, we had to concentrate on just the basic workflow of connecting to a server, examining the indexes, working out if and how they need to be fixed, and then carrying out the fixes. If you stay on that workflow, it kinda works, most of the time. If you try to do anything else, well, you’re likely to run into bugs and UX problems – sorry about that! We could have spent more time on it to iron out some of the more obvious bugs and missing functionality, but we thought it would be more worthwhile to just release it as is, and see what the response would be before investing more time on it.

    As it turns out, the response has been pretty good so far. Personally, I expected lots of complaints about the number of bugs and the many UI deficiencies (and, oh, there are so many!). What I was hoping for was lots of feedback about how useful the tool *could* be, and what needs to change to make it so. So far, I’ve been pleasantly surprised by the feedback it’s receiving. Yes, plenty of people have mentioned the bugs, obviously, but pretty much everyone who has provided feedback has been very positive about the tool and has provided useful suggestions for improvement, so I’m hoping that the powers that be here at Red Gate will choose to invest further time on it. If that happens, I think you can expect to see more frequent releases with plenty of incremental improvements.

    Let me clarify a few things about how it works. When assessing each index, we report its fragmentation percentage. High numbers are bad, and lower numbers are good, as you would expect. In general, anything that’s more than 30% fragmented is considered a high priority, and the suggested fix is normally to rebuild the index. Anything between 10% and 30% is considered medium priority, and the suggested fix is normally to reorganize the index. However, we also consider the size of the index. Anything less than 100 pages is basically ignored. Such indexes are considered to be small enough that fragmentation isn’t a performance concern for them, and the rebuilding or reorganizing process frequently doesn’t actually improve them. For those indexes that need to be fixed, we further sort them within their priority bands according to a “sickness” estimate. This is based on a combination of the fragmentation percentage and the size, which is why the indexes don’t appear to be sorted in order of either property (the sickness score is essentially the product of the fragmentation percentage and the size). This might seem a somewhat arbitrary scheme, but we found that it does a better job of pushing the more problem indexes to the top of the list that simply sorting by either fragmentation percentage or index size alone, and it is proportional to the absolute level of fragmentation in each index.

    Finally, the suggested fix is not just based on the fragmentation percentage. There are some indexes that need to be rebuilt rather than reorganized, and vice versa, depending on the settings of the table and columns that the index is based on.

    Anyway, I’ve probably typed enough for now (well, it’s dinner time here in the UK, and I’m getting hungry), so thanks again for trying out SQL Index Manager, and if you have any further feedback, please don’t hesitate to get in touch.

    Chris

    • thomasrushton says:

      Hi Chris

      Thanks for taking the time to type rather more in one comment than I typed in my entire blog post!

      I’ve heard about your “down tools” weeks before, and, I have to say, it seems like a really good idea – both from the point of view of you guys, who get to spend time doing something that might be a bit more fun, and from the perspective of the company who gets happier developers who feel more appreciated. I’m always impressed with the quality of what you manage to turn out in a week (the Tab manager tool, for example). Here’s hoping that this tool gets more development time.

      And thanks for providing more information about the internals of the product – what you say makes sense!

      Now, I’ve got this great idea for another tool…

  2. Thank you for reviewing SQL Index Manager.

    I’m pleased to announce that we have a second beta available. Based on user feedback made a few improvements in this latest version:

    • You can now select an individual database to analyse at a time
    • The index information is now sortable
    • The index fragmentation and size thresholds are now adjustable in the options dialog
    • The last connected to database instance is remembered for next time
    • We’ve fixed a few bugs and given the look and feel a bit more love

    The new version can be downloaded from:

    http://www.red-gate.com/products/dba/sql-index-manager/

    • thomasrushton says:

      Hi Daniel

      Thanks for coming by with the good news about the new Beta. I’ll download it and have a play next week.

  3. Pingback: Red Gate – SQL Index Manager – Beta 2 | The Lone DBA

  4. Pingback: Red Gate – SQL Server Index Manager – Beta 2 | The Lone DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s