6 Replies Latest reply on May 4, 2010 4:41 AM by JoeBidgood

    Database Optimization - Indexes

      Hello,

       

      Does anyone know if McAfee supports modifying any or all of the indexes configured by default on the EPO database?

      To resolve some performance issues I have a few indexes I would like to add.  And, more importantly, a few I would like to remove.

       

      Thanks!

       

      Sincerely,

       

      Dan B

        • 1. Re: Database Optimization - Indexes
          JoeBidgood

          Strictly speaking no, we don't support direct modification of the db unless directed by Support. (You'd be amazed at some of the... interesting modifications we've found in customer databases in the past )

          Can I ask - which indexes are you interested in removing, and why?

           

          Regards -

           

          Joe

          • 2. Re: Database Optimization - Indexes

            Hi Joe,

             

            Thanks for the quick reply.  I certainly understand what you mean!

             

            The number one candidates for me to remove are the following four indexes, all on the EPONotificationLog table:

             

            IX_EPONotificationLog_NotificationRuleName
            IX_EPONotificationLog_BranchNodePath
            IX_EPONotificationLog_NotificationType
            IX_EPONotificationLog_Status

            Next up are these two on EPOEvents:

             

            IX_EPOEvents_AutoGUID
            IX_EPOEvents_ReceivedUTC

            I show that these indexes have not been used by any seeks, scans or lookups in the past two months, but they have been updated nearly 60 million times.

            That is a lot of unnecessary work being done as these events are inserted and deleted.  If I can avoid that it would make me a very happy DBA.

             

            Details:

            TableNameIndexNameuser_seeksuser_scansuser_lookupsuser_updateslast_user_seeklast_user_scanlast_user_lookuplast_user_update
            EPONotificationLogIX_EPONotificationLog_NotificationRuleName000      13,398,780 NULLNULLNULL4/28/10 12:14 PM
            EPONotificationLogIX_EPONotificationLog_BranchNodePath000      13,398,780 NULLNULLNULL4/28/10 12:14 PM
            EPONotificationLogIX_EPONotificationLog_NotificationType000      13,398,780 NULLNULLNULL4/28/10 12:14 PM
            EPONotificationLogIX_EPONotificationLog_Status000      13,398,780 NULLNULLNULL4/28/10 12:14 PM
            EPOEventsIX_EPOEvents_AutoGUID000         1,950,529 NULLNULLNULL4/28/10 12:14 PM
            EPOEventsIX_EPOEvents_ReceivedUTC000         1,950,529 NULLNULLNULL4/28/10 12:14 PM

             

             

            Thanks!

             

            Sincerely,

             

            Dan B

            • 3. Re: Database Optimization - Indexes
              JoeBidgood

              That's a little strange. Do you not use the notifications system or run any event-based reports?

               

              Thanks -

               

              Joe

              • 4. Re: Database Optimization - Indexes

                I'll have to ask about the reporting.  I can tell you that the only indexes that get any use on the EPONotificationLog table are the following:

                 

                PK_EPONotificationLog    6695943 seeks
                IX_EPONotificationLog_Priority   6710 scans
                IX_EPONotificationLog_TimeNotificationSent 3926 seeks

                 

                Note that this activity is over a two month period.

                 

                Sincerely,

                 

                Dan B.

                • 5. Re: Database Optimization - Indexes

                  Hey Joe,

                   

                  Any more thoughts on this issue?  I am told that we do reporting, but mostly on demand.

                  I think the best bet is to remove the indexes and watch performance, but I don't want to negate any SLAs, etc..

                   

                  Thanks,

                   

                  Dan

                  • 6. Re: Database Optimization - Indexes
                    JoeBidgood

                    Hi...

                     

                    I think this is being worked in parallel - someone asked your question on one of the internal lists, and got a reply from one of the senior developers

                     

                    Basically:

                    The EPONotificationLog indexes have been removed in ePO 4.5

                    The EPOEvents indexes should *NOT* be removed under any circumstances

                     

                    He seemed fairly emphatic about it

                     

                    Regards -

                     

                    Joe