3 Replies Latest reply on May 30, 2014 1:45 PM by rob.hock

    Indexing Fields in Orion Database

    mike1843

      I have been monitoring our SQL server for Orion and found that our SQL Full Scans counter is fairly high.  We run on average at about 120/sec with a max of 2297/sec.  I have traced most of it down to my alerts.  I use multiple alerts where I use custom properties.  Disabling the alerting service drops my average down to 40/sec.  I looked at our indexes on the nodes table and found that a few custom properties have indexes created for them (which we did not create), but most do not.

       

      Is there any particular criteria that SW follows for automatically creating indexes?  Also, would there be any problems with us creating our own indexes on the custom properties?  We use our custom properties extensively in reporting, dependencies and dynamic groupings, so I would like improve the performance as much as possible.

        • Re: Indexing Fields in Orion Database
          rob.hock

          We would definitely encourage customers to create indexes for CPs. In more recent versions we will typically index any CPs that are not nvarchar(MAX), however CPs created under older versions and subsequently upgraded may not have indexes.

            • Re: Indexing Fields in Orion Database
              mike1843

              Thanks for the info.  I noticed that all of my fields that I need indexed are nvarchar(max).  I remember that in older versions of the custom property editor, we could actually change the size of the field.  Can I edit my field to be something like nvarchar(50) without hurting anything?  I understand that it may not be fully supported, but I make extremely heavy use of the properties. For example, we have around 150 sites.  I have depedencies set up for each site and one the dependencies for the site uses a dynamic group with the following criteria:

              Where Location_Name='Test Location' and AlertGroup not in ('switches', 'routers')


              Thanks in advance for your help.