locked
Solved: Why is SBSMONITORING Slow/Using all my processor resources? RRS feed

  • General discussion

  • 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.

     

    Thursday, October 21, 2010 1:48 PM

All replies

  • 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:09 PM
  • 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
    Thursday, October 21, 2010 2:31 PM
  • This fixed the issue on a SBS2008 server that SBSMONITORING was causing a lot of hard disk activity. Bingo! Well done! Cudos!
    Tuesday, April 5, 2011 8:42 PM
  • 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 7:06 PM
  • 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:48 PM
  • Trust me, it has been reported.  I don't expect support problems.
    Tuesday, August 16, 2011 8:51 PM
  • 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%.
    Monday, October 3, 2011 11:09 AM
  • 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

     

    Tuesday, December 6, 2011 5:02 PM
  • 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:

      • Log on as an administrator.
      • Start SQL Server Management Studio. (Express edition is fine)
      • Attach to SBSMONITORING instance (replace SERVERNAME with the name of your server):
        Server Type: Database Engine
        Server Name: SERVERNAME\SBSMONITORING
        Authentication: Windows Authentication
      • Once connected, from the Menu, click: File -> New -> Query with Current Connection. This creates a new query window.
      • 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)
    
    Good day! Does this apply to SBS2003 as well?
    Wednesday, September 26, 2012 8:15 AM
  • No.  SBS 2003 doesn't have that database.

    Wednesday, September 26, 2012 1:46 PM
  • Hey man, you are a genius!!! Its evident that when the installation was new, the Alerts table was smaller and so it didnt cause problems.. But now it was killing my server performance. I applied the script and once finished, it started to decrease and now it doesnt even appear as a disk cosuming task!!

    THANK YOU!!!

    Saturday, September 29, 2012 11:28 PM
  • Thank you very much!!!
    Thursday, October 11, 2012 8:35 PM
  • Does this work for SBS 2011 ?
    Friday, October 19, 2012 5:19 PM
  • Benhaha:

    Once the query has finished executing do you need to take any additional action to make this query "permanent"?  I noticed that my performance improved DRAMATICALLY after the query but slowly returned to previous slowness. 

    Thanks

    Sunday, October 21, 2012 3:04 PM
  • Looks like it was a temporary situation as my disk queue length is down to an average of 0.00 for the last several hours.  This ends a journey of several months effort trying to figure out what was going on with my server.  Quite miraculous from my view point.  Thanks Benhaha!!!
    Sunday, October 21, 2012 6:06 PM
  • I have the same question as Ian. Does anyone know if this works for SBS 2011? As I can see some of these posts go back to 2010, is there an update that resolves this issue for SBS 2011?
    • Edited by 76Tim Thursday, April 11, 2013 4:34 AM
    Thursday, April 11, 2013 4:32 AM
  • I’ve tried running this and I get some errors. I still have 100% disk activity and
    it bogging down the server still. If I stop the SQL Server (SBSMONOTORING) disk
    activity drops to hardly anything. Any ideas?

    Msg 1913, Level 16, State 1, Line 1
    The operation failed because an index or statistics with name 'IDX_Alerts_GetAlertsPerID' already exists on table 'dbo.Alerts'.
    Msg 1913, Level 16, State 1, Line 1
    The operation failed because an index or statistics with name 'IDX_WMICollectedData_GetSecurityProductPerComputer' already exists on table 'dbo.WMICollectedData'.
    Msg 1913, Level 16, State 1, Line 2
    The operation failed because an index or statistics with name 'IDX_WMIObjectProperties_ObjectID' already exists on table 'WMIObjectProperties'.
    Msg 1913, Level 16, State 1, Line 2
    The operation failed because an index or statistics with name 'IDX_WMIObjectInstances_ObjectID' already exists on table 'WMIObjectInstances'.

    Tuesday, May 14, 2013 8:46 PM
  • Pardon my ignorance, and I know this is a couple of years after this was posted, but when exiting having made the change, we're then prompted to save the SQL instance: should we overwrite the instance that is offered or create a new one?

    netwodau

    Tuesday, May 21, 2013 5:29 AM
  • That's just mean you've ran these queries before and the indices have been created
    Thursday, January 23, 2014 10:29 PM
  • Thank you I confirm this solved high-memory problem on SBS 2011 standard. Just a side note, when connecting to SQL server instance have to run management studio as administrator
    Thursday, January 23, 2014 10:35 PM
  • Hello i was trying to run this query for lowering disk activity but it shows me some errors:

    The statement has been terminated.
    Msg 1101, Level 17, State 2, Line 1
    Could not allocate a new page for database 'SBSMonitoring' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
    The statement has been terminated.
    Msg 1101, Level 17, State 2, Line 1
    Could not allocate a new page for database 'SBSMonitoring' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
    Msg 1913, Level 16, State 1, Line 2
    The operation failed because an index or statistics with name 'IDX_WMIObjectProperties_ObjectID' already exists on table 'WMIObjectProperties'.
    The statement has been terminated.
    Msg 1101, Level 17, State 12, Line 2
    Could not allocate a new page for database 'SBSMonitoring' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

    I have enough space on disk. I am new to SQL could anyone help me. Ow i forgot te mention i have SBS2008 and i run this query with sql managament tools 2005 run as administrator.

    Tuesday, February 18, 2014 7:27 AM
  • Hello, i solved the problem. My database was over 4GB so i created a new one, the the command run succesfully.
    Tuesday, February 18, 2014 7:55 AM
  • Looks like I've got the same setup and problem that you had..  Can you let me know how to create a new database.. I don't work with SQL much so I'm not sure how to go about this..  Thanks

    Friday, July 25, 2014 2:46 PM
  • Thanks, you saved my day workt like acharm
    Friday, February 2, 2018 9:16 PM