8 Replies Latest reply on Jan 21, 2013 7:32 AM by derekr

    EPO SQL Database

      Hi All

       

      I am a SQL DBA and one of the databases I look after is the Mcafee EPO Database. I'm not that clued up with the ins and outs of Mcafee so please bear with me

       

      I have been administering the EPO database and recently I have noticed extreme growth on the database.

       

      I narrowed down the growth to one table (EPOEvents). I have recently implemented a weekly purge to remove data older than a certain date, this helped the situation at first but it seems that more data is coming in than going out.

       

      Having scrutinized the table structure, I noticed that there are 22 indexes on the table, this is not normal for any table.

       

      For those that don't know, when data is inserted into a table, all indexes are updated with the data as well, this is fine if the indexes are being used in data retrieval (select statements). In my case, a few of the indexes have never been read from but are constantly being updated, this causes huge index growth which in-turn causes huge table growth and therefore database growth.

       

       

      My question: Is it ok to delete these unused indexes from the database?

       

      Any Ideas or pointers?

       

       

      Thanks

        • 1. Re: EPO SQL Database
          Sailendra Pamidi

          hi Derek,

           

          To answer your question - deleting or adding indexes without explicit approval from Engineering is not a supported step.  Have you checked if the database has been defragmented (REBUILD INDEX)? KB67184 discusses this.

           

          I would also suggest filtering events which you don't require for reporting - so they don't get sent to ePO in the first place.

           

          .

          • 2. Re: EPO SQL Database

            Thanks Sailendra Pamidi

             

            How would I go about getting approval from Engineering?

             

            The indexes are quite fragmented 50%+. I rebuild the indexes every week, this creates temporary breathing room because the amout of data being pumped in greatly exceeds the space reclaimed from rebuilding the indexes

             

            As for filtering events, we have already removed unnecessary events from being reported to the database.

             

            My point in question here is, why has Mcafee chosen to create indexes that never get queried from? The cost of SQL Server maintaining these indexes is huge, especially when there is no performance benefit from having them there in the first place.

             

            Thanks

             

            Message was edited by: derekr on 1/21/13 5:57:06 AM CST
            • 3. Re: EPO SQL Database
              Sailendra Pamidi

              I agree - the cost of maintaining indexes is significant - especially if you have a bunch of them that are never touched but still will be updated anyway.

               

              The indexes added to the EPOEvents and other tables are in response to various customer scenarios and internal testing done. Usually when an issue is noticed in the field, or comes up in internal testing by our QA team, which significantly affects performance of certain queries, Dev. may arrive at a decision to tweak or add new indexes.

               

              These are usually based on validation done both by customer and QA teams where the reported issue has been resolved or performance of the ePO/SQL queries improved upon adding the indexes.

               

              In your particular case, it is likely that you may not be using more of the queries which require SQL to touch upon the indexes.

               

              As you are probably aware - updating the statistics should help SQL come up with better exectuion plans. Since you do already rebuild indexes (i am assuming you rebuild all of them irrespective of their fragmentation?), the statisics are updated at that time and with reduced fragmentation the performance is ok for a while.

               

              Please check if you can run a manual statitics update with (UPDATE STATISTICS) every few days with a high sample rate (for e.g. 70 percent scan) and check if that makes a difference in the index usage by ePO/SQL Queries.

               

              If there is no change in the outcome, please log a case wtih Support describing your issue and any evidence of the negative impact as a result of that for review by Tier 3 or Engineering as required.

              • 4. Re: EPO SQL Database

                Thanks Sailendra

                 

                What I have noticed is that there are a few indexes whose names start with 'dta' - These indexes are indexes suggested by SQL Server when running a SQL Profiler Trace trace on a system and then loading the Trace into SQL Server's DTA (Database engine Tuning Advisor). These indexes are advisable for THAT particular system where the trace was run on, I find it extremely worrying that Mcafee assumes that it will be fine for every system. There are many things that can affect the outcome of using Database engine Tuning Advisor, for example Statistics.

                 

                I rebuild all the indexes on the table, including the Clustered index (Even though there is never fragmentation on the clustered index).

                 

                Performance is a concern for me but not my main concern, my main concern is the excessive growth. The server has a decent amount of memory so I'm getting +-99% Hit Ratios, therefore I haven't seen the performance degradation of the fragmentation..........yet.

                 

                As for Statistics, because of the recent growth, I update statistics on the database every night, with 100% sample rate. And still, there are indexes that are not being used.

                 

                Something else that I have noticed is that some of the indexes have duplicate Leading keys, also there are a few nonclustered indexes with the same leading column as the clustering key.

                 

                Thanks

                • 5. Re: EPO SQL Database
                  Sailendra Pamidi

                  Hi Derek.

                   

                  Thanks for the clarifications. Its true that the ePO database has some of the indexes probably suggested when running Database Tuning Advisor - in addition to the ones added by Engineering themselves. But please rest assured that these indexes themselves are used by queries not just restricted to the customer who reported them.

                   

                  For e.g. We did get several customers who reported performance issues with certain VirusScan queries which make use of the underlying ePO tables (EPOProductProperties, EPOProductVersions etc. for example) . The details of which are not usually documented since it is transparent to the ePO application.

                   

                  However, this does not mean that all customers will have the same environment and will therefore benefit from a solution provided to address a particular group of customers who report an issue. My understanding is that Mcafee Engineering team attempts to take a balanced approach and usually provides the additional indexes as a POC (Proof of Concept) initially - and available upon request from Support.

                   

                  After monitoring the situation and if increasing number of customers request for it, the decision is made to include these additional indexes based on the feedback from the field.

                   

                  Again I would request to please log a support case detailing your concerns and observations along with the details of your environment and any supporting SQL Reports to help highlight the issue.

                   

                  Regards,

                  Sailendra

                  • 6. Re: EPO SQL Database

                    Thanks for your assistance with this matter.

                     

                    How would I go about logging a support case?

                     

                    Thanks

                     

                    Derek

                    • 7. Re: EPO SQL Database
                      Sailendra Pamidi

                      No problem.

                       

                      You can use the Service Portal website to log a ticket with McAfee Support:

                      https://mysupport.mcafee.com/Eservice/Default.aspx

                       

                      You will need your login details to log a service request. This is usually provided when you register using the Grant Number provided at the time of purchasing.

                      If you have your Grant number handy, click on the New user link to register and then proceed to log a new service request. Hope that helps.

                      • 8. Re: EPO SQL Database

                        Thanks, it does help

                         

                        I was hoping that I can log a ticket with my user name and password I registered on this website.

                         

                        I'll have to chat to the Mcafee admin team to obtain the necessary

                         

                        Thanks Again

                         

                        Derek