Sign in
 
HomeLibraryWikiLearnGalleryDownloadsSupportForumsBlogs
Resources for IT Professionals > Forums Home > Windows Small Business Server Forums > Small Business Server > Solved: Why is SBSMONITORING Slow/Using all my processor resources?

Discussion Solved: Why is SBSMONITORING Slow/Using all my processor resources?

    •  
      Thursday, October 21, 2010 1:48 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      I hope this helps people.

      To apply this fix, connect to the SBSMONITORING instance using SQL Server Management Studio, and create the indexes as described.

      Inappropriate indexes to support function GetAlertsPerID

       

      SBSMonitoring will have a significant period of high processor usage every three minutes, using 100% of one CPU for about 30 seconds.

       

      Investigation showed that the offending call was GetAlertsPerComputer, which does a Cross Apply with GetAlertsPerID

      If you create this index to support GetAlertsPerID, the periods of 100% processor usage go away:

      CREATE NONCLUSTERED INDEX [IDX_Alerts_GetAlertsPerID] ON [dbo].[Alerts]

      (

            [DefinitionID] ASC,

            [ComputerID] ASC,

            [DateOccured] ASC,

            [IsSet] ASC,

            [ID] ASC

      )

       

      With this index in place, the regular spikes in processor usage vanished.

       

      Inappropriate indexes on WMICollectedData to support GetSecurityProductsPerComputer

       

      Investigation of remaining activity showed that a large part was taken up with executing GetSecurityProductsPerComputer.

      This uses a six-way join to get information about client computers. The estimated execution plan shows an inappropriate clustered index scan without seek on table WMICollected data on the subordinate branch of a nested scan. This is because there is no index on the join keys, WMIInstanceID and WMIPropertyID. There were also no indexes on the other foreign keys.

       

      CREATE NONCLUSTERED INDEX [IDX_WMICollectedData_GetSecurityProductPerComputer] ON [dbo].[WMICollectedData]

      (

            [WMIInstanceID] ASC,

            [WMIPropertyID] ASC,

            [DateCollected] ASC,

            [ID] ASC

      )

       

      create index IDX_WMIObjectInstances_ObjectID on WMIObjectInstances(ObjectID,ID)

      create index IDX_WMIObjectProperties_ObjectID on WMIObjectProperties(ObjectID,ID)

       

       

      Creating the first index took quite a while, but with these indexes in place, the processor and disk usage from SBSMONITORING instance of SQL Server dropped to virtually nothing.

       

       

    All Replies

    • Thursday, October 21, 2010 2:09 PM
      Moderator
       
       
      Sign In to Vote
      0
      Sign In to Vote

      For this crowd you'll need to blonde this down.

      To apply this to sql you need to run as admin, then attach to the monitoring instance and then do what?

       
    • Thursday, October 21, 2010 2:31 PM
       
        Has Code
      Sign In to Vote
      2
      Sign In to Vote

      OK good point. First, only do this if you are having this problem. This fix should be safe because it just creates additional non-unique indexes, but there are no guarantees when you mess around inside the guts of the software like this: Your warranty or support contract may be void.

      To perform the fix:

      1. Log on as an administrator.
      2. Start SQL Server Management Studio. (Express edition is fine)
      3. Attach to SBSMONITORING instance (replace SERVERNAME with the name of your server):
        Server Type: Database Engine
        Server Name: SERVERNAME\SBSMONITORING
        Authentication: Windows Authentication
      4. Once connected, from the Menu, click: File -> New -> Query with Current Connection. This creates a new query window.
      5. Copy the commands below into the window, then press F5 to execute.

       

       

      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)
      • Edited by Benhaha Thursday, October 21, 2010 2:33 PM typo
      •  
       
    • Tuesday, April 05, 2011 8:42 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      This fixed the issue on a SBS2008 server that SBSMONITORING was causing a lot of hard disk activity. Bingo! Well done! Cudos!
       
    • Tuesday, August 16, 2011 7:06 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Also fixed it for me in SBS 2008. Thanks for the easy cut and paste script.
      Byron Wright (http://byronwright.blogspot.com)
       
    • Tuesday, August 16, 2011 8:48 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      All credit to Benhaha BUT not being an expert would any of this have any bearing on any future microsoft support / further seperate issues sbsmonitoring / sql??

      Can just see it, call microsoft and ... this is not supported blah blah blah

       

      Should this not be reported to microsoft for input ?

       
    • Tuesday, August 16, 2011 8:51 PM
      Moderator
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Trust me, it has been reported.  I don't expect support problems.
       
    • Monday, October 03, 2011 11:09 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Thank you for the solution, but it did not work in my case. I've got SBS Monitoring utilizing 100% of drive bandwidth. After I've implemented the issue, instead of flat 100% disk usage the graph turned into spikes, but once in a while it is still flat 100%.
       
    • Tuesday, December 06, 2011 5:02 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      thank you thank you thank you! and thanks for "blonding" it up for me. sql is definitely not my strong suit.

       

      this fixed the issue on 6 of my clients's sbs2008 servers.

       

       

      i'm indebted to you