ME Trial 1 – Index Stats

ME Trial / By

minion enterprise-01Giving you the info!

Welcome to your 30 day Minion Enterprise trial! We know that you might not have installed Minion Enterprise yet, because things do tend to jump in the way. But whether you have or not, we’ll send you an email every other day for the next 30 days, to show you around and see some of the cool things ME can do.

Be sure you walk through the following “Quick Start” sections in the Minion Enterprise Introduction document, to get Minion Enterprise up and running:

  • System Requirements and Installation
  • Installing Minion Enterprise
  • Initial Configuration

 

Related video: Introducing Minion Enterprise

Minion Enterprise collects data into tables

Minion Enterprise gathers data from all managed servers into tables in the Collector schema. The moment you configure an instance in the dbo.Servers table, Minion Enterprise begins pulling in a stunning array of data: service status, disk space, backups, database properties, logins and users, table sizes, and on and on. All of this data from across your enterprise is now updated and available in one central repository, ready for alerts, and reporting, and planning for the future.

Your first module: Index Stats

The Index Stats module collects detailed index information into the Minion Enterprise repository. It gets all data from sys.indexes on your managed servers; plus, the list of indexed columns (and included columns); and more. This allows you to perform detailed, enterprise-wide index analysis.

What can we do with this? Well really, anything you want. While Minion Enterprise comes with a pretty big set of stored procedures to help you seek out* good solid info – like clustered GUIDs, duplicated indexes, missing indexes, and more – the data is all there and freely available to you in tables. So, put together any query or stored procedure you like, to get the information you want! One idea though: you can re-create dropped or modified indexes – or compare indexes over time to see if they’re changed – very easily, because ME saves the CREATE INDEX and DROP INDEX statements to the table!

*See what we did there? Seek? It’s an index pun.

Objects in the Index Stats Module

Tables

  • IndexStats – Stores the collections of index data.

Views

  • Collector.IndexStatsCurrent – Provides the most recent collection of index data.
  • Collector.IndexStatsPrevious – Provides the next-to-most recent collection of index data.

NOTE: You’ll find that all of the Collector.% tables  have “Current” and “Previous” views. These views are a really handy way to grab the most recent collections, or compare recent changes within a module!

Stored Procedures

Each of the Index Stats module Report.% procedures have five ways to query the data:

  • byAll – meaning, all active servers in the dbo.Servers table
  • byApp – meaning, filter by application as listed in the dbo.Application table. (Set up server-application relationships using the Setup.ServerToApplication stored procedure.)
  • byID – meaning, the InstanceID as listed in dbo.Servers
  • byServerName – filter by the ServerName (as listed in dbo.Servers)
  • bySLA – filter by the ServiceLevel (Gold, Silver, Bronze), as listed in dbo.Servers

And now, the Index Stats stored procedures:

  • GUIDs: The GUID reports detail those tables with clustered UNIQUEIDENTIFIERS.
    • IndexClusteredGUIDsByAll
    • IndexClusteredGUIDsByApp
    • IndexClusteredGUIDsByID
    • IndexClusteredGUIDsByServerName
    • IndexClusteredGUIDsBySLA
  • Duplicate indexes: The duplicate indexes report detail those tables with indexes that are exact duplicates of one another.
    • IndexDuplicateIndexesByAll
    • IndexDuplicateIndexesByApp
    • IndexDuplicateIndexesByID
    • IndexDuplicateIndexesByServerName
    • IndexDuplicateIndexesBySLA
  • Heaps: The heaps report details those tables with no clustered index.
    • IndexHeapsByAll
    • IndexHeapsByApp
    • IndexHeapsByID
    • IndexHeapsByServerName
    • IndexHeapsBySLA
  • Low use: The low use report details those indexes that are very low use. These indexes are sometimes good candidates to be dropped; however, be careful not to drop indexes that are seasonally critical (e.g., for quarterly reports).
    • IndexLowUseIndexesByAll
    • IndexLowUseIndexesByApp
    • IndexLowUseIndexesByID
    • IndexLowUseIndexesByServerName
    • IndexLowUseIndexesBySLA
  • Missing indexes: The missing indexes report details missing index information, pulled from SQL Server management views. These indexes are sometimes good candidates to create, but be sure to evaluate each suggestion against need and existing indexes.
    • IndexMissingIndexesByAll
    • IndexMissingIndexesByApp
    • IndexMissingIndexesByID
    • IndexMissingIndexesByServerName
    • IndexMissingIndexesBySLA

So there’s a good start on the Index Stats module!

Write to us at Sales@MinionWare.net for a demo, a trial, and a quote. Write to us at https://minionware.freshdesk.com  for help and suggestions. And, get more information on our Minion Enterprise YouTube playlist.

Next time we’ll talk about two stellar modules: Database Files, and Script Schema!