Monitoring Microsoft SQL Server using Enterprise Manager Grid Control

The Enterprise Manager offers several system monitoring plug-ins for third-party products. One of them is Microsoft SQL Server Plug-in. This plug-in supports Standard, Enterprise, and Workgroup editions of Microsoft SQL Server 2000, Microsoft SQL Server 2005, and Microsoft SQL Server 2008 (both 32 and 64bits).

I have tried to prepare step by step guides to demonstrate how to install Grid Control 11 (to Linux) and how to deploy agent to windows in my previous posts:

How to install Grid Control 11g
How to deploy Grid Control agent to a windows server

Now, I’ll show how to deploy this plug-in to a windows server we already registered with our Grid Control. Microsoft SQL Server Monitoring Plug-in can be deployed to any machine which can connect to target SQL Server service, but I prefer to deploy it to the host which MS SQL Server is installed.

First, we’ll download the plug-in from Oracle website (download r12):

http://www.oracle.com/technetwork/oem/grid-control/downloads/devlic-099348.html

Then we’ll import the plug-in file into our EM Grid Control.

Login to the Grid Control, click “setup” and then click “management plug-ins”. You’ll see the list of plugins which are already installed. Click “import” button.

How to Find Unused Indexes

You can use Monitoring feature of Oracle to find which indexes are unused. You can run the following query to enable monitoring on an index:

ALTER INDEX index_name MONITORING USAGE;

After you enable indexing, you should wait for a while (for example 1-2 days if you think that this index should be used daily), and then you can query v$object_view to see if index is used:

You can run the following query to disable monitoring of an index:

ALTER INDEX index_name NOMONITORING USAGE;

Disabling monitoring an index, does not change the “used” value on v$object_usage; If you query that view, you should still use the index as used. Because the “used” property shows if index is used between start_monitoring and end_monitoring period.