SBS Monitoring–Fixing high resource usage

One (of many) bugbears of Windows SBS 2008 is the SBS Monitoring feature which probes the network for WMI information.  This process has been known to chew up a lot of resources at time and also cause huge disk IO.

Without even looking for this fix, I stumbled across a post on the Microsoft Social forums and thought it quite interesting.  I knew of the perfect server to put this on and at the very moment the SQL query finished, the CPU usage had dropped and disk IO from the database resumed to a near idle state.

I can not claim glory for this – but hope to definitely share to others who need.
Run this query if any of the below meets your criteria…

• High Disk IO – Resource  Monitor reports high read/write for SBSMonitoring.mdf
• Moderate to high CPU usage on the SQLServr.exe responsible for the SBS Monitoring
• SBS Console becomes unresponsive or takes a long time to open

Perform this query in SQL Management Studio while connected to the SBSMonitoring instance

USE SBSMONITORING
GO
CREATE NONCLUSTERED INDEX [IDX_Alerts_GetAlertsPerID] ON [dbo].[Alerts] 
(
   [DefinitionID] ASC,
   [ComputerID] ASC,
   [DateOccured] ASC,
   [IsSet] ASC,
   [ID] ASC
)
GO
CREATE NONCLUSTERED INDEX [IDX_WMICollectedData_GetSecurityProductPerComputer] ON [dbo].[WMICollectedData] 
(
   [WMIInstanceID] ASC,
   [WMIPropertyID] ASC,
   [DateCollected] ASC,
  [ID] ASC
)
GO

create index IDX_WMIObjectProperties_ObjectID on WMIObjectProperties(ObjectID,ID)
Go

create index IDX_WMIObjectInstances_ObjectID on WMIObjectInstances(ObjectID,ID)