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.
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.
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.
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 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.
Thanks for your assistance with this matter.
How would I go about logging a support case?
You can use the Service Portal website to log a ticket with McAfee Support:
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.
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